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.