Migrating SQL Server Databases – The DBA’s Checklist (Part 1)

It is a fact of life: SQL Server databases
change homes. They move from one instance to another, from one version to the
next, from old servers to new ones.  They move around as an organisation’s data
grows, applications are enhanced or new versions of the database software are
released. If not anything else, servers become old and unreliable and databases
eventually need to find a new home. Consider the following scenarios:

1.     A
new  database application is rolled out in a production server from the
development or test environment

2.     A
copy of the production database needs to be installed in a test server for
troubleshooting purposes

3.     A
copy of the development database is regularly refreshed in a test server during
the system development life cycle

4.     A
SQL Server is upgraded to a newer version. This can be an in-place upgrade or a
side-by-side migration

5.     One
or more databases need to be moved between different instances as part of a
consolidation strategy. The instances can be running the same or different
version of SQL Server

6.     A
database has to be restored from a backup file provided by a third party
application vendor

7.     A
backup of the database is restored in the same or different instance for
disaster recovery

8.     A
database needs to be migrated within the same instance:

a.     Files
are moved from direct attached storage to storage area network

b.    The
same database is copied under a different name for another application

Migrating SQL Server database applications is
a complex topic in itself. There are a number of components that can be
involved: jobs, DTS or SSIS packages, logins or linked servers are only few
pieces of the puzzle. However, in this article we will focus only on the
central part of migration: the installation of the database itself.

Unless it is an in-place upgrade, typically
the database is taken from a source server and installed in a destination
instance.  Most of the time, a full backup file is used for the rollout. The
backup file is either provided to the DBA or the DBA takes the backup and
restores it in the target server. Sometimes the database is detached from the
source and the files are copied to and attached in the destination.

Regardless of the method of copying, moving,
refreshing, restoring or upgrading the physical database, there are a number of
steps the DBA should follow before and after it has been installed in the
destination. It is these post database installation steps we are going to
discuss below. Some of these steps apply in almost every scenario described
above while some will depend on the type of objects contained within the
database.  Also, the principles hold regardless of the number of databases

1:  Make a copy of data and log files when attaching and detaching

When detaching and attaching databases,
ensure you have made copies of the data and log files if the destination is
running a newer version of SQL Server. This is because once attached to a newer
version, the database cannot be detached and attached back to an older version.
Trying to do so will give you a message like the following:

Msg 602, Level 21, State 50, Line 1

not find row in sysindexes for database ID 6, object ID 1, index ID 1. Run DBCC
CHECKTABLE on sysindexes.


If you try to backup the attached database
and restore it in the source, it will still fail.

Similarly, if you are restoring the database
in a newer version, it cannot be backed up or detached and put back in an older
version of SQL. Unlike detach and attach method though, you do not lose the
backup file or the original database here.

When detaching and attaching a database, it
is important you keep all the log files available along with the data
files. It is possible to attach a database without a log file and SQL Server
can be instructed to create a new log file, however this does not work if the
database was detached when the primary file group was read-only. You will need
all the log files in such cases.

2: Change database compatibility level

Once the database has been restored or
attached to a newer version of SQL Server, change the database compatibility
level to reflect the newer version unless there is a compelling reason not to
do so. When attaching or restoring from a previous version of SQL, the database
retains the older version’s compatibility level.  The only time you would want
to keep a database with an older compatibility level is when the code within
your database is no longer supported by SQL Server. For example, outer joins
with *= or the =* operators were still possible in SQL 2000 (with a warning
message), but not in SQL 2005 anymore. If your stored procedures or triggers
are using this form of join, you would want to keep the database with an older
compatibility level.  For a list of compatibility issues between older and
newer versions of SQL Server databases, refer to the Books Online under the sp_dbcmptlevel

Application developers and architects can
help you in deciding whether you should change the compatibility level or not.
You can always change the compatibility mode from the newest to an older
version if necessary. To change the compatibility level, you can either use the
database’s property from the SQL Server Management Studio or use the sp_dbcmptlevel
stored procedure.


