Post reply

Create new databases from BAK file.

  • June 23, 2005 at 1:18 am

    #67199

    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

    #567830

    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

    #568206

    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

    #568207

    Thank you all!

  • June 24, 2005 at 10:07 am

    #568311

    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