October 13, 2011 at 7:07 pm
Hello all,
We run a SQL 2005 box on Windows 2003 x64. The hardware is aging and we will replace it with a Windows 2008 R2 box.
Does anyone see any issue with re-locating the db from W2k3 to W2k8R2?
Note we will use BACKUP on the source machine and simply RESTORE on the new machine. SQL 2005 will be used on the new machine as well.
Thanks for any info!
Speedy
October 14, 2011 at 4:55 am
Sounds good
Make sure that you have the same drive structure on the new machine as the old one.
October 14, 2011 at 5:10 am
you'll want to export your logins from the old box to the new one. the procedure sp_help_revlogin from Microsoft will do that for you .
run it on the old server, and run the results on the new server to migrate your existing logins.
you'll also want to script any linked servers , and if the linked servers were local(text folder, access datbase), you want to move those files as well.
Lowell
October 14, 2011 at 5:12 am
Backup and Restore your system DB's as well
October 14, 2011 at 9:55 am
All fantastic replies, thanks very much!
One note kind of irks me though...the one about maintaining the same drive structure
Currently, the SQL server has all its files stored in named junctions. So, there are several physical raid arrays to store the various pieces of the db (mdf, ldf, ndf, etc.) and rather than being stored in d:, e:, f: etc. they are all kept under a single folder (C:\volumes) that contains junctions to the various arrays. Like this:
c:\volumes\dbstorage1
c:\columes\dbstorage2
c:\volumes\db2storage1
...and so on. To Windows, those junctions can be separate physical drives or just folders, it doesn't care.
On our new server, we plan to setup arrays as drive letters c, d, e and were planning to put, for example, OS on C:, mdf on D:, ldf on E: and so on.
Is is not possible to re-configure the db to store the various pieces in different locations?
Thanks!
October 14, 2011 at 10:17 am
the structure does not have to match. that's a "nice to have" thing, but not a requirement.
when you restore, you'll use the WITH MOVE to tell the restore the place where the files*mdf/ndf/ldf) will be placed.
when you try to restore a database, and the original path was on , say the D:\somefolder, you'll get an error if it doesn't exist, and you jsut have to change the path, and maybe the name if you want, of the MDF files.
you'll end up doing that part anyway, since it sounds like you'll put data on D: and logs on E:, so you'd do that step automatically, anyway.
no big deal.
Lowell
October 14, 2011 at 10:28 am
That's fantastic! Thanks very much!
October 14, 2011 at 10:57 pm
derekr 43208 (10/14/2011)
Make sure that you have the same drive structure on the new machine as the old one.
There's no need whatsoever to do that. There is nothing about moving databases (backup/restore or detach/attach) that requires the drive structure to be the same or even similar.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply