2

I need to migrate an instance of SQL Server 2008 R2 (20+ databases) to a new server this weekend. The new server is exactly the same as the old server in all respects, including SQL Server version numbers. The only difference is that the new server has a lot more CPU power, RAM and harddrive space.

I've already successfully performed a test migration using my automated backups, but I only restored the user databases. This meant that I had to do a lot of work afterwords to recreate logins, jobs, maintenance plans, settings etc. I have all of this scripted and backed up, but its a lot of extra work that I'd like to skip in order to save time during the actual live migration. I only have a couple of hours to get it done. I need more time for testing and configuring the systems using the databases.

One of the guys here pointed out that as all the SQL Server version numbers are the same, I could just shutdown both instances of SQL Server and then copy all the *.mdf (data) and *.ldf (log) files to the new server, including those for the system databases, and then just start SQL Server again.

My questions:
- Is this wise / recommended? - Will it work? - Any foreseeable problems?

I'm going to go do a test of this theory now, but I'd like to know what you guys think.

Improve this question

1 Answer 1

Reset to default
3

This will work

Some gotchas:

  • @@SERVERNAME will be wrong. Fix as per this MSDN sp_addserver example
  • SQL Agent jobs (in msdb) may be "owned" by the old server. You'll have to script and recreate. However, this doesn't apply to SQL Server 2012 from what I can see: it used to for SQL Server 2000 which is the last time I did it....

Note that using DNS to "hide" the physical server is a useful trick. See these

Improve this answer
3
  • I confirmed that the server names are the same (@@server and physical server). The new servers are on a completely different network, actually different country. So having the same name is not a problem. I already script the sql jobs as part of my daily backups, so I'll restore them from there. Thank you for warning me about those.
    – Daniel Brink
    Commented Jan 11, 2013 at 11:14
  • @DanielBrink: in this case, you're looking good then to simply copy the files.
    – gbn
    Commented Jan 11, 2013 at 11:16
  • All you need to do is update the msdb.dbo.sysjobs table and change the server name to match the new server name.
    – mrdenny
    Commented Jan 12, 2013 at 5:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.