We help IT Professionals succeed at work.

How to backup transaction log to separate files in SQL 2005 Express

KANEWONG
KANEWONG asked
on
Hello;

I am using the sql script below to create a transaction log backup file for my database but the log is appending to the same .bak file that will create a huge file eventually.  How can I separate each transaction log backup to a separate file with time stamp?  It is very easy in the Full version of SQL2005 but I am using the SQL 2005 Express this time.


BACKUP LOG [mycompany] TO  DISK = N'F:\LifeDB-Backup\DB_Log.bak' WITH NOFORMAT, NOINIT,  NAME = N'Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'mycompany' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'mycompany' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''mycompany'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'F:\LifeDB-Backup\DB_Log.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO
Comment
Watch Question

Commented:
You would need to build the filename with the timestamp in it.  

http://www.sqlservercentral.com/Forums/Topic359034-8-1.aspx

This link will show you some examples of how to do that.  But once you build the file, then you will not have to worry about the backup hitting the same file each time.

Author

Commented:
the example in the link is ok if I do not have the following parameters, if I have those WITH NOFORMAT syntax added, it does not work.

WITH NOFORMAT, NOINIT,  NAME = N'msdb-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'msdb' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'msdb' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''msdb'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = @BackupFilename WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO
Commented:
Sorry.  I should have tested it.  The : is what broke it.  This is a different syntax and they should not be in the filenames.

declare @backupfile nvarchar(2000)
set @backupfile = N'C:\Temp\msdb_db_' + REPLACE(REPLACE(replace(convert(nvarchar(50), getdate(), 120), ' ',''), ':', ''), '-', '') + N'.BAK'
BACKUP DATABASE [msdb] TO DISK = @backupfile
WITH NOFORMAT, NOINIT,  NAME = N'msdb-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Author

Commented:
Hi;

It works.

thx!