Restore database from database using script

Question

I'm looking for a simple script that I can use to overwrite one database with another one. We have a master db with master schema and data and every so often a developer wants to blow away his messed up db with a complete overwrite from the master. I currently accomplish this with SQL Server Studio and the GUI controls but I want something similar to what we use when restoring from a backup file (just without the backup file step):

RESTORE DATABASE [SlaveDB]
FROM  DISK = N'E:\Backup\MasterDB.bak'
WITH  FILE = 1,  
MOVE N'SlaveDB_Data' TO N'E:\Data\SlaveDB_Data.mdf',
MOVE N'SlaveDB_Log' TO N'E:\Log\SlaveDB_Log.ldf',
NOUNLOAD,  
STATS = 10
GO

What's the syntax for getting the db from another db instead of a backup file?

Solution 1

if you want to restore database from a generated script file you can use windows command line.

open CDM and run the below command (database=NorthWind, script file C:\MyScript.sql)

sqlcmd -S localhost -d NorthWind -i "C:\MyScript.sql"

Solution 2

I use this script every day to restore production backup to test database.

1. db names on production and on test are the same, suppose its name is MyDb.

2. delete test database

3. Run the script

---

RESTORE FILELISTONLY 
FROM DISK   = 'E:\WorkCopy\BackUp.bak'

RESTORE DATABASE [MyDb]
FROM DISK   = 'E:\WorkCopy\BackUp.bak'
WITH 
MOVE  'MyDbPrimary' TO 'D:\data\MyDb\WorkCopy.mdf',
MOVE 'MyDbImp' TO 'D:\data\MyDb\WorkCopy_1.ndf',
MOVE 'MyDbCut' TO 'D:\data\MyDb\WorkCopy_2.ndf',
MOVE 'MyDbIX' TO 'D:\data\MyDb\WorkCopy_3.ndf',
MOVE 'MyDbAUD' TO 'D:\data\MyDb\WorkCopy_4.ndf',
MOVE 'MyDbLog' TO 'D:\data\MyDb\WorkCopy_5.ldf',
move 'sysft_FTIndexCatalog' TO 'D:\data\MyDb\FTIndexCatalog'


ALTER DATABASE MyDb
Set RECOVERY  SIMPLE