Moving a db to different Windows versions

  • 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

  • Sounds good

    Make sure that you have the same drive structure on the new machine as the old one.

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Backup and Restore your system DB's as well

  • 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!

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • That's fantastic! Thanks very much!

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply