Category Archives: Server Migration

How to migrate SQL Server from one machine to another

I recently was involved in a major migration project which included the move of few database production servers from one location to another. It was a very big project which implied a lot of inner coordination between teams and scheduling, so that the impact for the end-users would be minimal.

In case you are in the position of moving an SQL Server from one machine to another make sure you stick to the below presented checklist. The main purpose of the migration is to create a clone SQL Server with the same configuration, databases up to date, security and SQL Agent jobs in place. For this I always chose to restore both master and msdb along with all user databases. It seemed a lot easier than moving users/passwords, resolving orphaned users, updating default databases, updating system properties (sp_configure), migrating jobs, SQL Agent operators, recreating DB mail profiles, change data capture functionality, restoring partitioning information, and whole other specific database options that where once enabled.

After the migration everything must go smoothly and with no errors, otherwise, the migration didn’t go successfully. You have to make sure you cover all points and when you make the switch in connection strings everything is completed from DBA side of view.

Please if you find there is something to be added to the list below, feel free to comment and I will update this post.

I. Steps to do on the current Production server – [Server A]

  1. Check SQL Server properties – These must stay the same on the future server.

    use master
    select SERVERPROPERTY ('Collation')
    select SERVERPROPERTY ('Edition')
    select SERVERPROPERTY ('InstanceName')
    select SERVERPROPERTY ('ProductVersion')
    select SERVERPROPERTY ('ProductLevel')
    select SERVERPROPERTY ('ServerName')
  3. Check global configuration settings – These must stay the same on the future server.

    use master
    sp_configure 'show advanced options' , 1
  5. Check Databases Integrity
    It is necessary in order to be sure that restored backups on migrated server will be valid.

  7. Note down model database growing options.
    This setting is important for every new created database. My strong advice is that you never leave the default setting because it can be a big cause for poor performance. The default setting is 1 MB per data file and 10% per log file – imagine what it means for a database to allocate MB after MB if it needs to allocate say 50 MB or grow a log file that is 10 GB in size.

  9. Note down physical path for master, model, msdb, tempdb databases data and log files.

    use master
    select name, filename from sys.master_files where database_id in (1,2,3,4) order by database_id
  11. Note down physical path for mssqlsystemresource data and log files.
    SQL 2008: It should be in :\Program Files\Microsoft SQL Server\MSSQL10_50.\MSSQL\Binn\
    For SQL 2005: same as master database location.
    Query to run on SQL 2008 and upper versions in order to find the physical location:

    use master
    select name, filename,dbid from sys.master_files where database_id in (32767) 
  13. Perform Full backup on master and msdb

