[dba-SQLServer] Copy a database using a store procedure or function

Hi all

Just a note - the pass-through query property "Returns records" must be set to No.

Also, there seems to be several ways to name the data and log files. Some use a suffix of _data and _log, some only _log, and some nothing. As the file extension, .mdf or .ldf, tells it all I see no reason to use a suffix at all. However, my example base used _log.

Perhaps one of our gurus can clarify this.

/gustav



>>> Gustav at cactus.dk 27-03-2008 09:57 >>>
Hi Elizabeth - and John

The gun smoke distracted me so much from the original question that I had lost the request of John - to be able to perform this parameterized from code.

But it seems like your suggestion could be the key of a solution. I found a minor error ('AdventureWorks_Data' should read just 'AdventureWorks'). With that corrected I  simply moved your code (skipping the GO) into a pass-through query in Access:

BACKUP DATABASE somedb 
   TO DISK = 'c:\template2.bak'

RESTORE FILELISTONLY 
   FROM DISK = 'c:\template2.bak'

RESTORE DATABASE newfromtemplate2 
   FROM DISK = 'c:\template2.bak'
   WITH MOVE 'somedb' TO 'c:\newfromtemplate2.mdf',
   MOVE 'somedb_log' TO 'c:\newfromtemplate2.ldf'

Wow! In a few seconds you have a brand new database.
Of course, once a template has been created you can skip the first part with the backup.

Now, for this to work as to fulfill John's request, you will have to rewrite the SQL above to reflect the name of the new database but that can be done with a simple replace of "newfromtemplate2" to the actual new database name and then execute the query. This John can code with his left hand in Access and it shouldn't take much to write a small class in VB.net to perform the same two actions: replace the database name and send the SQL command to the server.

/gustav

>>> Elizabeth.J.Doering at wellsfargo.com 25-03-2008 21:10 >>>
I haven't used this (being pretty new to this myself), but I'm sure some
one else here can speak to it:

E. Make a copy of a database using BACKUP and RESTORE
This example uses both the BACKUP and RESTORE statements to make a copy
of the AdventureWorks database. The MOVE statement causes the data and
log file to be restored to the specified locations. The RESTORE
FILELISTONLY statement is used to determine the number and names of the
files in the database being restored. The new copy of the database is
named TestDB. For more information, see RESTORE FILELISTONLY
(Transact-SQL). 

 Copy Code 
BACKUP DATABASE AdventureWorks 
   TO DISK = 'C:\AdventureWorks.bak'

RESTORE FILELISTONLY 
   FROM DISK = 'C:\AdventureWorks.bak'

RESTORE DATABASE TestDB 
   FROM DISK = 'C:\AdventureWorks.bak'
   WITH MOVE 'AdventureWorks_Data' TO 'C:\testdb.mdf',
   MOVE 'AdventureWorks_Log' TO 'C:\testdb.ldf'
GO
 
This is from BOL for SQL Server 2005, which is often amazingly helpful.

HTH,


Liz 


Liz Doering 
elizabeth.j.doering at wellsfargo.com 
612.667.2447