SQL Server DBA

Restoring Databases in SQL Server 2012


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;
Advertisements

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