T-Log Backup/Restore with Date & Time

,

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;

Rate

4.75 (4)

Share

Share

Rate

4.75 (4)