II. Steps to do on the future Production server – [Server B]

  1. Install same edition/version of SQL Server on the new machine.
    Keep in mind the following when installing:

    • Make sure the new server has mounted the same drive letters where system databases reside on old SQL Server. Otherwise, after the restore of master SQL will not be able to start because SQL will want to start the rest of system databases on physical paths from [Server A].
    • If you want to restore system databases then you need to keep the same edition (Standard, Entreprise, Business Intelligence) and version (ex: 2008 RTM, 2008 SP1, 2008 R2 RTM etc.). You cannot restore a backup of a system database (master, model, msdb) on a server build that is different from the build on which the backup was originally performed. An workaround would be, to replace manually the system databases files copied from [Server A] to [Server B]. This can be done only if the migrated server can be stopped. If not, then you must follow the procedure with “restore database….”
    • If you want to restore only user databases, then you can install the same or upper edition/version. If you install a lower edition/version than the current Production one, you might not be able to restore some of the databases having higher builds features which won’t be supported on lower builds.
    • Keep the same collation when installing SQL Server.
    • Create a Domain/Windows user account that will be used to start SQL Services.
  3. Backup system databases – master, model, msdb – and put in a safe location… in case you want to recover them.
  4. Stop SQL Server. Copy all system databases files and add them to the safe location.
  5. Create the physical locations for master, model, msdb, tempdb, mssqlsystemresource databases noted down at steps I.5 & I.6
  6. Give to the user running SQL Full security rights on the new folders created.
  7. Copy system databases backups made on [Server A] to [Server B] (step I.7).
  8. Stop SQL Server.
  9. Copy existing model, msdb and mssqlsystemresource database files from the installed location on [SERVER B] to the new created ones (step II.4). Afterwards you will be able to change the location for these databases. For now SQL will need old locations in order to load msdb, model, tempdb and mssqlsystemresource.
  10. Start SQL Server in single user mode. From an elevated cmd (started with administrator rights), access the right folder where sqlservr.exe executable is located and execute below commands. Normally you will find it at “C:\Program Files\Microsoft SQL Server\MSSQL[InstanceName]\MSSQL\Binn”

    There where cases when other services like SQL Agent or SQL Reporting took the single one connection and prohibited the administrator to restore master. This error is given:

    Reason: Server is in single user mode. Only one administrator can connect at this time.
    Before starting the SQL in single user mode, make sure you have stopped every SQL service and there is no one that will make a default connection to it (application, reporting)

    cd [drive]:\folder_name 
    sqlservr.exe -c -m
  11. Restore master database from the backup copied from [SERVER A] using a NEW cmd line started with administrator permissions or using a DAC (Dedicated Administrator Connection) connection.

    -- connect to SQL
    -- make sure you are connected to right server: 
    select @@servername 
    RESTORE DATABASE master FROM DISK = '[Drive]:\Backup_path\MASTER_.bak' WITH REPLACE;
  12. Exit the cmd prompt and Start SQL from Services (in multi-user)
  13. Now the system databases (except master) will be loaded from the new created paths. Check new files location for system databases. master will point to the right location. msdb, tempdb and model must be changed. If you will restore msdb you can specify at that time, the new location for the files. So here I will show how to move model and tempdb. For msdb is the same.

    select * from sys.master_files 
    use master
    Alter database tempdb modify file
    (name=tempdev, filename='[drive]:\new_location\tempdb.mdf')
    Alter database tempdb modify file
    (name=tempdev2, filename='[drive]:\new_location\tempdev2.ndf')
    Alter database tempdb modify file
    (name=templog, filename='[drive]:\new_location\templog.ldf')
    Alter database model modify file
    (name=modeldev, filename='[drive]:\new_location\model.mdf')
    Alter database model modify file
    (name=modellog, filename='[drive]:\new_location\modellog.ldf')
  14. Stop SQL Server and move the model files from old location to new location. tempdb will be re-created on the new specified location at every SQL restart so no need to move the files.
  15. Start SQL and make sure that the system database point to the right locations.

    select * from sys.master_files 
  16. [OPTIONAL] Restore msdb database. I personally encountered problems running Database mail after msdb restore on SQL 2008 R2. I ended up leaving the msdb orginal files and migrated all jobs by scripting, created msdb.dbo.cdc_jobs table (because cdc was in place as well) and re-configured database mail.
  17. Remove folders created at step II.4.
  18. Test some SQL users for connection.
  19. Linked Servers will not work because their credentials where encrypted with Service master key from originated server. In order to fix this you need to backup service master key from [Server A], give permission on the file to your user and copy to [Server B] to restore it.

    -- on [Server A]
    BACKUP SERVICE MASTER KEY TO FILE  ='[Drive]:\share\master_key' ENCRYPTION BY PASSWORD ='password'
    -- right click on file, properties, security, permissions: Give full permissions to your user. 
    -- on [Server B]
    RESTORE SERVICE MASTER KEY FROM FILE = '[Drive]:\master_keymaster_key' DECRYPTION BY PASSWORD = 'password' force
  20. Change the server name in system tables. @@servername variable will point to old server. It must be changed.

    -- check servername with ID = 0. The queries will return old server name [SERVER A]. [SERVER B] will not be found.  
    SELECT @@servername
    EXEC sp_helpserver 'SERVER B'
    EXEC sp_helpserver 'SERVER A'
    SELECT srvname FROM sysservers where srvid = 0
    -- DELETE old references to old servername.
    EXEC sp_droplinkedsrvlogin 'SERVER A', null
    EXEC sp_dropserver  'SERVER A'
    -- ADD new server name: [SERVER B]
    EXEC sp_addserver [SERVER B] , 'local' ,'duplicate_OK' 
    EXEC sp_addlinkedsrvlogin 'SERVER B', 'true'
    -- RESTART SQL. [SERVER B] will replace the old server name. 
    SELECT @@servername
    EXEC sp_helpserver 'SERVER B'
    SELECT srvname FROM sysservers where srvid = 0
  21. On versions below SQL 2008, you have to update also the originating server name from sysjobs as well. On higher versions, the reference in sysjobs is made by server_id which is always 0.

    SELECT * FROM msdb.dbo.sysjobs
    UPDATE msdb.dbo.sysjobs SET originating_server = @@SERVERNAME WHERE originating_server <> @@SERVERNAME

    III. Remaining Steps to do on the current Production server – [Server A]

  22. Perform Full backups for all user databases. It is a good idea to use backup compression and backup verification (RESTORE VERIFYONLY)
  23. Perform Differential backups.
  24. Copy all backups on [SERVER B].
  25. Restore Full backups and differential backups on [SERVER B] with ‘NO RECOVERY’ option.
  26. Perform Transactional backups.
  27. Copy all tran backups on [SERVER B] and restore them using with ‘Recovery’ option.
  28. And a last step, after you restore all user databases, keep in mind to change databases owners to ‘sa’ user, otherwise you will receive errors of executing sys schema objects like these ones:

    The EXECUTE permission was denied on the object ‘’, database ‘mssqlsystemresource’, schema ‘sys’. (.Net SqlClient Data Provider)

  29. use Database_Name
    EXEC sp_changedbowner 'sa'

