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

Hi all

One parameter is the database locations which must be specified for MOVE .. TO ..
In the example this is hardcoded as c:\ which probably is not what you want.

If you wish to find the default locations, these has to be read from the Registry. But to do so you must know the instance name of the engine as well and it starts to be a little complicated.
Another method is to create a temp database. Its files will be located in the default folder. Read the filenames and you have the default folder for later use. Then delete the temp database:

<code>
  use master
  create database defaultdb

  select 
    GroupId as DataTypeId,
      case GroupId
        when 1 then 'Data' 
        when 0 then 'Log'
        end 
        as DataTypeName,
      left(filename, 
        len(filename) - charindex('\', 
          ltrim(reverse(filename)))) + '\' 
        as DefaultPath
  from 
    defaultdb.dbo.sysfiles

  drop database defaultdb
</code>

It is not fancy but effective. 
A faster method is to use the name of an existing database located in the folder you wish to use, and then just run the select part of the code above.

/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