Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Vendor DB backup job help. Expand / Collapse
Author
Message
Posted Wednesday, January 16, 2013 9:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 08, 2013 10:48 PM
Points: 31, Visits: 124
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.
Post #1408133
Posted Thursday, January 17, 2013 1:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
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
Post #1408197
Posted Thursday, January 17, 2013 3:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 08, 2013 10:48 PM
Points: 31, Visits: 124
That works thanks. but it puts the whole data and time on the file name.

Is there anyway to just get the time?
Post #1408638
Posted Thursday, January 17, 2013 3:33 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:42 AM
Points: 20,460, Visits: 14,086
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1408645
Posted Thursday, January 17, 2013 4:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 08, 2013 10:48 PM
Points: 31, Visits: 124
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.
Post #1408664
Posted Thursday, January 17, 2013 4:41 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:42 AM
Points: 20,460, Visits: 14,086
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1408671
Posted Thursday, January 17, 2013 4:58 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 1:11 PM
Points: 3,081, Visits: 11,230
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.








Post #1408673
Posted Thursday, January 17, 2013 5:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, December 08, 2013 10:48 PM
Points: 31, Visits: 124
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.
Post #1408677
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse