Restoration of Database using SQL Server 2005 : Part IV


In this post, we will see how to restore a database using Transact SQL query language. So let’s start with the database restoration from a full backup file.

i. Restoration of a Database from a Full Backup File:

In this part, we are going to discuss the syntax of restoring a database from a full backup file. The default syntax is as follows:

RESTORE DATABASE <Database_Name> FROM DISK = <Backup_File_Location>
WITH MOVE <Logical_DataFileName_of_BackupDB> TO <Physical_Path_of_DataFile>,
MOVE <Logical_LogFileName_of_BackupDB> TO <Physical_Path_of_LogFile>
-- ,REPLACE /* Use replace if the data and log files are already exists */

To complete this example, we are going to restore the backup file of SKG_TEST2 on the SKG_TEST4 database.

RESTORE DATABASE [SKG_TEST4]

FROM DISK ='D:\Backup\Test\SKG_TEST2_FULL.bak'

WITH

MOVE 'SKG_TEST2_Data' TO 'D:\UserDB\DBS01\Data\SKG_TEST4_Data.MDF',

MOVE

'SKG_TEST2_Log' TO 'D:\UserDB\DBS01\Log\SKG_TEST4_Log.LDF'

GO

ii. Restoration of a Database from a Full & a Differential Backup File:

In this part, we are going to restore the SKG_TEST4 from a full and a differential backup files. Both of these backup files are residing in a single backup media set with file id 1 & 2 respectively. The syntax to restore the database from a full & a differential backup file is as follows:

RESTORE

DATABASE [SKG_TEST4]

FROM DISK = N'D:\Backup\Test\SKG_TEST2_FULL.bak'

WITH

FILE = 1, NORECOVERY, NOUNLOAD, REPLACE

GO

RESTORE

DATABASE [SKG_TEST4]

FROM DISK = N'D:\Backup\Test\SKG_TEST2_FULL.bak'

WITH

FILE = 2, NOUNLOAD, REPLACE, RECOVERY

GO

In the above script, File ID 1 represents the Full backup file, whereas File ID 2 represents the Differential Backup File. The REPLACE keyword is used because the data and log files are already exists and overwritten with the use of REPLACE keyword.

iii. Restoration of a Database from a Full , a Differential & a Transaction Log Backup File:

In this section, we are going to restore the SKG_TEST4 database from a full, differential and a t-log backup file. All this files are reside in the same backup media set. As the data and log file are already exists, we are going to overwrite both the files. The t-sql syntax is as follows:

RESTORE

DATABASE [SKG_TEST4]

FROM DISK = N'D:\Backup\Test\SKG_TEST2_FULL.bak'

WITH

FILE = 1, NORECOVERY, NOUNLOAD, REPLACE

GO

RESTORE

DATABASE [SKG_TEST4]

FROM DISK = N'D:\Backup\Test\SKG_TEST2_FULL.bak'

WITH

FILE = 2, NORECOVERY, NOUNLOAD, REPLACE

GO

RESTORE

LOG [SKG_TEST4]

FROM DISK = N'D:\Backup\Test\SKG_TEST2_FULL.bak'

WITH

FILE = 3, RECOVERY, NOUNLOAD, REPLACE

GO

For more information on the RESTORE DATABASE syntax, please visit Books Online @ http://msdn.microsoft.com/en-us/library/ms186858(SQL.90).aspx .

Advertisements

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in Backup - Restore. Bookmark the permalink.

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