Bear in mind that you cannot run the built-in
reports for databases from SQL Server Management Studio if you keep the
database with an older compatibility level. The following figure shows the
error message I received when trying to run the “Disk Usage by Top Tables”
report against a database. This database was hosted in a SQL Server 2005 system
and still had a compatibility mode 80 (SQL 2000).



Run the DBCC UPDATEUSAGE command
against the migrated database when upgrading to a newer version of SQL Server.
This is particularly important if you are migrating from a pre-SQL Server 2005
version. Up until SQL 2005, the table or index row counts and the page counts
for data, leaf, and reserved pages could become out of synch with time in the
database system tables. DBCC UPDATEUSAGE command corrects these inaccuracies
and ensures the counts are updated.

To run the DBCC UPDATEUSAGE command against
your target database, execute the following:


If there are no inaccuracies found, the
command does not return any data. If there are inaccuracies found and DBCC
UPDATE USAGE fixes these inaccuracies, the detailed report is shown in the

You should run the DBCC UPDATEUSAGE command
before you run DBCC CHECKDB (discussed next). From SQL Server 2005, the DBCC
CHECKDB command has been modified to trap errors related to incorrect row or
page counts. However If the counts are wrong in the source database and you run
DBCC CHECKDB against your newly restored/attached database, you will get a
warning message like the following:

2508, Level 16, State 3, Line 1

In-row data RSVD page count for object “table_name“, index ID
0, partition ID 58037252456448, alloc unit ID 58037252456448 (type In-row data)
is incorrect. Run DBCC UPDATEUSAGE.


a commonly used command that can check the allocation, structural and logical
integrity of a database and its objects. Although it can be used to fix minor
issues with the built-in REPAIR options, they should not be used the first time
you are running it against your migrated database. This is because you want to
know if the database you just installed is structurally okay. You would execute
a command like the following:


Starting from SQL Server 2005, DBCC CHECKDB
has another option: DATA_PURITY.  This option can detect field values
that are invalid or out-of-range. An example of such an anomaly would be a
datetime field with values outside the acceptable range. For databases upgraded
from pre-SQL Server 2005 versions, DBCC CHECKDB does not automatically check
column values until the command has been run with the DATA_PURITY option error
free. After that, you do not need to specify this option as DBCC CHECKDB will
automatically check for entity integrity in the field values.

If everything is okay, the command will run
successfully and you will see something like the following at the end of the

CHECKDB found 0 allocation
errors and 0 consistency errors in database ‘database_name‘.

execution completed. If DBCC printed error messages, contact your system

5: Update statistics


Server automatically generates any missing statistics during the query
optimisation phase. As statistics can become out-of-date and stale over time,
setting the AUTO_UPDATE_STATISTICS option to ON ensures the query optimizer
will automatically update any out-of-date stats during optimisation.

I would usually keep the “Auto Update
Statistics Asynchronously
” option to its default value of “false”. This
option – introduced in SQL Server 2005 and also present in SQL Server 2008 –
controls how SQL Server will behave if it finds any out-of-date statistics
during the optimisation phase. Setting this option to “true” will let the query
run with the old statistics. It will then update the stats outside the query. 
This ensures subsequent runs of the query will use the new statistics. When this
property is set to false, query optimiser will wait till new statistics are

Part 2 of the series will examine setting database options, mapping database users to logins, recovery models, backups, and changing the code associated with configurations


2 Responses to “Migrating SQL Server Databases – The DBA’s Checklist (Part 1)”

  1. Is a very good one – keep posting. Thanks

    • I would like to point out that according to Microsoft regarding sp_dbcmptlevel:

      “This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use ALTER DATABASE Compatibility Level instead.”

      Source: http://msdn.microsoft.com/en-us/library/ms178653.aspx

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |