June 23, 2005 at 1:18 am
We have a template database in which development is done.
We back up that database to a BAK file, and then we restore from the BAK file to new databases - the ones we use in training. We used to do this manually in Enterprise Manager. Problem is when we have to make, like, 50 or so databases (training_01, training_02, etc).
Frankly it is getting tedious.
So I think to myself, there must be an easier way of getting this done. Looking in BOL I find some TSQL on how to restore a database from BAK file, but however I do it I always fail.
I know - I probably suck, hehe.
The database have two datafiles, maybe that is part of the problem?
I hope maybe someone here has already done something like this, and that it will be possible to script the creation of 50 databases.
June 23, 2005 at 4:41 am
You has been checking the location of data? the filename of mdf file? Because I guest the file name or the file exist in the folder location where you restore the database.
June 24, 2005 at 2:40 am
Check out this script:
http://www.sqlservercentral.com/scripts/contributions/764.asp
I use it every week and it works for me.
Andy
June 24, 2005 at 3:02 am
Thank you all!
June 24, 2005 at 10:07 am
How about using just a restore with replace and move? Run sp_helpdb TrainingDb to get the logical names and paths of the database you want to restore. You may need to add a step prior to the restore to kill SPIDS on the TrainingDb since the database can't be in use.
RESTORE DATABASE TrainingDb
FROM DISK = 'G:\Mssql\backup\ProductionDb.bak'
WITH REPLACE,
MOVE 'TrainingDb_Data' TO 'G:\MSSQL\data\TrainingDb.mdf',
MOVE 'TrainingDb_Log' TO 'F:\MSSQL\data\TrainingDb.ldf'
Linda
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply