TSQL syntax to restore .bak to new db

Question

I need to automate the creation of a duplicate db from the .bak of my production db. I've done the operation plenty of times via the GUI but when executing from the commandline I'm a little confused by the various switches, in particular, the filenames and being sure ownership is correctly replicated.

Just looking for the TSQL syntax for RESTORE that accomplishes that. thx

Answer 1

look here: How to: Restore a Database to a New Location and Name (Transact-SQL), which has a good example:

This example creates a new database named MyAdvWorks. MyAdvWorks is a copy of the existing AdventureWorks database that includes two files: AdventureWorks_Data and AdventureWorks_Log. This database uses the simple recovery model. The AdventureWorks database already exists on the server instance, so the files in the backup must be restored to a new location. The RESTORE FILELISTONLY statement is used to determine the number and names of the files in the database being restored. The database backup is the first backup set on the backup device.

USE master
GO
-- First determine the number and names of the files in the backup.
-- AdventureWorks_Backup is the name of the backup device.
RESTORE FILELISTONLY
   FROM AdventureWorks_Backup
-- Restore the files for MyAdvWorks.
RESTORE DATABASE MyAdvWorks
   FROM AdventureWorks_Backup
   WITH RECOVERY,
   MOVE 'AdventureWorks_Data' TO 'D:\MyData\MyAdvWorks_Data.mdf', 
   MOVE 'AdventureWorks_Log' TO 'F:\MyLog\MyAdvWorks_Log.ldf'
GO

This may help also: Copying Databases with Backup and Restore

Answer 2

Assuming you're using SQL Server 2005 or 2008, the simplest way is to use the "Script" button at the top of the restore database dialog in SQL Server Management Studio. This will automatically create a T-SQL script with all the options/settings configured in the way you've filled in the dialog.