CODEWITHSUNDEEP
sqlsql-server-2005
grenade
grenade

Reputation: 32179

Restore database from database using script

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?

Upvotes: 3

Views: 16389

Answers (2)

Alper Ebicoglu
Alper Ebicoglu

Reputation: 9644

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"

Upvotes: 3

skaeff
skaeff

Reputation: 753

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

Upvotes: 1

Related Questions