Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm planning to set up a new Win 2012 server, with SQL server 2014 standard. This server is going to replace our current server, Win 2008R2, with SQL server 2008R2. The name and ip is going to be the same. I'm wondering which approach is the best?

We are planning downtime to do this.

ServerA = current db-server

ServerB = the new db-server

Method 1

  1. ServerA: Use the sp_help_revlogin from Microsoft (http://support.microsoft.com/kb/918992) to generate the script to transfer logins.
  2. ServerA: Make a full backup of the user databases
  3. ServerA: Copy both the logins transfer script and the db-backups to ServerB
  4. ServerA: Shut down the server
  5. ServerB: Change the name of ServerB to ServerA, and change the ip to match the one ServerA had (this will not be done by me, but it will make ServerB "identical" to ServerA with name, ip and so on).
  6. ServerB: Install MS Sql 2014
  7. ServerB: Run the logins script to generate the logins
  8. ServerB: Run restore on the user databases

I'm a bit unsure if nr 7 and 8 should change places?

Method 2

This method is identical to Method 1, the only difference is instead of using backup/restore, to use detach - copy db - attach on ServerB.

EDIT

Just wanted to say that i went with method 1. The only thing i changed was that I switched places with nr 7 and 8. So 1, 2, 3...8 and 7.

share|improve this question
2  
Small addendum that you might want to keep in mind (pitfal I fell in before). If you use the sp_help_revlogin, and the target server has enforce password policy on, old sql server users with passwords that don't conform will not work. –  Reaces Jan 14 at 8:43
    
Will you upgrade compatibility level of database? –  user_0 Jan 14 at 9:40
    
No, the databases will stay at the same compability level. –  Svein Erik Jan 14 at 13:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.