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 FILELISTONLY . This 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 :
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. 🙂