ID Number: Q69359
1.10 1.11 4.20
OS/2
Summary:
This article describes one method for creating a duplicate SQL
database.
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 dump disk. 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
Be sure to run the DBCC CHECKDB and DBCC CHECKALLOC diagnostics on the
newly created database to ensure that it was created correctly.
Additional reference words: 1.10 1.11 4.20 Dumping and loading,
Database repair