T-Log Backup/Restore with Date & Time

S. Kusen, 2010-12-29 (first published: 2010-12-15)

Recently, I have been moving a lot of VLDB's onto new SQL Server environments. One method (of many) is to restore the full backup to the new system, and then incrementally restore log backups in sequence.

One way to lessen confusion would be to name the log backups to include the date and time to prevent overwriting the files, and hopefully make it less confusing as you proceed with the DB move, which is what this script tries to accomplish.

What I have been doing for my recent migrations is backup the log every 4 hours, and as I get closer to the maintenance window, I'll lower the frequency down to 1 hour, and then down to 15 minutes; I restore each log on the new system as they are backed up. Using the backup script provided, it will dynamically update the file names as you change the schedule of the SQL Server Agent job. At the time of the cutover, the last 15 minute log backup is quick to restore.

There are obviously ways to automate these scripts even more, but this method has helped me out a lot recently.

To start using this, make sure you've set the database to FULL recovery model and have already taken a full database backup after changing the recovery model, and then restored that backup to your new host.

To use, change the path of the location for your backups, and change YourDBNameHere to the name of your database to backup the log.

As always, thanks for checking out my scripts.

Cheers,

Steve

--S. Kusen: This script can be used to backup T-LOGs for a specific database and include the date and time in the name. --this will put the date and time in the backup filename --can be used during migrations from server A to server B, for example --DECLARE VARIABLES DECLARE @dbname varchar(255), @path varchar(1000), @name varchar(100), @currentdate datetime, @year varchar(4), @month varchar(2), @day varchar(2), @hour varchar(2), @minute varchar(2), @AMPM varchar(2) --SET USER DEFINED VALUES SELECT @currentdate = GETDATE() SELECT @year = CONVERT(varchar(4), YEAR(@currentdate)) SELECT @day = RIGHT('0' + convert(varchar(2), DAY(@currentdate)), 2) SELECT @month = RIGHT('0' + convert(varchar(2), MONTH(@currentdate)), 2) SELECT @hour = RIGHT('0' + convert(varchar(2), DATEPART(HOUR,@currentdate)), 2) SELECT @minute = RIGHT('0' + convert(varchar(2), DATEPART(MINUTE, @currentdate)), 2) SELECT @AMPM = RIGHT(RIGHT('0'+LTRIM(RIGHT(CONVERT(varchar,@currentdate,100),7)),7), 2) SET @dbname = 'YourDBNameHere' --DB being backed up SET @path = 'D:\MSSQL\BACKUP\LOG_Backups\'+@dbname+'_Log_'+@year+@month+@day+'_'+@hour+@minute+@AMPM+'.trn' --set the backup path, including DB name SET @name = @dbname+' Log Backup' --name the backup set SELECT @path --BACKUP DATABASE BACKUP LOG @dbname TO DISK=@path WITH INIT, NAME=@name, STATS = 10 GO --restore first log RESTORE LOG [YourDBNameHere] FROM DISK = 'D:\MSSQL\BACKUP\LOG_Backups\YourDBNameHere_Log_20101215_2015PM.trn' -- example of an 8:15 PM backup time WITH NoRECOVERY, STATS = 10; --leave in NORECOVERY to allow future T-LOG backups to be restored --restore next log RESTORE LOG [YourDBNameHere] FROM DISK = 'D:\MSSQL\BACKUP\LOG_Backups\YourDBNameHere_Log_20101215_2115PM.trn' -- example of an 9:15 PM backup time WITH NoRECOVERY, STATS = 10; --leave in NORECOVERY to allow future T-LOG backups to be restored --recover the database, we aren't going to restore any additional logs RESTORE DATABASE [YourDBNameHere] WITH RECOVERY;