Systems Engineering and RDBMS

Archive for August 13th, 2008

Copying Databases from one Instance to Another – I

Posted by decipherinfosys on August 13, 2008

One of the very common tasks for developers or DBAs is to copy or move around databases (SQL Server) or Schemas (Oracle) from one instance to another. In a series of posts, we will cover the different ways in which it can be done. We will cover SQL Server first and then will start the Oracle ones. In the case of SQL Server, there are a lot of different ways in which this can be achieved. Let’s highlight the different options available and then we can dig into each one of them to see how it can be achieved.

a) Use attach/detach of the databases from the source instance to the destination instance.

b) Good old backup and restore i.e. backup the database from the source instance and restore it on the destination instance.

c) Use DTS (SQL Server 2000) or SSIS (SQL 2005 or SQL 2008 ) to copy over the database from the source to the destination instance.

d) Script out the database into DDL and DML statements and then run those scripts on the destination instance to re-create the schema and then load up the data.

e) Script out only the DDL and re-create the database with the schema objects in the destination instance and then use either bcp or SSIS or insert scripts or bcp/bulk insert combination to extract and load up the data.

f) The Copy Database wizard can be used.

g) Replication can be set up.

One key thing to remember in all of these is that the user databases also have a dependency on the master and the msdb databases. Some common dependencies:

Master Database:

a) The logins for the instance are stored in the master database and thus when you are copying/moving the user database(s) from one instance to another one, you need to script out the logins and the permissions and move them first.

b) If you have server level triggers on the source instance, then you need to make sure that those are moved as well to the other instance. Another link for this.

c) Instance settings: You would need to make sure that both the instances have the same configuration settings – this is typically already taken care of if you have standardized your environment though there can very well be some differences between environments.

d) Linked Servers: If you are using linked servers on the source instance, you need to make sure that you have scripted them out and have moved them to the destination instance as well.

e) If you are using data encryption and if the database master key is protected by the service master key on the source instance, you will need to re-create that on the destination instance as well.

f) Permissions: Any grant, revoke or deny permissions on system objects or server level permissions need to be taken care of on the destination instance as well.

g) Any startup procedures need to be re-created on the destination instance.

MSDB Database:

a) If on the source instance you have maintenance jobs like a database maintenance job that is implemented via SQL Agent, then that meta-data is stored in the msdb database and you would need to script those out and re-create those jobs on the destination instance as well.

b) If you have server level event notifications, then those are stored in the MSDB database. So, if you have an application that relies on the server level event notification, then you will need to re-create those on the destination instance as well. Same is true for any explicit route that your service might be using when you use service broker.

And three more things to be aware of:

1) If you are using any cross database queries which basically means that you have the DB_CHAINING and TRUSTWORTHY options set to YES, then when you move the databases using attach/detach, you have enable those options again since the attach and detach steps disable those options.

2) If you are using the Full Text Search feature, then the properties are set on the MSFTESQL service by a pass through procedure and these properties are stored in the registry. You will need to make sure that those are available on the destination server as well.

3) If you have replication configured on the source instance and you restore a backup of the replicated database on the destination instance, you would need to re-create all publications and subscriptions once the restore is done.

4) When you are done copying/moving the user database(s) from one instance to another one, you should always compute 100% statistics.  You can get the code for USP_UPD_STATS on this blog and if you search for “Statistics”, you can read more about it in different posts for SQL Server as well as Oracle.

In the next post in this series, we will discuss the attach/detach method and then in each subsequent posts, we will cover each one of the options mentioned above.

Posted in SQL Server | 3 Comments »