Quick Database Backup and Restore

As a part of my job, I routinely backup and restore databases. From testing database upgrade scripts to fixing a developer faux pas, this script has seen a lot of use. I know that this can be done very easily through the GUI, but the T-SQL is much faster, and you can automate these tasks, which is especially helpful when you are restoring seven or eight backups.

I hope that you find it useful. 🙂

/* For good measure, run this from master */
USE [master];
GO

/* Full Backup to Disk */
BACKUP DATABASE [Database]
TO DISK='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\FullBackup.bak'

/* Differential Backup to Disk */
BACKUP DATABASE [Database]
TO DISK='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DifferentialBackup.bak'
WITH DIFFERENTIAL

/* Log Backup to Disk */
BACKUP LOG [Database]
TO DISK='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\LogBackup.trn'

/* Copy Only Full Backup to Disk to not disrupt any current backup plans */
BACKUP DATABASE [Database]
TO DISK='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CopyOnlyFullBackup.bak'
WITH COPY_ONLY

/* If you are moving the file locations, check for file names */
RESTORE FILELISTONLY
FROM DISK = '\\unc\path\to\FullBackup.bak';

/*****************************************/
/* Backup the tail log before proceeding */
/*****************************************/

/* Restore the last full backup */
RESTORE DATABASE [Database]
FROM DISK = '\\unc\path\to\FullBackup.bak'
WITH RECOVERY,
/* The lines below are only necessary if you are relocating the files */
MOVE 'Database_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Database_Data.mdf',
MOVE 'Database_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Database_Log.ldf';
GO

/* Restore the latest differential */
RESTORE DATABASE [Database]
FROM DISK = '\\unc\path\to\DifferentialBackup.bak'
WITH RECOVERY;
GO

/* Reapply transaction from the transaction logs since the last backup */
/* You will likely run this several times */
/* WITH RECOVERY should only be set on the last restore. */
RESTORE LOG [Database]
FROM DISK = '\\unc\path\to\LogBackup.trn'
WITH RECOVERY; -- WITH RECOVERY should only be set on the last restore.

~Ron

Advertisements

0 Responses to “Quick Database Backup and Restore”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s





%d bloggers like this: