SQL server

Moving a database between SQL instances.

Moving a DB between two SQL instances can be done with a simple backup and restore, but there are a number of dependencies on the DB that are required to be working to have a full cycle connection between the end user and the DB. One example is the server level logins which are not moved with the DB. This data is saved in the master DB and will have to be explicitly copied over and linked with the DB users in the new instance. Another example is the SQL jobs there were running on the first instance. These jobs are saved in MSDB  and need to be recreated on the second instance. There are others like encryption keys, full text settings, etc.

Below is a list of entities/objects that are stored outside the DB:

  • Server configuration settings
  • Credentials
  • Cross-database queries
  • Distributed queries/linked servers
  • Encrypted data
  • User-defined error messages
  • Event notifications and Windows Management Instrumentation (WMI) events (at server level)
  • Extended stored procedures
  • Full-text engine for SQL Server (MSFTESQL) service properties
  • Jobs
  • Logins
  • Permissions
  • Replication settings
  • Service Broker applications
  • Startup procedures
  • Triggers (at server level)

This list was compiled on BOL and the details of these items can be viewed here.


Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s