Backup & Restore

Find out logical file name of a database backup file

It’s easy when you want to restore an SQL Server database backup file using SQL Server Management Studio (SSMS). Since you only have to know where you’ve stored backup file and with few click and done, database will successfully restored.

But what if you wanna use T-SQL instead to restore the database and you don’t want to place all data files in default location? You have to add “WITH MOVE” option in your restore command. But the problem is this option will only move logical file name of database backup file. Here comes RESTORE FILELISTONLYThis command will return list of data file and log file in  a SQL Server database backup file.

Here is the T-SQL to get list of data and log file of SQL backup file :

USE [master]
GO
RESTORE FILELISTONLY
FROM DISK = N'F:\DB_BACKUP\AdventureWorks2012-Full Database Backup.bak'
GO

Once you run the above command, you will got the following result :

restore-filelistonly-result

Since you know what is the logical name of your backup file, then you can restore this backup file and move the data or log file to different location using”WITH MOVE” option.

USE [master]
GO
RESTORE DATABASE [AdventureWorks2012]
FROM DISK = N'F:\DB_BACKUP\AdventureWorks2012-Full Database Backup.bak'
WITH FILE = 1,
MOVE N'AdventureWorks2012_Data' TO N'F:\UserDataFiles\AdventureWorks2012_Data.mdf',
MOVE N'AdventureWorks2012_Log' TO N'G:\UserLogFiles\AdventureWorks2012_Log.ldf'
GO

Thats it.Hope this help. 🙂

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