Solved

Help generate SQL Script

Posted on 2004-08-11
13
1,410 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
  • 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
 
LVL 69

Expert Comment

by:ScottPletcher
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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:
ScottPletcher 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now