SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Moving Your Database to a New Server

By Paul Mu,

Time to Give Your Databases a New Home?

Do you have a server that is underperforming? Do you have end-users constantly reminding you that a server is slow at certain times of the day? Maybe the server has passed its used-by date and requires an overhaul, or maybe that you have a need to implement load balancing by having a second server (or a third). If this (or any other scenario that requires a replacement server) fits you then read on to find out what you need to consider and the approaches that are available to you to have your new server up and running in as little time as possible.

We will assume here that your aim is to have a replacement server, and that this server already has the OS, services and applications installed, which may or may not be an exact replica of the existing server. Therefore, what is left is the task of moving all relevant databases (and other SQL Server related objects) from one server to another.

Understanding What is Involved

Before delving into specifics, there are questions that need consideration upfront. The following is not meant to be an exhaustive list, but gives some idea as to what might be involved:

  1. What is your operation's system downtime policy?
  2. Is your database server also involved in replication (either as publisher or subscriber)?
  3. Is the move between same releases of SQL Server or not?
  4. Will the existing server continue to operate or be retired?
  5. Will the system databases be moved, in particular the master database?
  6. How should all this be implemented? Using scripts and/or SSIS packages?

Means of Approach

The fact that some operations are 12x7 has meant that system downtime is a premium that needs to be considered carefully. The following discussion will principally be concerned with restoring a database to another machine, and how quickly this can be achieved.

Most companies already have a database maintenance plan setup that performs regular full and differential (optional) database backups followed by frequent transaction log backups. Figure 1 shows a remote database restore process that makes use of the full and differential backups only, making it applicable for all database recovery models. Note the serial nature of the process and the sequence to be followed by each individual task. Steps 1 and 4 are tasks to perform against the current system, whereas steps 2, 5 and 6 are tasks to perform against the replacement server. Steps 3 and 7 are dependent on your system downtime policy, which may (in its simplest form) require all affected databases to be set in read-only mode.

The overall process execution time will largely depend on the time taken to perform the differential backup on the existing databases and the corresponding restores on the replacement server. Therefore, the smaller the differential backup the shorter the execution time. This can be achieved by performing the maintenance process as close as possible to the completion of the full backup (shaded regions between steps 1 and 4), thus reducing the size of the differential backup.

Figure 1. Restoring a database to a remote server using the full and differential backups.

In the event that transaction log backups exist, then the approach would be to perform a NORECOVERY restore of the full, differential (if available) and all transaction logs (in the appropriate order) on the replacement server before the start of system downtime, then after system downtime is initiated, perform a final transaction log backup on the existing system and then to restore it on the replacement server. As with the previous approach, the closer the transaction log backup is to the completion of the differential backup, the quicker will be the backup and restore process.

What happens if you have a replicated database? Well, it depends on whether you are dealing with a publisher or a subscriber. With the former, there is the issue of whether or not the replacement server takes over completely and whether it will have the same name as the existing server. The consequence being that replication may need to be rebuilt on the publisher as well as the need to move the existing server off the network (or offline it). This is a subject for another time, suffice to say that your network admin staff would be help you with such matters.

In the case where you are only interested in moving a subscription database, then the approach is much simpler. The trick is to use a no-sync subscription (to be created at point B' in Figure 1), particularly where very large tables are involved (There are caveats to using a no-sync subscription, but nothing that a DBA cannot handle). A prerequisite for using a no-sync subscription is to ensure that data in both publisher and subscriber databases are in sync. That is, to ensure that all undelivered commands in the distribution database have been delivered to the subscriber before performing the final backup.

The following script (to be executed at point A' in Figure 1) will help with this check, which displays the number of undelivered commands in the distribution database for all articles in all publications on that server.

use <publication database>

if object_id('tempdb..#dist_status') is not null 
 drop table #dist_status

select * 
into #dist_status
from distribution..MSdistribution_status 
select p.name as publication, a.name as article, t.*
from #dist_status t
	left outer join sysarticles a
 on t.article_id = a.artid
	inner join syspublications p
 on a.pubid = p.pubid
 where UndelivCmdsInDistDB > 0
 order by p.name,a.name,t.agent_id

Other Considerations

If you are migrating, say from SQL Server 2000 to SQL Server 2005, then you will need to run the SQL Server 2005 Upgrade Advisor against your server to identify an incompatibilities that will need taken care of beforehand. This includes DTS packages, whose meta-data are stored in the msdb database. There may be a need to rework some of the DTS packages before it will execute in 2005, otherwise look at migrating these packages using the Package Migration Wizard in SQL Server 2005.

If you do not wish to restore the system databases then there are other considerations for you to take note of. One of the most important being the user logins. There are three approaches to this, including (a) manually create the logins using the system stored procedures; (b) use off-the-shelf scripts to generate the logins (e.g. http://support.microsoft.com/default.aspx?scid=kb;en-us;246133) ; or (c) use the SSIS Transfer Logins Task. Method (a) will require some extra work to realign the SIDS for the logins, so the approach I would take is either (b) or (c). Then, there are also linked servers, which are easy enough to script out and apply.

Finally, there is a tool that can help with putting your scripts and process together. SQL Server Integration Services (or SSIS for short) is a powerful tool that can be used for such a task. I used it in my last server migration project, and I can say that it beats running scripts in QA (or the Management Studio equivalent in 2005). Once you are familiar with the SSIS interface and know how to configure the various components that you will be using, it is rather fun to design and to deploy.


In conclusion, the aim of this article is to provide you with some approaches that are available to you as you ponder the need to provide a new home for your databases. The focus of the discussion is more on the need to minimize the downtime to your production system. With some proper planning - and this involves doing as much of the work outside of the system downtime window - it is possible to achieve minimal impact to your production systems. Hopefully, there is sufficient information here for you to plan your next database-move project.

Total article views: 15534 | Views in the last 30 days: 20
Related Articles

Server Database Backup

Server Database Backup


how to take DataBase Backup from remote server to local system

how to take DataBase Backup from remote server to local system


System databases maintenance in SQL Server

Let’s talk about maintenance  of system databases in MS SQL Server. There are a few differences from...


System databases

System databases


Backup BizTalk Server Databases

Backup of BizTalk Server Databases