?
Solved

Help generate SQL Script

Posted on 2004-08-11
13
Medium Priority
?
1,424 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:ITDharam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +3
13 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11776570
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
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 11776581
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
 
LVL 8

Author Comment

by:ITDharam
ID: 11776615
OK, I'll give this stuff a shot and let you guys know.  Hopefully I'll get to this today.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 11777156
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
 
LVL 34

Expert Comment

by:arbert
ID: 11777401
I agree--use backup/restore....Detach/reattach only when there's no other way....
0
 
LVL 8

Author Comment

by:ITDharam
ID: 11778143
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
 
LVL 8

Author Comment

by:ITDharam
ID: 11778300
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
 
LVL 34

Expert Comment

by:arbert
ID: 11778601
You have to add WITH MOVE to the restore statement to "move" (rename) the original files to their new name...
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 11778610
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
 
LVL 8

Author Comment

by:ITDharam
ID: 11778809
Thanks Scott, it worked.
0
 

Expert Comment

by:suecohen
ID: 12037761
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
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12039359
suecohen, are you the same user as ITDharam?
0
 
LVL 34

Expert Comment

by:arbert
ID: 12039382
I'm guessing you're a different user.  This isn't your question, and the question is closed--please open a new one....
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question