Solved

Help generate SQL Script

Posted on 2004-08-11
13
1,414 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

856 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