ITDharam
asked on
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.
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.
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
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
ASKER
OK, I'll give this stuff a shot and let you guys know. Hopefully I'll get to this today.
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.
I agree--use backup/restore....Detach/r eattach only when there's no other way....
ASKER
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\Trainin g_Master.b ak'
Executed as user: AKAL\SQLCluster. The file 'F:\WinTeam\Database\Train ing_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\Train ing_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.
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\Trainin
Executed as user: AKAL\SQLCluster. The file 'F:\WinTeam\Database\Train
ASKER
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.
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.
You have to add WITH MOVE to the restore statement to "move" (rename) the original files to their new name...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Scott, it worked.
Hi everyone
I used a script similar to:
RESTORE DATABASE training1
FROM DISK = 'F:\Winteam\Backup\Trainin g_Master.b ak' WITH
MOVE 'DEV_Data' TO 'F:\WinTeam\Database\Train ing1.mdf',
MOVE 'DEV_Log' TO 'F:\WinTeam\Database\Train ing1_log.l df',
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
I used a script similar to:
RESTORE DATABASE training1
FROM DISK = 'F:\Winteam\Backup\Trainin
MOVE 'DEV_Data' TO 'F:\WinTeam\Database\Train
MOVE 'DEV_Log' TO 'F:\WinTeam\Database\Train
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
suecohen, are you the same user as ITDharam?
I'm guessing you're a different user. This isn't your question, and the question is closed--please open a new one....
DROP DATABASE Training1
GO
DROP DATABASE Training2
GO
DROP DATABASE Training3
GO
EXEC sp_detach_db 'MasterTraining'
xp_cmdshell 'copy C:\MSSQL\Data\MasterTraini
xp_cmdshell 'copy C:\MSSQL\Data\MasterTraini
xp_cmdshell 'copy C:\MSSQL\Data\MasterTraini
EXEC sp_attach_db 'MasterTraining', 'C:\MSSQL\Data\MasterTrain
EXEC sp_attach_db 'Training1', 'C:\MSSQL\Data\Training1.m
EXEC sp_attach_db 'Training2', 'C:\MSSQL\Data\Training2.m
EXEC sp_attach_db 'Training3', 'C:\MSSQL\Data\Training3.m