Help generate SQL Script

I have some SQL Databases that are used for training.

What I have looks something like

Training1
Training2
Training3
.
.
Master Training DB

The master training DB is updated periodically with new information to include in the training.  At the beginning of a training session the training DBs mirror the Master Training DB.

Currently I do a backup of the Master Training DB, and then restore over the Training DBs using overwrite.

I need assistance with a script that would automate this.

It would basically.

Backup Master Training DB to disk
Delete Training DBs
Restore Training DBs using Master Training DB Backup  (Or restore with overwrite)

Your help is appreciated.
LVL 8
ITDharamAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
This should do it:

RESTORE DATABASE training1
      FROM DISK = 'F:\Winteam\Backup\Training_Master.bak' WITH
      MOVE 'DEV_Data' TO 'F:\WinTeam\Database\Training1.mdf',
      MOVE 'DEV_Log' TO 'F:\WinTeam\Database\Training1_log.ldf'

RESTORE DATABASE training2
      FROM DISK = 'F:\Winteam\Backup\Training_Master.bak' WITH
      MOVE 'DEV_Data' TO 'F:\WinTeam\Database\Training2.mdf',
      MOVE 'DEV_Log' TO 'F:\WinTeam\Database\Training2_log.ldf'

RESTORE DATABASE training3
      FROM DISK = 'F:\Winteam\Backup\Training_Master.bak' WITH
      MOVE 'DEV_Data' TO 'F:\WinTeam\Database\Training3.mdf',
      MOVE 'DEV_Log' TO 'F:\WinTeam\Database\Training3_log.ldf'



Personally, I suggest not dropping the dbs -- which causes deletes and inserts to system tables and deallocation and reallocation of disk space -- instead do this:

RESTORE DATABASE training1
      FROM DISK = 'F:\Winteam\Backup\Training_Master.bak' WITH
      MOVE 'DEV_Data' TO 'F:\WinTeam\Database\Training1.mdf',
      MOVE 'DEV_Log' TO 'F:\WinTeam\Database\Training1_log.ldf',
                REPLACE   -- add this param to overwrite existing db
0
 
jdlambert1Commented:
I think this will do what you want, but instead of using a backup, it detaches, copies and then attaches the databases. I think it'll be a little faster this way.


DROP DATABASE Training1
GO
DROP DATABASE Training2
GO
DROP DATABASE Training3
GO

EXEC sp_detach_db 'MasterTraining'

xp_cmdshell 'copy C:\MSSQL\Data\MasterTraining.mdf C:\MSSQL\Data\Training1.mdf'
xp_cmdshell 'copy C:\MSSQL\Data\MasterTraining.mdf C:\MSSQL\Data\Training2.mdf'
xp_cmdshell 'copy C:\MSSQL\Data\MasterTraining.mdf C:\MSSQL\Data\Training3.mdf'

EXEC sp_attach_db 'MasterTraining', 'C:\MSSQL\Data\MasterTraining.mdf', 'C:\MSSQL\Data\MasterTraining.ldf'
EXEC sp_attach_db 'Training1', 'C:\MSSQL\Data\Training1.mdf'
EXEC sp_attach_db 'Training2', 'C:\MSSQL\Data\Training2.mdf'
EXEC sp_attach_db 'Training3', 'C:\MSSQL\Data\Training3.mdf'
0
 
SjoerdVerweijCommented:
Warning: untested! Also, you might want to adjust the C:\ path.


backup database mastertrainingdb to disk='c:\m.bak'
go

drop database training1
go

drop database training2
go

drop database training3
go

restore database training1 from disk='c:\m.bak'
go

restore database training2 from disk='c:\m.bak'
go

restore database training3 from disk='c:\m.bak'
go
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
ITDharamAuthor Commented:
OK, I'll give this stuff a shot and let you guys know.  Hopefully I'll get to this today.
0
 
Scott PletcherSenior DBACommented:
You should be very careful with this, in particular you should verify the status of the db before you detach it.  If you detach a suspect db you will likely never be able to recover it.
0
 
arbertCommented:
I agree--use backup/restore....Detach/reattach only when there's no other way....
0
 
ITDharamAuthor Commented:
All the Drop database statements worked, and the backup statement worked as well.  I used the sample provided by SjoerdVerweij

I get the following failure on step 9 which is the first step that attempts to restore, here is the statement from step 9  

Restore Database Himats_Copy from disk = 'F:\Winteam\Backup\Training_Master.bak'

Executed as user: AKAL\SQLCluster. The file 'F:\WinTeam\Database\Training_Master.mdf' cannot be overwritten.  It is being used by database 'Training_Master'. [SQLSTATE 42000] (Error 1834)  File 'DEV_Data' cannot be restored to 'F:\WinTeam\Database\Training_Master.mdf'. Use WITH MOVE to identify a valid location for the file. [SQLSTATE 42000] (Error 3156)  The file 'F:\WinTeam\Logs\Training_Master_log.ldf' cannot be overwritten.  It is being used by database 'Training_Master'. [SQLSTATE 42000] (Error 1834)  File 'DEV_Log' cannot be restored to 'F:\WinTeam\Logs\Training_Master_log.ldf'. Use WITH MOVE to identify a valid location for the file. [SQLSTATE 42000] (Error 3156)  RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.
0
 
ITDharamAuthor Commented:
In case that wasn't clear...this doesn't work.

I appears to be trying to overwrite the data and log file for the Training_Master db when I try and restore.

Something is missing here.
0
 
arbertCommented:
You have to add WITH MOVE to the restore statement to "move" (rename) the original files to their new name...
0
 
ITDharamAuthor Commented:
Thanks Scott, it worked.
0
 
suecohenCommented:
Hi everyone

I used a script similar to:

RESTORE DATABASE training1
     FROM DISK = 'F:\Winteam\Backup\Training_Master.bak' WITH
     MOVE 'DEV_Data' TO 'F:\WinTeam\Database\Training1.mdf',
     MOVE 'DEV_Log' TO 'F:\WinTeam\Database\Training1_log.ldf',
                REPLACE   -- add this param to overwrite existing db

to get a copy of the database at a different location. (In C++ and ADO code), then I added some new data to the database and wanted to copy the newly created database to yet another location.
Any attempt to copy the files in the file system to another location event by simple copy+paste gives an error stating that the database is currently at use. In spite the fact that the connection to it is closed.

I got the same result using Query Analyzer. Could not copy the newly created database files to another location manually.
Any Ideas as why this happens and how can it be solved ?

Thanks
0
 
jdlambert1Commented:
suecohen, are you the same user as ITDharam?
0
 
arbertCommented:
I'm guessing you're a different user.  This isn't your question, and the question is closed--please open a new one....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.