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.
  2.  

    use master
    GO
    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.
  4.  

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

    DBCC CHECKDB ('DATABASE_NAME')
  7. Note down model database growing options.
  8.  
    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.
  10.  

     
    use master
    go
    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.
  12.  
    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
    go
    select name, filename,dbid from sys.master_files where database_id in (32767) 
  13. Perform Full backup on master and msdb
  14.  

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

  1. Install same edition/version of SQL Server on the new machine.
  2.  
    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
    sqlcmd -SMACHINENAME\SERVERINSTANCE -E 
    -- make sure you are connected to right server: 
    select @@servername 
    GO
    RESTORE DATABASE master FROM DISK = '[Drive]:\Backup_path\MASTER_.bak' WITH REPLACE;
    GO
    
    
  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
    go
    
    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'
    go
    
Leave a comment ?

62 Comments.

  1. Irena, this is great. Thank you. I have one question. If you needed to upgrade SQL server and move to another machine (Upgade OS and Physical to virtual) would you A.) first move to the same version of SQL (in this case SQL 2005) and then upgrade SQL on the the new machine or B.) bite the bullet and move over the databases to the new version of SQL and then deal with all the move users ect.

  2. Thank you for your comment Tom. About your question. First, it depends if I need to preserve more than the users from master db, like linked servers, db options. If yes than A is the way to follow. Otherwise, the simplest way would be to do an in-place upgrade, if possible of course.
    Anyway, in both cases, must be considered the differences between versions (backward compatiblity) and dealt with that before doing the upgrade. Hope it helped you!

  3. Hi Tom, I replied below. Sorry for the delay.

  4. Hi Yruksha,

    Hi
    I am from india and following your post,
    You are doing fantastic job Yruksha keep it and post your latest
    complicated task and experiences.
    I will definately ask for your help whenever i need it.Hope you can help?

    Thanks
    Atul
    msgsatul@gmail.com

  5. Thank you!

  6. Great article. Thanks!

  7. Great Work, please try to post a script to find and remove unused Tables,stored procedures and functions in sql server.thanks in advance

  8. Very comprehensive write-up yrushka 🙂 My compliments!

    As you seem to have a lot of migration experience, here’s a – most likely – simple question for you.

    We’re looking at creating a new server with upgraded hardware. Software (OS and SQL server level) as well as drive volumes and file paths will be identical.

    We will decomission the old server and use this new server during an approved downtime (so the destination server will have the original name once we shut down the source server).

    1) Can we simply copy over the system DB files and folders and overwrite these locations on the DESTINATION server? Since “everythign is identical” all paths should be found, do you forsee an issue with starting SQL after copying the system DB files and folders?

    2) Will mirroring and/or logshipping be preserved (since all this information is contained in the system DBs)? Of course we will graciously stop all running jobs and agents first.

    Much appreaciated for your input in this! 🙂

  9. 1. What version of SQL are you considering to migrate? Honestly, I did this on SQL 2000 once, but don’t remember what issues appeared then. I suspect you could face the problem with linked servers because of Service Master Key being different on the new installed instance. But if you install the same edition, same version, same type of instance, and overwrite the system databases after you stop SQL it could work. Check the mssqlsystemresource hidden database files location as well and double check that the destination server has them. Backup all original system databases that belong to the new installation and if something goes wrong you can restore them and follow the described steps in this article.

    2. All information is not contained only in system master database. There are physical references about service master key, and certificates (if you used this method to trust the servers). Mirroring I think will not work, since the IP will be different even if the server name is the same. Also, if there are certificates & master keys created on a physical disk location, manual move will be necessary. It is possible you will need to recreate the mirroring and I really suggest you do it. I assume at the downtime, the mirroring server will take the principal role, and after the move you wanted to resume the mirroring, considering that no backups will be done on the migrated server. But it is not a good practice. You must backup all system and user db’s before any migration starts. system DB files or user can become corrupted at the movement.

  10. When I try to run sqlcmd, I get a message “Only one administrator can connect at this time”… How can I restore the Master db?

  11. You probably had another connection open on SQL taken by another process in advance for yours. Take a look at II.9 point in my post, since I have made an update on it, how to start SQL in single mode and reserving the connection only for sqlcmd process.

  12. Microsoft Windows [Version 6.1.7601]
    Copyright (c) 2009 Microsoft Corporation. All rights reserved.

    C:\Windows\system32>cd \prog
    The system cannot find the path specified.

    C:\Windows\system32>cd \Prog*

    C:\Program Files>cd “microsoft sql server”

    C:\Program Files\Microsoft SQL Server>cd mss*&

    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER>cd mss*

    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL>cd binn

    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn>sqlservr
    .exe -c -m”sqlcmd”
    2014-05-20 08:03:49.57 Server Microsoft SQL Server 2008 R2 (RTM) – 10.50.16
    00.1 (X64)
    Apr 2 2010 15:48:46
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service P
    ack 1) (Hypervisor)

    2014-05-20 08:03:49.60 Server (c) Microsoft Corporation.
    2014-05-20 08:03:49.62 Server All rights reserved.
    2014-05-20 08:03:49.62 Server Server process ID is 1424.
    2014-05-20 08:03:49.62 Server System Manufacturer: ‘VMware, Inc.’, System M
    odel: ‘VMware Virtual Platform’.
    2014-05-20 08:03:49.62 Server Authentication mode is MIXED.
    2014-05-20 08:03:49.62 Server Logging SQL Server messages in file ‘C:\Progr
    am Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG’.
    2014-05-20 08:03:49.63 Server This instance of SQL Server last reported usi
    ng a process ID of 2084 at 5/20/2014 12:50:14 PM (local) 5/20/2014 5:50:14 PM (U
    TC). This is an informational message only; no user action is required.
    2014-05-20 08:03:49.63 Server Registry startup parameters:
    -d C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\D
    ATA\master.mdf
    -e C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\L
    og\ERRORLOG
    -l C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\D
    ATA\mastlog.ldf
    2014-05-20 08:03:49.63 Server Command Line Startup Parameters:
    -c
    -m sqlcmd
    2014-05-20 08:03:49.71 Server SQL Server is starting at normal priority bas
    e (=7). This is an informational message only. No user action is required.
    2014-05-20 08:03:49.71 Server Detected 1 CPUs. This is an informational mes
    sage; no user action is required.
    2014-05-20 08:03:50.20 Server Perfmon counters for resource governor pools
    and groups failed to initialize and are disabled.
    2014-05-20 08:03:50.23 Server Using dynamic lock allocation. Initial alloc
    ation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an infor
    mational message only. No user action is required.
    2014-05-20 08:03:50.55 Server Node configuration: node 0: CPU mask: 0x00000
    00000000001:0 Active CPU mask: 0x0000000000000001:0. This message provides a des
    cription of the NUMA configuration for this computer. This is an informational m
    essage only. No user action is required.
    2014-05-20 08:03:50.68 Server Database Mirroring Transport is disabled in t
    he endpoint configuration.
    2014-05-20 08:03:50.70 spid7s Warning ******************
    2014-05-20 08:03:50.71 spid7s SQL Server started in single-user mode. This
    an informational message only. No user action is required.
    2014-05-20 08:03:50.79 spid7s Starting up database ‘master’.
    2014-05-20 08:03:51.00 spid7s 4 transactions rolled forward in database ‘ma
    ster’ (1). This is an informational message only. No user action is required.
    2014-05-20 08:03:51.01 spid7s 0 transactions rolled back in database ‘maste
    r’ (1). This is an informational message only. No user action is required.
    2014-05-20 08:03:51.01 spid7s Recovery is writing a checkpoint in database
    ‘master’ (1). This is an informational message only. No user action is required.

    2014-05-20 08:03:51.31 spid7s FILESTREAM: effective level = 0, configured l
    evel = 0, file system access share name = ‘MSSQLSERVER’.
    2014-05-20 08:03:51.49 spid7s SQL Trace ID 1 was started by login “sa”.
    2014-05-20 08:03:51.52 spid7s Starting up database ‘mssqlsystemresource’.
    2014-05-20 08:03:51.55 spid7s The resource database build version is 10.50.
    1600. This is an informational message only. No user action is required.
    2014-05-20 08:03:51.83 spid7s Server name is ‘FRA-MAS’. This is an informat
    ional message only. No user action is required.
    2014-05-20 08:03:51.85 spid9s Starting up database ‘model’.
    2014-05-20 08:03:52.11 spid9s Clearing tempdb database.
    2014-05-20 08:03:52.51 Server A self-generated certificate was successfully
    loaded for encryption.
    2014-05-20 08:03:52.58 Server Server is listening on [ ‘any’ 1433].
    2014-05-20 08:03:52.58 Server Server is listening on [ ‘any’ 1433].
    2014-05-20 08:03:52.59 Server Server local connection provider is ready to
    accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
    2014-05-20 08:03:52.59 Server Server local connection provider is ready to
    accept connection on [ \\.\pipe\sql\query ].
    2014-05-20 08:03:52.62 Server Server is listening on [ ::1 1434].
    2014-05-20 08:03:52.67 Server Server is listening on [ 127.0.0.1 143
    4].
    2014-05-20 08:03:52.67 Server Dedicated admin connection support was establ
    ished for listening locally on port 1434.
    2014-05-20 08:03:52.75 Server The SQL Server Network Interface library succ
    essfully registered the Service Principal Name (SPN) [ MSSQLSvc/FRA-MAS.telco1.l
    oc ] for the SQL Server service.
    2014-05-20 08:03:52.75 Server The SQL Server Network Interface library succ
    essfully registered the Service Principal Name (SPN) [ MSSQLSvc/FRA-MAS.telco1.l
    oc:1433 ] for the SQL Server service.
    2014-05-20 08:03:52.75 Server SQL Server is now ready for client connection
    s. This is an informational message; no user action is required.
    2014-05-20 08:03:53.21 spid11s Error: 9954, Severity: 16, State: 1.
    2014-05-20 08:03:53.21 spid11s SQL Server failed to communicate with filter
    daemon launch service (Windows error: The service cannot be started, either bec
    ause it is disabled or because it has no enabled devices associated with it.
    ). Full-Text filter daemon process failed to start. Full-text search functionali
    ty will not be available.
    2014-05-20 08:03:53.46 spid11s Starting up database ‘msdb’.
    2014-05-20 08:03:53.46 spid12s Starting up database ‘ReportServer’.
    2014-05-20 08:03:53.46 spid13s Starting up database ‘ReportServerTempDB’.
    2014-05-20 08:03:53.54 spid9s Starting up database ‘tempdb’.
    2014-05-20 08:03:54.31 spid7s Recovery is complete. This is an informationa
    l message only. No user action is required.
    2014-05-20 08:06:10.96 Logon Error: 18461, Severity: 14, State: 1.
    2014-05-20 08:06:10.96 Logon Login failed for user ‘FRANKLIN\sqladmin’. Re
    ason: Server is in single user mode. Only one administrator can connect at this
    time. [CLIENT: ]

  13. Restart server after disabling all services. This is still the error I get when I try to run sqlcmd. Any ideas?

  14. here you go, I have made a little video tutorial. Do not use the “sqlcmd” option when you start sql in single server mode. Apparently it is not a good advice. I don’t know the reason you got the same error even before using this option. Make sure you don’t have any default connection made to the db like the application ones. http://screencast.com/t/Jx3d9lNrD9

  15. Is there a way to automate(scriptout) the restore of sp_configure
    setting from source to migrated server or we need to do it manually one by one.

  16. No, these are configuration options which cannot be overwritten, because they are read from master system database. It is up to you to keep the similar SQL configuration after you install SQL.

  17. Irena,

    Thank you for the excellent write up. I am in the process of a migration and have a question regarding step II.18.

    I get the message back:
    “Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 56
    There are still remote logins or linked logins for the server ‘SERVER A’.”

    I looked in the login sections and can not see any local logins left. Do you have any suggestions on how I can quickly find these logins?

    Raoul

  18. Are you using replication for this server?
    You could have some remote logins configured and you should remove them as well.

    what is the output from: sp_helpremotelogin ?

    Take a look here please, and see if it is your case: https://technet.microsoft.com/en-us/library/aa197071%28v=sql.80%29.aspx

  19. Hi yrushka,

    Thanks for your post and I have a scenario like.

    We are planing to migrate our SQL2008 R2(Hosted on Windows servwr 2003) to SQl2012(WS 2012 R2) .

    Please let me know the best way to migrate and we need to migrate the Sharepoint configuration BD’s tooo.

    Regards,
    Pavan

  20. there is no answer like: best way. you need to make that decision based on what exactly you need from migrated server, allowed downtime, resources, and time. This posts covers some important aspects and please take them as a starting point in planning your migration.

  21. If SQL editions and versions (SPs and patches etc) are same on Server A (Migrating From) and Server B (Migrating To) then why does it need to replace mssqlsystemresource DB on server B? Can we manage migration without touching this DB?

  22. I am sorry, I did not read step II.11 carefully. This step actually says move mssqlsystemresource db files from Server B’s “installed location” to Server B’s new location (Not from server A to Server B). If I got it right this time, then no need to reply to my question. And, I apologize for this false alarm.

  23. Nice post Irina.
    Step 5 should be updated to something like:
    select name, physical_name from sys.master_files where database_id in (1,2,3,4) order by database_id

  24. Excellent write up, but I don’t think i can use it….all I have after a server meltdown are backups up system and user databases.
    The old server will not boot and we do not have any other dr machine to work with, so I cannot script out any of the old configuration or path information.
    SSIS packages were stored on the file system, not SSMS, and there are also a considerable number reports that we would like to preserve.
    It is SQL Server 2008 R2
    thanks very much for your advice

  25. Hi, I have got new project in my office to work on. It’s Migrate SQL Databases from test environment which has sql 2008 version to new sql instance which has Sql 2014.
    Could you help me on that?
    Like what are the direction I would have to follow.
    It would be big help from you.

    Thanks,
    Mariya

  26. Thanks Lee, I was just used to query that table. Frankly, I am still using this one although it has passed some time from SQL 2000 🙂

  27. sorry for delayed answer, I hope it is not too late though. I was on a summer vacation and didn’t check my blog comments. So, first if you have the backups from master and msdb databases if you restore them as user databases on a test machine, a lot of configuration information is found there. sys.configurations table is queried from sp_configure SP. As for serverpoperty functions, some of the information can be obtained from system objects like sysxsrvs, dm_os_loaded_modules, sp_addlinkedserver (Sp), sysservers

    also from msdb databases you can obtain a lot of information about backups,jobs,database mail profiles.

  28. Hello there,

    I’ll be doing a cloud migration on a production server. I wanted to apply some cumulative pack updates to the new build. When should I do that ? After I complete all the database restores and all the steps or prior to that ?

    I will also be using log shipping to keep the two servers in sync and test all of our applications.

  29. The migration can be done to an upper version of SQL Server but there are some constraints. You need to check backward compatibility issues and application consistency with the new version. Given this fact, my recommendation would be to restore the databases to the same version of SQL and then when it is all good and working update the engine with cumulative updates. Only pay attention on the release notes for each cumulative update, so that you are sure that some features are not disabled.

  30. Hi, I did not get why do we need to restore the system databases. Usually we only migrate the user databases and create the logins etc separately on the new instance.

  31. You don’t need to do this step if you want create separately the users and the linked servers, as well jobs, settings for db mail and everything else that you need. But if you have a lot of customized information, permission roles, credentials and you need the future server to be as close as possible to the one migrated then yes you would restore master and msdb.

  32. Hi Irina,
    I am a server administrator and have been given the task of moving a SQL 2008 database to a new machine with the same version of SQL. I have Windows Server experience but I am a SQL novice (have some brief familiarity with SSMS and SQL queries).

    You instructions are very detailed, but do you have a more detailed version or links to some reference material? For example, explaining items such as “7. Perform Full backup on master and msdb”. I realize some steps must be very obvious for you, but I am tasked with this project and could use any additional guidance possible.

  33. Hi,
    how to restore db from 2012 to 2014

  34. Hi Irina,
    Thanks for your Wonderful Post.
    I’m going to move SQL Server 2008 from Windows 2003 to Windows 2008 ,here everything is same as old except we don’t have same disk drive names like old server 😥 . So could you please help us to how to proceed to move system databases.

    Much appreciated input on this.

  35. Nice post. I have couple of questions to move my environment sql 2008r2.
    1)I have only backups of existing server.
    2)Drive names of the new server are different.
    I appreciate your response.

  36. Hi,
    i think you have done a great job explaning all of the steps. But i have a question. SO when we restore the database in to new server, the logins will be copied as well but not users permisison and roles and there is no way we could manually add them for thousand user. Can you please let me know how do we do that ???

  37. there is a way to map an user to a login. Find what users need to be fixed with: sp_change_users_login ‘report’ and after that
    sp_change_users_login ‘update_one’, username, loginame

  38. Draga Irina,

    Acest post este excelent!

    Multumesc,
    Cezar

  39. Hello, and thanks for the great post. I have performed this restore method a number of times successfully, but my most recent attempt has me scratching my head.

    I restored the master database from server A (2005 SP3) into an instance on server B (2005 SP3) with no user databases. After the restore I ran ALTER DATABASE statements to relocate the TEMPDB and MSSQLSYSTEMRESOURCE files to their new locations and restarted the SQL Server service. However, the service will not restart successfully, and the error log indicates that the instance on server B is attempting to startup my user databases from server A but can’t find their data and log files. Any idea why it is trying to startup databases that don’t yet exist on server B, and how to prevent it?

    As I said above, I have performed this restore successfully on other servers, even using the exact same master database backup from server A. I’ve never encountered this issue before and I’m not really sure what could be different. Any help would be greatly appreciated!

  40. Hi Irina,
    Thank you for this article. It has been much helpful.
    After following all the steps (I also copy-pasted model and msdb data\log files of the source server to the target server before restoring master db and kept the original paths.) However there seems to be a glitch. SQL Server Agent on the SSMS shows as disabled: “Agent XPs disabled”
    In fact, I can confirm that it is not. Agent service can be started and run successfully. Whenever the service is stopped it will show as disabled. ‘sp_configure ‘Agent XPs’ ‘ is set to 1.
    What might be causing this and how can I fix it? It doesn’t look like an important issue as it is not causing any headache on UAT but I want to make sure everything is ok before going for production.

  41. Do you think it will work to transfer my sql 2008 R2 to a new server using your instructions, do an in place upgrade to sql 2014 and then transfer the database to a new machine running sql 2014?

    I need to do a clean install on my production server and would like to keep all my sql settings and upgrade to 2014 at the same time.

  42. Hello Yrushka,

    i wish to use the same server name of Server A for Server B, is there any way to make the server offline so that I can rename Server B and install new version of SQL, and also I can remote control server A and backup it’s user databases?

  43. SQL Server’s master database contains the information about all user & system databases and at startup it is trying to start those databases on specified locations from master.databases table. If not found, those dbs will appear in suspect state inside SS Management Studio. If you restore a master database from one source to another you can’t prevent it because it is not recommended to alter master database, but you can remove the db’s from the SSMS after the startup. For a successful startup SQL need only system databases.

  44. I never came up around this issue. If you say it is enabled than this should be true, nevertheless can you run again below commands and let me know if it is the same?
    sp_configure ‘show advanced options’, 1;
    GO
    RECONFIGURE;
    GO
    sp_configure ‘Agent XPs’, 1;
    GO
    RECONFIGURE
    GO

  45. In theory yes but some system tables names have changed in 2014 version and I don’t have a 2014 version to test those steps.
    Before doing this, test all the commands that are valid on 2014 installation (at least syntax in MSDN site)

  46. Primul comentariu in limba romana, nu pot decat sa ma bucur!

  47. Hi Yruksha,
    I am murali from india i’m following your post
    plz let me know how to create triggers in sql server 2012

  48. Hello, Great post.
    I got a quick question. Can you system Databases by restore from 2008 to sql 2014? I am in middle of server migration.

    Regards

    Abdul

  49. Hello,

    I have a question, migration in above post is from which version to which version?

    Regards
    IK

  50. Great work Yrushka!

  51. It should be available from 2005 to 2005 and up.
    In this very example, the version was 2005 and was the same for the migrated databases.
    You can do an upgrade to an upper version from the “server A” to “server B” if you want.

  52. Hi Yrushka, I am Amit from India. Tomorrow i need to migrate complete SQL 2005 to new built Server. Even i need to migrate SQL jobs and alerts. I will follow your steps i need yor help. The BEST post i ever searched..!

  53. Hi Yrushka,

    This looks great for our purposes – we are moving off of our old 2003 Server with SQL 2005 installed to a VM with 2008 R2 and same SQL version (2005). We also use SQL merge replication for about 15 remote users; do you know if I need to take additional steps for this migration or will the backup and restore of the system databases take care of things for me? Thank you,
    Dave

  54. probably you will have to re-do the replication at least at subscribers side…I didn’t work so much with SQL Server replication so I can’t give you the best advice.
    afaik, merge replication means a relation between a publisher and more subscribers. and if the publisher’s name changes than the subscribers will loose the connection.

  55. OK – thank you Yrushka, We have decided to move forward with a new server and a new name. Thus I am exporting and importing various items like Maintenance Plan, etc. We will slowly move over our various databases as needed. Thanks again for your help to the SQL community through your blog!

  56. Hi yrushka,

    Do you have any experience with MS SQL Server on the AWS platform?

  57. Hi Yrushka,

    I need to move a SQL Server installation from one Windows Server to another. The target server is pratically a clone of the source server. Both of them run Windows 2008, have the same directory structure, same machine name and the same SQL Server 2008 R2. Target server SQL databases are empty, freshly installed. Can I just stop SQL servers, copy all system and user databases from the source server and paste them in the same directories of the the target server ? Is there any other step to do ?

  58. no you can’t just copy the files and paste them into the same directory tree. It is not MySQL MyISAM databases where this method would work just fine. SQL Server has a lot of metadata about databases and tables stored inside master system tables – where it creates entries for each database restored on SQL Server and used that information to start the SQL Server. The steps are described inside this post, if you don’t need to change the server names just skip that step. you have to go through the backup/restore operation any way.

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

 

Trackbacks and Pingbacks: