October 13, 2005 at 2:03 am
Hi All.
(SQLserver 2000)
How can I duplicate an entire database including logins, structure and data. I have a need to make a copy of my live database for training purposes. Is it possible to do this using T-SQL in an SP. I would like the user see a list of existing dbs through my front end, pick the one they want to copy, give it a name, check it does not already exist then copy the whole db to the new name.
Thanks for any help with this.
CCB
October 13, 2005 at 2:30 am
My personal preference would be to have the nights backup files stored in a particular location.
When the user selects the database they want to copy they get a restore of last nights backup.
October 13, 2005 at 5:55 am
Yes a backup and restore is probably the easiest way to duplicate an online database. Is this backup & restore occurring on the same server or are you backing up from one server and restoring to another? In either case you will probably be using the restore command in conjunction with the "with move" parameter to have the database files restored to a different spot on the server.
If so, you should also look up sp_addlogin and how you can ensure the passwords of the logins are transferred between servers.
October 14, 2005 at 8:34 am
We do this a lot (not through an sp but manually) since we sometimes want to run tests on a database that contains actual production data but is not the ACTUAL production database. Here are the steps that we take. Hope this helps you develop your sp:
1) Get a list of the files in the backup file by using the following command
RESTORE FILELISTONLY
FROM Disk = 'BACKUPFILELOCATION’
Where BACKUPFILELOCATION = the location of your full database backup file
2) From the results, jot down the values in the “Logical Names” column:
3) Create a new database to restore the data to.
4) Use the following command to restore
USE MASTER
RESTORE DATABASE NEWDATABASENAME
FROM Disk = 'BACKUPFILELOCATION'
WITH REPLACE
MOVE 'DATA_LOGICALNAME' TO 'PHYSICAL_DATA_LOCATION',
MOVE 'LOG_LOGICALNAME' TO 'PHYSICAL_LOG_LOCATION'
GO
where NEWDATABASENAME = the name of the Destination Database BACKUPFILELOCATION = the physical location of the full backup file
DATA_LOGICALNAME = the "Logical Name" of the data portion on the backup file
PHYSICAL_DATA_LOCATION = the physical location on the server of the mdf file for the Destination Database (You can get this from the "Properties" screen for the database in the SQL Enterprise Manager)
LOG_LOGICALNAME = the "Logical Name" of the log portion on the backup file
PHYSICAL_LOG_LOCATION = the physical location on server of the ldf file for the Destination Database (You can get this from the "Properties" screen for the database in the SQL Enterprise Manager)
5) We have found that we need to use the “sp_change_users_login” command to synch up logins. You may want to refer to the BOL for more information on this command.
Norene Malaney
October 17, 2005 at 2:33 am
Thanks for the advice.
CCB
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply