TechJames: Backup and Restore

This is from Sandeep Lingarkar , DBA SQL Server, write up.
I would thank him for his contribution which happended over a conversation.

Backup jobs: Create 3 backup jobs for full, differential and log backups of all databases.

1) Create a full backup job which starts every day at 10 pm.

Script to be used in job step:

DECLARE @name VARCHAR(50) -- database name

DECLARE @path VARCHAR(256) -- path for backup files

DECLARE @fileName VARCHAR(256) -- filename for backup

DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Sandeep\Backup\Full\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

1) Create a differential backup job which runs every 4 hours starting at 11 pm.

Script to be used in job step:

DECLARE @name VARCHAR(50) -- database name

DECLARE @path VARCHAR(256) -- path for backup files

DECLARE @fileName VARCHAR(256) -- filename for backup

DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Sandeep\Backup\differential\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @fileName = @path + @name + '_' + @fileDate + '.diff'

BACKUP DATABASE @name TO DISK = @fileName with differential

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

2) Create a log backup job which runs every 15 mins starting from 10 : 30 pm

Script to be used in log backup job step:

DECLARE @name VARCHAR(50) -- database name

DECLARE @path VARCHAR(256) -- path for backup files

DECLARE @fileName VARCHAR(256) -- filename for backup

DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Sandeep\Backup\Log\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN ('master','model','msdb','tempdb','reportservertempdb')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @fileName = @path + @name + '_' + @fileDate + '.trn'

BACKUP log @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

Restore:

Restoration process in case of any database failure. During any database failure we need to perform a tail log backup before starting the restoration process.

Tail Log Backup:

SQL Server 2005 and later versions usually require that you take a tail-log backup before you start to restore a database. The tail-log backup prevents work loss and keeps the log chain intact. When you are recovering a database to the point of a failure, the tail-log backup is the last backup of interest in the recovery plan. If you cannot back up the tail of the log, you can recover a database only to the end of the last backup that was created before the failure.

· If the database is online and you plan to perform a restore operation on the database, before starting the restore operation, back up the tail of the log using WITH NORECOVERY:

BACKUP LOG test TO disk ='C:\Sandeep\Backup\Log\Test_20120104.trn'

WITH NORECOVERY

· If the database is offline and does not start.

Try to take a tail-log backup. Because no transactions can occur at this time, using WITH NORECOVERY is optional. If the database is damaged, use WITH CONTINUE_AFTER_ERROR, as follows:

BACKUP LOG test TO disk ='C:\Sandeep\Backup\Log\Test_20120104.trn'

WITH CONTINUE_AFTER_ERROR

If the database is damaged, for example, if the database does not start, a tail-log backup succeeds only if the log files are undamaged, the database is in a state that supports tail-log backups, and the database does not contain any bulk-logged changes.

Steps to restore the database:

1) Restore the database with latest full backup with no recovery.

RESTORE DATABASE [Test] FROM DISK = 'C:\Sandeep\Backup\Full\Test_20120104.BAK' WITH NORECOVERY,

Restore the database with latest differential backup with no recovery.

RESTORE DATABASE [Test] FROM DISK = 'C:\Sandeep\Backup\differential\Test_20120104.diff' WITH NORECOVERY

2) Restore the transaction logs and the tail log backup.

· Before restoring the log backups run the below script and check for the backupfinishdate for the latest differential backup applied.

restore headeronly from disk='C:\Sandeep\Backup\Differential\Test_20120104.diff'

BackupFinishDate = 2012-01-04 09:41:16.000

· Now run the below command to check the log backup date time and fileid.

restore headeronly from disk='C:\Sandeep\Backup\Log\Test_20120104.trn'

Some Columns from the Output:

Position	DatabaseName	BackupFinishDate
1	Test	1/4/12 9:06
2	Test	1/4/12 9:30
3	Test	1/4/12 9:44
4	Test	1/4/12 9:54
5	Test	1/4/12 9:54
6	Test	1/4/12 10:18
7	Test	1/4/12 10:42
8	Test	1/4/12 11:06
9	Test	1/4/12 11:30
10	Test	1/4/12 11:54

As the differential backup is of 9: 41 PM, restore all the logs after 9:41PM with no recovery and the tail log with recovery using the below script.

RESTORE LOG test