Restore only the data file from a database backup

Yesterday I came across a question “Is it possible to restore only the .mdf file from a database backup? “. Apparently, the user wanted to restore the database on a new server which had limited space and the transaction file was not required to restored on the test server.

Now the answer to this question is a big Yes. It is possible to restore only a few files from the database backup using the FILE syntax explained here on MSDN.

I am reproducing the same using this example. First I am creating a new database named MDFRestoreExample.

CREATE DATABASE MDFRestoreExample ON   PRIMARY
( NAME = N'MDFRestoreExample' , FILENAME = N'C:MDFRestoreExample.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'MDFRestoreExample_log' , FILENAME = N'C:MDFRestoreExample_log.ldf' , SIZE = 2048KB , FILEGROWTH = 10 %)
GO

After that I am taking the database backup to a local drive.

BACKUP DATABASE MDFRestoreExample TO DISK = 'C:MDFRestoreExample.bak'

Now that we have the backup, I will drop the database.

DROP DATABASE MDFRestoreExample

Now before restoring, I will check what files does the database backup contain, using this query.

RESTORE FILELISTONLY FROM DISK = 'C:MDFRestoreExample.bak'

The output will be as in the below screen shot.

Here the requirement is to restore only the Data File (MDFRestoreExample) from the backup files. The FileID is 1 for the data file and we want to restore only that file. Here is the query which does it.

RESTORE DATABASE MDFRestoreExample FROM DISK = 'C:MDFRestoreExample.bak'
WITH
       FILE = 1 ,
       RECOVERY

In this query I am instructing SQL Server to restore only the File 1 in the backup set. However a database cannot be ONLINE unless it has a transaction log associated with it. So the RECOVERY option will force SQL Server to rebuild the transaction log and brings the database ONLINE.

After executing this query, sp_helpfile confirmed that the new database was online with a new transaction log.

2 thoughts on “Restore only the data file from a database backup

  1. Prakash

    NO. That’s INCORRECT. File given in RESTORE DATABASE command is the position of backup if there are multiple backups in a given backup file. it has nothing to do with output of Restore FileListOnly. (Refer Books online)

    How can you have a database recovered in this way?

    1. PradeepAdiga Post author

      Here is what is mentioned in BOL

      **********
      In the RESTORE DATABASE, notice that there are two types of FILE options. The FILE options preceding the backup device name specify the logical file names of the database files that are to be restored from the backup set; for example, FILE = ‘MyDatabase_data_1’. This backup set is not the first database backup in the media set; therefore, its position in the media set is indicated by using the FILE option in the WITH clause, FILE=9
      **********
      Please refer this link for more information. Also give it a try on a test box

      Cheers,
      Pradeep

Comments are closed.