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;-)