February 11, 2004 at 1:48 am
hi,
what is the best way to copy a database on one server to another server i.e. i have DB1 on server1 now i want to create the same DB1 on server2. i cannot perform backup and restore on any of the servers.
please tell me the best way to do it.
Thanks
Pratik
February 12, 2004 at 1:39 am
Go and have a look at http://www.red-gate.com. I am using sql-bundle with great pleasure.
February 12, 2004 at 7:30 am
We actually do this all the time. We have created a generic DOS Bat job which take parameters (server names, database names, etc.) to perform the action. We perfer the backup / restore process for master / slave database (test to production) type scenarios because it ensures all the database objects and data that were tested gets copied. The job performs the following steps:
1) Generate a script of the users that currently exist in the target database so they can be re-added later.
2) Get lists of the group names from the target database and the source database and compare the lists to ensure there has been no changes to the groups that will impact the re-adding of users to the target.
3) Backup the source database.
4) Restore to the target database.
5) Remove any users that may have been copied as a result of the restore.
6) Re-add the users using the script generated in step 1.
If the databases are at geographically different locations this process becomes 2 processes with an FTP (and ZIP and unZIP steps) between the processes.
We perfer this process to the detach / reattach process since the backup / restore is not effected by a difffering number of database files that may result from database administartion activities.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply