January 16, 2013 at 9:32 pm
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.
January 17, 2013 at 1:07 am
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;-)
January 17, 2013 at 3:15 pm
That works thanks. but it puts the whole data and time on the file name.
Is there anyway to just get the time?
January 17, 2013 at 3:33 pm
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[/url]
Learn Extended Events
January 17, 2013 at 4:20 pm
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.
January 17, 2013 at 4:41 pm
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[/url]
Learn Extended Events
January 17, 2013 at 4:58 pm
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.
January 17, 2013 at 5:11 pm
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply