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

Step 8: Recovery model considerations and backup requirements

If the database you have migrated or installed can be classed as “production”, ensure it is included in your backup plan.

One of things the recovery model of a database dictates is the type of backups that can be performed against it. For example, transaction log backups cannot be done against a database set to simple recovery mode. The restored or attached database will retain its original recovery mode, so you will need to check it once the database has been migrated.

If your server has a maintenance plan set up that backs up the transaction logs of “all user databases”, the log backup step will fail if the new database is in simple recovery mode. To get around, you will either need to modify the maintenance plan to exclude the new database or change the database’s recovery model to full.

Even if the restored database is in full recovery mode, you will need to take a full backup of it if transaction log backups are going to be taken later. Unless the new database has been fully backed up once, log backups will not have a base to start from and will fail. In such cases you will have a message like the following:

Msg 4214, Level 16, State 1, Line 1

BACKUP LOG cannot be performed because there is no current database backup.

Msg 3013, Level 16, State 1, Line 1

BACKUP LOG is terminating abnormally.

Step 9: Changing database code and associated configurations

This step will probably be required if your database is being migrated from an older version of SQL to a newer one. The DBA will be working with developers and system architects to identify commands and keywords that are no longer supported or those that can be replaced with newer features. Unlike most of the steps described above, this is not a simple, one off procedure but can comprise of a series of iterations.

For example, if the database is migrated from SQL Server 2000 to 2005 or 2008, code that uses xp_sendmail may need to be changed to use the newer database mail feature with the sp_send_dbmail stored procedure.  Likewise, isql and osql commands in stored procedures should be changed to make use of the sqlcmd utility.

Note that some of these features may require the DBA to make server or database level changes. For example, the Surface Are Configuration tool (or facet in SQL 2008) may need to be used to enable xp_cmdshell, CLR code, database mail or older style OPENROWSET queries.

Developers and DBAs will also need to look for linked server references within the code. New linked servers may need to be created and the code accordingly modified.

Not all required configuration changes may be evident from the code either. Databases tables sometimes store application specific metadata such as connection strings or server names. DBAs and developers will need to modify these data to reflect any changes.

A database property called TRUSTWORTHY controls whether CLR assemblies marked with EXTERNAL_ACCESS or UNSAFE mode will be able to access resources outside the databases. This property also dictates whether functions and procedures that use impersonation via EXECUTE AS clause can access resources outside the database.

By default, SQL Server keeps the TRUSTWORTHY property of a database to OFF. Even if your source database has this property set to ON, it will automatically revert to OFF when you detach and reattach the database. This is to ensure that no malicious user can detach and attach a user database and add malicious code there. To allow the newly attached database’s code to access external resources, the TRUSTWORTHY property needs to be set explicitly:


Also there are additional requirements for CLR assemblies to run successfully. The DBA will probably need to work closely with the developers in this area.


The final part of the series will examine full text catalogs and indexing, database collation, service broker considerations, encryption and high availability.

Pages: 1 2


No comments yet... Be the first to leave a reply!