I am practicing backup and restore techniques for SQL Server databases. There are three scenarios that I find myself constantly looking up, so though I would blog them here.
SCENARIO 1: Using backup and restore to move a database from Server A to Server B.
This requires taking a backup of the data and log files on Server A, moving these to Server B and then creating (or attaching) the database on the new server. T-SQL to recreate the database on Server B is:
CREATE DATABASE [db_name] ON ( FILENAME = '<path to data file that was copied to ServerB>' ), ( FILENAME = '<path to log file that was copied to ServerB>' ) FOR ATTACH
SCENARIO 2: Restoring a TEST database from PRODUCTION.
In this scenario, we want to refresh a TEST database with the current PRODUCTION version, and the production database backup file is accessible from the test server (i.e. when don’t have to physically move the backup to the test server ourselves). T-SQL as follows:
-- STEP 1: restore the filelist from the backup to get the logical filenames RESTORE FILELISTONLY FROM <logical backup name>; RESTORE DATABASE [test_db_name] FROM DISK = '<path to production backup>' WITH MOVE '<logical name of data file>' TO '<location of TEST data file>', MOVE '<logical name of log file>' TO '<location of TEST log file>', REPLACE, STATS=10;
SCENARIO 3: A simple database restore
T-SQL to simply restore a database. In this scenario I am assuming that there is a full backup and two log backups
RESTORE DATABASE [db_name] FROM DISK = '<path to backup>' WITH NORECOVERY, STATS=10; RESTORE LOG [db_name] FROM DISK = '<path to log backup # 1>' WITH NORECOVERY, STATS=10; RESTORE LOG [db_name] FROM DISK = '<path to log backup # 2>' WITH RECOVERY, STATS=10;