Restore SQL 2008 R2 Database on SQL 2008 SP1 instance

It has been a long time since I wrote a new post, but now I ‘m back after a very busy period and I would like to share with you a recent problem I had with a restore from a higher version of SQL Server – SQL 2008 R2 to a lower version – SQL 2008 SP1.

If you search the internet, you will be sad to find out that:

  1. The restore from a database backup made on a higher SQL version will fail on a lower SQL version because the SQL is not up-level compatible.
  2. The attaching of data and log files created on a higher SQL version will fail on a lower SQL version because the SQL is not up-level compatible.
  3. Scripting entire database (SQL 2008 R2: Tasks –> generate scripts –> script entire database –> advanced: select to script schema and data) and executing on destination server can get you what you need but it works only if the database is not very big and the output of the sql file is in reasonable size parameters.
  4. Another scripting method is to do it step by step (object by object): create database, export tables with export/import tool (easier because you don’t have to deal with constraints), create primary keys, foreign keys, default and other check constraints, indexes, views, stored procedures, migrate users security, cdc feature, triggers… etc. As you can see it is time consuming and you can’t do a single mistake. If the database was as big as I had to deal with (over 60 tables) then it can become a nightmare.

Gladly, there is one single possibility of “restoring” the database and having all of needed objects inside in a very elegant way 🙂
The duration depends on the database size. If you have do it relatively urgent like I did and can’t afford to go through the above alternative steps (3 and 4), please read further. By the courtesy of one of my readers, as he pointed out: very important to know is also that this method will work if the database migrated doesn’t have encrypted procedures. To use this method, you need to remove encrypted procedures and recreate them after the migration. I couldn’t find anywhere this tip even if this is an SQL feature and I thought many can find it usefull. On MSDN, the solution presented below that works for a database downgrade is treated only as an database upgrade option. (Using the Copy Database Wizard).

Things that are good to now

The database version is a number stamped in the boot page of a database that indicates the SQL Server version of the most recent SQL Server instance the database was attached to. The database version number does not equal the SQL Server version. For example, doing the following:

SELECT @@version;

will display information about the SQL Server installation build, meaning the whole engine. The database version however is not the same.
Running this will get you the database version:

USE master
SELECT DatabaseProperty ('DATABASE NAME', 'version');

Database compatibility level determines how certain database behaviors work. This is set on database level. You can have a lower database compatibility level than the default one. On 2008 instances the default compatibility level is 100, and it can be 90 or 80 if you need the database to be compatible with a lower SQL Server edition. Setting a lower compatibility version does not allow the database to be restored on a lower SQL Server version.

In the following example, I will detail how you can use the Copy Database Wizard to migrate a database created on SQL 2008 R2 to SQL 2008 SP1, meaning to downgrade it from database version 661 to 655.

As a note to this, and by the courtesy of one of my readers I have to add that this option is only available if you don’t have encrypted stored procedures inside your database. If you do you will receive an error and will need to escalate this problem: either drop the procedures and re-create them after the copy database process described below, either un-encrypt the procedures before the copy database process and re-encrypt after.

