Re: Move Existing SQL Server to a New Server
From: Sue Hoegemeier (Sue_H_at_nomail.please)
Date: 09/14/04
- Next message: SQL DBA Newbie: "dbo user performance"
- Previous message: Keith Kratochvil: "Re: Help!.................................."
- In reply to: MarkS: "Re: Move Existing SQL Server to a New Server"
- Next in thread: Mark: "Re: Move Existing SQL Server to a New Server"
- Reply: Mark: "Re: Move Existing SQL Server to a New Server"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 14 Sep 2004 13:14:10 -0600
You can avoid most of the errors you reference by following
the articles. It looks like you could be missing some of the
trace flags when restarting the services between restores or
by not following the sequence outlined in whichever article
you are following. .
Restore isn't more error prone - it's just that you really
need to understand all of the restore arguments, how to use
them, when to use them.
If you are more comfortable with detach and attach, the
steps are pretty well outlined in this article:
http://support.microsoft.com/?id=224071
but I generally follow the backup and restore approach as
outlined in
http://support.microsoft.com/?id=304692
If you combine steps from the different articles, you may
end up going the wrong direction unless you understand all
the implications of the trace flags, restoring system
databases, etc. And I'm not at all clear on which steps you
are using for what. You can actually do the whole thing in
different orders but you really need to understand the
process, the system databases, etc. Otherwise, it's probably
best to just following the steps in one of the articles.
In terms of the user databases you don't want to restore,
one option is to detach those databases, backup the master
database for the move, then reattach the other databases.
You can also just drop the user databases you don't plan on
restoring after the master database is restored.
Whether you use restore or attach, the databases will have
the same elements of data stored in master and the same ones
stored in the user databases. It's just SQL Server's
architecture and that doesn't change whether databases are
moved with detach/attach or backup/restore.
You really just need to study the articles and get a good
handle on the process and how it works. I understand having
a lean shop but you could always download the evaluation
version to your PC and practice restoring the system
databases and sample databases on your PC. I've seen that
type of approach in companies that don't have spare
hardware. It may not be the real databases but it can get
you more comfortable with the process and issues that can
arise. What if you have to do disaster recovery at some
point? What if the server totally goes out and all you have
left is the SQL Backups from tape? When the new server comes
in, someone will need to know what to do with those backups
on tape to get the databases back up and running.
-Sue
.
On Tue, 14 Sep 2004 11:01:49 -0700, "MarkS"
<marks@mezzetta.com> wrote:
>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.
>>
>
- Next message: SQL DBA Newbie: "dbo user performance"
- Previous message: Keith Kratochvil: "Re: Help!.................................."
- In reply to: MarkS: "Re: Move Existing SQL Server to a New Server"
- Next in thread: Mark: "Re: Move Existing SQL Server to a New Server"
- Reply: Mark: "Re: Move Existing SQL Server to a New Server"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
