Solved

Help generate SQL Script

Posted on 2004-08-11
13
1,419 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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
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 500 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

695 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