INFO: Creating a Duplicate SQL Database (69359)

MORE INFORMATION

1. Create a database device and log using the DISK INIT command.
   

         disk init
         name = "<device name>"
         physname = "<full path name of .dat file>"
         vdevno = <unused device number>
         size = <size of device>
     
   						

   NOTE: Issue an sp_helpdevice to find out what devices are available. For example:
   

         disk init
         name = "developmentdevice"
         physname = "d:\development\development.dat"
         vdevno = 8
         size = 5120
     
   						

2. Dump the desired database to the disk dump. Following the example, this would be the development database:
   

   dump database <database name> to <dumpdevice>

   For example:
   

   dump database development to diskdumpdevelopment

   NOTE: Create a dump device by issuing the following command:
   

   sp_addumpdevice "<disk|diskette>", "<logical name>", "<physical name>", <cntrltype>

   For example:
   

   sp_addumpdevice "disk", "diskdumpdevelopment", "d:\dump.dat",2

3. Rename the desired database, again following this example. The desired database is the development database:
   

   sp_renamedb <currentname>, <newname>

   For example:
   

   sp_renamedb development, production

4. Execute the following to re-create the original database on the new device:
   
   
   1. create database <database name> on <database device name> = <size of database>, <log device name> = <size of log>
      
      For example:
      

      create database development on developmentdevice = 5,
      developmentlog = 2

   2. sp_logdevice <database name>, <logname>
      
      For example:
      

      sp_logdevice development, developmentlog

   3. load database <database name> from <dumpdevice>
      
      For example:
      

      load database development from diskdumpdevelopment

Be sure to run the DBCC CHECKDB and DBCC CHECKALLOC diagnostics on the newly created database to ensure that it was created correctly.