SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Vendor DB backup job help.


Vendor DB backup job help.

Author
Message
PretendDBA
PretendDBA
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 503
Hi, I have a vendor app that runs an SQL DB.

Now the vendor provided all the agent jobs for this DB, they recommend that transaction logs be backup hourly. I would like to backup the log every 15 minutes, They said thats ok and i could modify their job and they'll have a look and let me know.

declare @FullPathName as varchar(2000), @BackupPathName as varchar(2000), @DBName as varchar(100)
--assign database name
select @DBName = 'DATABASENAME', @BackupPathName = '\\SERVER\SHAREPATH'
--assign the pathname - tack on a number to denote that hour's log backup
select @FullPathName = @BackupPathName + @DbName + 'Log_' +
convert(varchar(2),datepart(hh,getdate())) +
convert(varchar(2),datepart(mi,getdate())) + '.bak'
--backup the transaction log - allow it to do overwritten (retaindays = 0, init)
backup log @DBName to disk = @FullPathName with retaindays = 0, init

Now i added the convert(varchar(2),datepart(mi,getdate())) part which does give me the minutes into the filename. but if the backup runs on the hour the file will get called DBNAMELog_13.bak for example.

Not a huge deal but when restoring the files it doesn't list them in a nice order and the files not backed up on the hour are a larger number and get listed at the end.

I'm guessing i need some sort of where clause to fix this but i am no T-SQL pro and the team member that could help me is on leave till 11th of Feb and i'd like to have this proofed and signed off but by change committee by then.

I would like the file be get created as dbanmelog_1300.bak, not fussed about the hour part showing 600 instead of 0600 for 6am for example just the minutes part.
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5366 Visits: 4076
davidwarner (1/16/2013)
Now i added the convert(varchar(2),datepart(mi,getdate())) part which does give me the minutes into the filename. but if the backup runs on the hour the file will get called DBNAMELog_13.bak for example.

i found your above quote is confusing , what exactly you need from backup suffix (time display).

davidwarner (1/16/2013)
Not a huge deal but when restoring the files it doesn't list them in a nice order and the files not backed up on the hour are a larger number and get listed at the end.
When you use WIZARD , sql itself maintain the backup files order with the help of LSN no. regardless of the backup file name.

and when you use T-saql apprach then for your easiness , you can have well defind and CLEAR time suffix and i drew in below example by @backuptime
declare @FullPathName as varchar(2000), @BackupPathName as varchar(2000), @DBName as varchar(100),@backuptime nvarchar(20) ,@lsql nvarchar(1000)
--assign database name
select @DBName = 'DATABASENAME', @BackupPathName = '\\SERVER\SHAREPATH'
SELECT @backuptime = left(REPLACE(replace(replace(CONVERT(VARCHAR(20),GETDATE(),120),':',''),'-','') ,' ',''),12)
--assign the pathname - tack on a number to denote that hour's log backup
set @lsql = ''
select @FullPathName =
@BackupPathName + @DbName + 'Log_' +
@backuptime + '.bak'
--backup the transaction log - allow it to do overwritten (retaindays = 0, init)
set @lsql = 'backup log ' + @DBName + ' to disk = ' + @FullPathName + ' with retaindays = 0, init'
print @lsql
exec(@lsql)



-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
PretendDBA
PretendDBA
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 503
That works thanks. but it puts the whole data and time on the file name.

Is there anyway to just get the time?
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32928 Visits: 18560
There is a way, but while hashing that out I just want to bring up a preference. I prefer to have the date and time on the backup files myself. If the logs traverse a day, then they will still be listed in order by name. It's just easier imho.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

PretendDBA
PretendDBA
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 503
Problem is that the files will just build up if the filenames are different. This current setup allows the files to just roll over the next day.
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32928 Visits: 18560
davidwarner (1/17/2013)
Problem is that the files will just build up if the filenames are different. This current setup allows the files to just roll over the next day.


And what if you need to recover to a point in time yesterday after you have overwritten that file with today's tlog backup?



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5862 Visits: 11771
SQLRNNR (1/17/2013)
There is a way, but while hashing that out I just want to bring up a preference. I prefer to have the date and time on the backup files myself. If the logs traverse a day, then they will still be listed in order by name. It's just easier imho.


My experience is that vendor supplied backup jobs are usually not very well done, and it looks like the case in your situation. I prefer to setup my own backups.

One day is really too short a time to retain the transaction logs. I recommend at least four days, and I would like to see longer. I also recommend you do a full backup daily and keep that for at least three days.

If you are on SQL Server 2008 Enterprise Edition or SQL Server 2008 R2 or later, you may also want to set the option to compress backups. They usually run faster, and they will take up less disk space.

You could do all this with SQL Server maintenance plans that can be setup in just a few minutes. It takes care of generating the date/timestamped backup file names, setting up jobs and schedules, and also takes care of deleting the old backup files on the retention that you decide.

There are also very good backup stored procedures posted on this site and elsewhere that can do all of this.
PretendDBA
PretendDBA
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 503
We are already doing nightly full backups. without this we would be in hot watewr over writting the log backups everyday. we also archive the tlogs and nightly backups to tape over a 30 days cycle. so at any point i can restore from up to 30 days ago the full backup and that days tlogs backups. we also do a 3/6 monthly archive to tape.

We need to vendor to be comfortable with the backup / restore process as if I and another Staff member are away they'll be the ones called to do the retore process.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search