Re: Move Existing SQL Server to a New Server

From: MarkS (marks_at_mezzetta.com)
Date: 09/14/04 

Date: Tue, 14 Sep 2004 11:01:49 -0700

Thanks, Sue. I will respond in line.

> Mark,
> All of what you are attempting to do is doable.
> To move the master, you need to use the start up parameters
> as outlined in the 224071 article at:
> http://support.microsoft.com/?id=224071
> You can find the gist of what you need in that article as
> well as:
> http://support.microsoft.com/?id=314546
> http://support.microsoft.com/?id=304692

I referenced all the KB articles to which you referred.

>
> 2. Restore master, then model, then msdb then the user
> databases.

It seemed that the master database wanted to see all the user
databases (including old drive paths of user databases) during start up
of the sql service. I received the following types of errors:
"FCB::Open failed: Could not open device e:\MSSQL7\DATA\
pubs.mdf for virtual device number (VDN) 1."
or
"FCB::Open failed: Could not open device e:\MSSQL7\DATA\
DATA_25_DATA.mdf for virtual device number (VDN) 1."
or
"Could not find database ID 3. Database may not be activated y
et or may be in transition."

I do not want to move all user databases, just some. I also see
no point in moving pubs or northwind.

Restore seemed more error prone than the Detach/Attach method.
Do I have to do all the attachments, then attempt to start the service?
 How do I avoid the above errors?

> 3. You can move the data and log files in the process of
> restoring or attaching. With the master database, you need
> to use the startup parameters. I'm not sure of what you mean
> by moving all mdfs and ldfs in "one move"

The master database seems to refer to user databases by their
old path (see errors above). My "all in one move" comment
was about whether I had to exactly mimic the old file structure,
then move files on the server, then change the file structure.

> 4. The master database does not need to be on the same
> partition as the SQL Server binaries.

Good

> 5. If you are restoring the original master database, it
> will have what it needs when you restore model, msdb and the
> user databases. The data elements stored in the master
> database would be restored. The other information is stored
> in the individual databases.

Does the same follow in the Detach/Attach method?

> Mostly, you'd want to really study the above articles and
> practice some of the moves, restores somewhere - even if
> it's just to your own PC. The articles and steps may not
> make much sense if you haven't really practiced the act of
> moving or restoring the databases - especially related to
> system databases.

We run a lean operation here, and I do not have any spare server
equipment or OSs. I do not even have disk space to transfer the system
databases and all user databases at one time. I am purposely leaving two
user databases, pubs, and northwind behind. Practicing has to be done
 off hours as well.

> You may want to try just restoring the master somewhere.
> When you run into problems, make not of the exact error
> message and error number. Then post a message on one of the
> newsgroups. It's hard to tell what steps you may have missed
> or what went wrong without the error messages.

I hope the additional information above is specific enough.

>
> -Sue

Kind Regards,

Mark

>
> On Mon, 13 Sep 2004 12:05:00 -0700, "MarkS"
> <marks@mezzetta.com> wrote:
>
> >I am currently running a MS SQL server 2000 on a RAID 5 (3 drives) with a
> >simple volume in the OS (Win2K member server) and a C and E partition.
> >Everything SQL related is on the E partition.
> >
> >I plan to move it to a Win2K3 server with SQL 2000 SP3 which has a RAID 1
OS
> >and SQL application, another RAID 1 for the logs, and a RAID 0/1 for the
> >data. I have assigned C and E partitions to the OS disks with the SQL
app
> >on E. I have assigned the L to the logs disks, and M to the data disks.
> >
> >I attempted to RESTORE (with move) the master database first, because I
> >could not find any specific guidence on how to proceed. Each time I ran
> >into a problem, I found a KB article ( see below) that seemed on point,
but
> >failed somehow. I changed tacks and attempted a detach/attach strategy,
but
> >that failed as well.
> >
> >I dealt with multiple issues over an 11 hour period (a lot of file
transfer
> >time and restoring back to original configuration). This is a production
> >only environment, so I have to work after hours.
> >
> >Now I went back to square one.
> >
> >1) What cites are there for best practices on such a move?
> >
> >2) In what sequence should I transfer databases? (user, master, model,
> >msdb)
> >
> >3) Can I go from having all mdf and ldf files on one partition E to
> >multiple partitions (M and L) in one move?
> >
> >4) Does the master db need to remain on the same partition as the SQL
app,
> >or can it remain with the other mdf files on M?
> >
> >5) Would a startup master database acquire all the necessary meta files
by
> >restoring or attaching the user, msdb, and model databases? What would I
> >lose?
> >
> >Here are the KB articles that I have already worked with: 224071, 304692,
> >221465, and 314546.
> >
> >Thanks in advance.
>

Relevant Pages

  • Re: Please answer my queries for fresh Installation
    ... You can restore MSDB as well as master. ... > SQL Server, It will create Master, MSDB databases. ...
    (microsoft.public.sqlserver.server)
  • Re: Move databases between servers
    ... What I'm trying to do now is to copy master. ... Now I'm reinstalling sql with identical version, and restore all databases ... Since the logins are in master and operators/agents are in msdb why I ... copy all database and log files to new server ...
    (microsoft.public.sqlserver.setup)
  • Re: Rebuilt master, whats next?
    ... one thing remains unclear to me: what will happen when I restore ... master then start the server and user databases aren't where they ... Will the references to them in master be automatically ...
    (microsoft.public.sqlserver.server)
  • Re: Move databases between servers
    ... Tell me why you need to move master, msdb, and model? ... When you restore databases on your other server SQL Server adds ... If you have lots of jobs defined you could try restoring MSDB. ...
    (microsoft.public.sqlserver.setup)
  • Re: Move Existing SQL Server to a New Server
    ... To move the master, you need to use the start up parameters ... If you are restoring the original master database, ... user databases. ... >simple volume in the OS and a C and E partition. ...
    (microsoft.public.sqlserver.setup)