Full Database Backup Restore

Working as DBA or developer, restoring a database is the request which we get regularly. Restoring a database is easy task if our back file is healthy.

Restore a Full Database Backup

Below is script which we can use for restore a database from full backup...

-------------------------------------------------------------------- 

RESTORE DATABASE DB_Name FROM DISK = 'Location of DB backup file path' 

--------------------------------------------------------------------

But the problem with the script is, we do not know where the DB files are moving after the restore.

To over come the issue, below are steps 

1. We need to find out the list of database files which the backup file is containing.

2. The logical name of each DB files which are in side the backup file.

To know the above information, we use  RESTORE FILELISTONLY command.

Below is the example.

  

--------------------------------------------------------------------

RESTORE FILELISTONLY from DISK= N'Location of DB backup file path'

--------------------------------------------------------------------

With the help of the commend, we can identify the list of DB files which are inside the backup file.

In the example, we can see there are 3 files in the D1.BAK D1, D1_1 and D1_log.

At the time of restoration, we can use MOVE options in RESTORE command to move the files at desire location. I'm taking D1.BAK file to explain the restore with move option.

--------------------------------------------------------------------

RESTORE DATABASE D1_RESTORE FROM DISK = N'C:\D1.BAK'

WITH

MOVE 'D1' TO 'C:\D1_DATABSE_1.MDF' ,

MOVE 'D1_1' TO 'E:\D1_DATABSE_1.NDF',

MOVE 'D1_log' TO 'E:\D1_LOG_FILE.LDF',

RECOVERY 

--------------------------------------------------------------------