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.
Best Answer
This will work
Some gotchas:
@@SERVERNAMEwill be wrong. Fix as per this MSDN sp_addserver examplemsdb) 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