Copy Database method

  1. Step 1 – Select source and destination servers
  2. Start by selecting the database that you want to downgrade. Right click on it and go to: Tasks –> Copy Database… Press Next. In “Select Source Server” window specify the source server name and the authentication type. In my example I used Windows. If you use SQL authentication, it should be with a user that has sysadmin role assigned. Both Windows and SQL need sysadmin role.

    Press Next and in following screen, select the destination server with same considerations on authentication type as on previous screen.

  3. Step 2 – Select Transfer method
  4. There are 2 options: Use detach and attach method and Use SQL Management Object method.
    If I would select the detach/attach method, the database would be detached, the data and log files would be copied to the destination server and finally attached to the destination server. This method would fail because the attach method is not supported on lower SQL versions.

    This is why you need to select the second option: SMO method.

    Select Transfer Method

    Select Transfer Method


  5. Step 3 – Select Database
  6. Select Databases

    Select Databases


  7. Step 4 – Select Database file location
  8. Select the location on disk for the future database files:

    Configure Destination Database (1 of 1)

    Configure Destination Database (1 of 1)


  9. Step 5 – Select Server objects
  10. You can migrate Logins, SQL Agent Jobs and other objects. I selected only the logins for the migrated database.

    Select Server Objects

    Select Server Objects


  11. Step 6 – Configure the Package
  12. The SSIS package created will be integrated into a new SQL Agent job with the same name as the package but you can change it. This can be executed immediately or scheduled. In this example I will execute it immediately. Also, you can store the log file and to check afterwards what exactly happened beyond this wizard.

    Configure the Package

    Configure the Package

    Schedule the Package

    Schedule the Package


  13. Step 7 – Finalize the migration
  14. Review that all the information displayed in below screen are valid.

    Complete Wizard

    Complete Wizard


At last if someone is curious of what exactly this SSIS package is doing, I thought it would be useful to post the package log from both types of methods: Detach/Attach & SMO. You will see the difference between them starting the moment when the data and log files are created. In SMO, these are created not copied.


Transferring database DB_2008_R2 from SERVER2008R2 server as DB_2008_R2 to SERVER2008SP1 server
Dropping all connections to DB_2008_R2
All active connections to DB_2008_R2 are dropped
Detaching database DB_2008_R2 from source SQL Server
Database DB_2008_R2 detached from source SQL Server
Copying file D:\SERVER2008SP1\MSSQL\DATA\DB_2008_R2.mdf to G:\SERVER2008R2\MSSQL10.LCS\MSSQL\DATA\DB_2008_R2.mdf overwrite set to False
Copying file D:\SERVER2008SP1\MSSQL\DATA\DB_2008_R2_log.ldf to G:\SERVER2008R2\MSSQL10.LCS\MSSQL\DATA\DB_2008_R2_log.ldf overwrite set to False
Attaching database DB_2008_R2 to destination SQL Server as database DB_2008_R2
Attaching database DB_2008_R2 to SERVER2008SP1, SetBrokerState=True, AttachOptions=None
OnError,SERVER2008R2,DOMAIN\SQLUSER,SERVER2008R2_SERVER2008SP1_Transfer Objects Task,10/21/2011 12:00:32 PM,10/21/2011 12:00:32 PM,0,0x,Attach database failed for Server ‘SERVER2008R2’.
InnerException–>The database ‘DB_2008_R2’ cannot be opened because it is version 661. This server supports version 655 and earlier. A downgrade path is not supported.
Could not open new database ‘DB_2008_R2’. CREATE DATABASE is aborted.


Transferring database DB_2008_R2 from backenddc server as DB_2008_R2 to backenddc\LCS server
Added data file DB_2008_R2 in path G:\LCS_INSTANCE_2008_SP1\MSSQL10.LCS\MSSQL\DATA\DB_2008_R2.mdf to file group [PRIMARY]
Added log file DB_2008_R2_log in path G:\LCS_INSTANCE_2008_SP1\MSSQL10.LCS\MSSQL\DATA\DB_2008_R2_log.ldf
Transferring data to database DB_2008_R2 from DB_2008_R2
Transferred data to DB_2008_R2
Transferring server objects to server backenddc\LCS from backenddc
Database transfer is complete.
Transfer objects finished execution.