Tuesday, 13 August 2013

Moving SQL Server database onto another machine

I have had a pleasure of moving databases onto different machine. I decided it will be good to write down quick step by step guide. This is just simple migration between development machines - so no complex stuff!

Requirements

  • SQL Server (I used 2008 - it also works for 2012)
  • SQL Management Studio (2008 - 2012 will also work)

Steps


  1. Grab a notepad and write down all users & passwords you will require on the other machine. (Otherwise systems that depend on those database could fail!)
  2. Right click every Database and select "Tasks->Backup"
    1. Point .bak file to some directory that you can later on move to new machine.
    2. As a rule of thumb (sanity check) always go to Options and tick
      1. Verify backup when finished
      2. Perform checksum before
        writing to media.
  3. Move folder with all .bak files to new machine.
  4. Open SQL Management Studio on new database server.
  5. Create all users noted in step 1.
  6. Right click Databases 
    1. Select Tasks -> Restore
    2. Point it to your .bak file.
    3. Press Restore.
    4. Repeat the process to restore all databases.
  7. Due to how SQL managers users, even though users do match (name wise), it will still not associate them with database.
    1. User that associated with database yet doesn't exist is called "orphaned user". (FYI if you need to do more googling)
    2. We can fix it by opening new Query
    3. Select Database that you want to run this query on 
      1. EXEC sp_change_users_login 'Auto_Fix', 'UserName'
      2. Replace UserName with a user that you want to re associate with database. 
      3. Note: This will not create new user, which is why you had to create all your users above. All it does is looks for new user and orphaned user and matches their names and if match is found it will associate new user and remove orphan. 
  8. Run few tests to make sure that 
    1. Database are running in recovery mode of choice (Simple or Full)
    2. See if there are any maintenance plans that need to be moved across (not part of this guide).
    3. Check if users can log in and query their database just like they could on old one.
      1. Note: On new install of database it will not allow TCP connection, you can run SQL Server Configuration Manager to enable TCP for specific database instances.  
    4. Check if backup is necessary.
Good luck!