How to Backup SQL Server 2005 Databases

by

Backup Requirement

Daily full backup, transaction log backups every 15 minutes. Files are backed up to the local filesystem. Remote backup server copies changes from the server every 15 minutes. Although not an efficient use of space, it is useful to backup to the local hard drive in case a point in time restore is needed, or for restoring tests databases to other SQL Server instances.

Recoverability

If an unrecoverable failure occured on the database system the data will be recoverable to a state within 30 minutes (this is a worst case scenario if the remote backups aren’t synchronized: 15 since last transaction log and 15 minutes since last offsite backup) of failure by restoring the full backup and rolling the transaction log backups forward.

SQL Server backups

Create a Job to do a FULL backup daily. Overwrite the backup if it already exists, and it’s always a good idea to do a checksum and verify the backup:

BACKUP DATABASE [WSS_Content] TO DISK = N’D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\WSS_Content.bak’ WITH NOFORMAT, INIT, NAME = N’WSS_Content-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N’WSS_Content’ and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N’WSS_Content’ )
if @backupSetId is null begin raiserror(N’Verify failed. Backup information for database ”WSS_Content” not found.’, 16, 1) end
RESTORE VERIFYONLY FROM DISK = N’D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\WSS_Content.bak’ WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

Create a second Job to do a Transaction Log backup every 15 minutes. This time append to the backup file like this:

BACKUP LOG [WSS_Content] TO DISK = N’D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\WSS_Content.trn’ WITH NOFORMAT, NOINIT, NAME = N’WSS_Content-Transaction Log Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N’WSS_Content’ and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N’WSS_Content’ )
if @backupSetId is null begin raiserror(N’Verify failed. Backup information for database ”WSS_Content” not found.’, 16, 1) end
RESTORE VERIFYONLY FROM DISK = N’D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\WSS_Content.trn’ WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

To prevent the transaction log backup file from growing infinitly large add a second step to the first Job, so that after the FULL backup an immediate transaction log backup occurs. The only difference is you want to overwite the backup set for the transaction log:

BACKUP LOG [WSS_Content] TO DISK = N’D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\WSS_Content.trn’ WITH NOFORMAT, INIT, NAME = N’WSS_Content-Transaction Log Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N’WSS_Content’ and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N’WSS_Content’ )
if @backupSetId is null begin raiserror(N’Verify failed. Backup information for database ”WSS_Content” not found.’, 16, 1) end
RESTORE VERIFYONLY FROM DISK = N’D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\WSS_Content.trn’ WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

Summary

The first Job runs daily and has two steps. The first step does a full backup of the database overwriting the previous full backup. The second step does a transaction log backup overwriting all the previous transaction log backups. The second job runs a transaction log backup, which gets appended to the previous transaction log backup every 15 minutes. While this is happening the backup server checks every 15 minutes archiving any new or changed files from the system.

Restoring

To restore the database, first restore the full backup, then restore each transaction log. Here’s an example of restoring the first four transaction logs:

RESTORE DATABASE [WSS_Content] FILE = N’WSS_Content’ FROM DISK = N’D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\WSS_Content.bak’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [WSS_Content] FROM DISK = N’D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\WSS_Content.trn’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [WSS_Content] FROM DISK = N’D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\WSS_Content.trn’ WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [WSS_Content] FROM DISK = N’D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\WSS_Content.trn’ WITH FILE = 3, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [WSS_Content] FROM DISK = N’D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\WSS_Content.trn’ WITH FILE = 4, NOUNLOAD, STATS = 10
GO

Make sure the last log restore puts the database into RECOVERY mode by omitting the NORECOVERY command. The database can only have transaction logs rolled forward while in NORECOVERY mode. If you mess this up by putting the database into RECOVERY mode before the last transaction log is rolled forward, you have to start the restore process all over. So do not mess it up.

Leave a Comment