INF: Creating a Duplicate SQL Database

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server versions 4.2, 4.21, and 4.21a

SUMMARY

This article describes one method for creating a duplicate SQL database.

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:

   a. 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
   
      b. sp_logdevice <database name>, <logname>
   
         For example:
   
            sp_logdevice development, developmentlog
   
      c. load database <database name> from <dumpdevice>
   
         For example:
   
            load database development from diskdumpdevelopment