Re: SQL 2000 restore to new server
From: John Bell (jbellnewsposts_at_hotmail.com)
Date: 02/08/05
- Next message: Paul S Randal [MS]: "Re: corrupt system catalog"
- Previous message: Allan Mitchell: "Re: DTS Programming Question -- how to remove custom transformations"
- In reply to: Monty: "Re: SQL 2000 restore to new server"
- Messages sorted by: [ date ] [ thread ]
Date: 8 Feb 2005 10:52:47 -0800
Hi
As you are trying to re-create the system (how it was) then you should
restore the system databases as described in
http://support.microsoft.com/kb/304692/EN-US/ If your machine has
failed totally then your overnight backups may be the most recent files
available to restore from. To reduce the amount of lost information you
may want to backup the logs periodically and store that offline as
well. How to the restore logs is described in Books online.
Whatever the strategy you finalise on, you should document it and
periodically test it.
HTH
John
Monty wrote:
> Thanks John,
> I am not sure I totally understand all the condition you outline (my
lack of
> knowledge not your description) but let me see if I can answer...
> Objects placed in those DB's would have been placed there by
requirements of
> the System and not manually by us in most cases. There might be some
user
> info in there I am not sure but we would need to restore them as well
if I
> am not mistaken.
> All databases, user and system (except for tempdb) are backed up
daily at
> two different times of day. Northwind has been deleted.
> What we are doing is trying to set up a strategy that would allow
complete
> recovery with as little fuss as possible assuming complete failure of
> hardware/software. The machine that the DB's will be restored to will
be an
> exact duplicate hardware wise of the "lost" machine but with a fresh
> installation of W2K3 and SQL2000Sp3a as identical to the "lost"
machine as
> possible but within the limitations of the scenario (maybe a new NIC
with a
> different MAC, etc.).
> No users would be allowed on until the server was fully functional in
any
> situation I can think of but we would need to be function as quickly
as
> possible.
> I can have the backup be the usual with the .BAK files or of the .ldf
and
> .mdf files or both (we are formulating the strategy here and will do
what's
> required).
> I agree in the script solution....hence my included version in the
first
> post. I am just unsure if that is correct and what needs to differ to
move
> system DB's.
> I hope this sheds more light on my situation and I look forward to
input.
> "John Bell" <JohnBell@discussions.microsoft.com> wrote in message
> news:37B5EEF8-FB25-4F9C-86C9-38B759FEF3A1@microsoft.com...
> > Hi
> >
> > You requirements will depend on what exactly your applications such
as if
> > you have placed objects in the master or model, or if you want to
script
> > things like jobs or restore them. How well you control your
production
> > envirnonment and how often you backup the system database may also
be a
> > factor, how close the hardware is on both systems. It may also be
that you
> > can allow users on before all the work is completed, although there
may be
> > risk to using this kind of strategy.
> >
> > For a controlled installation (i.e. where you can make up-to-date
backups
> > etc.) my preference would probably be to use backup/restore:
> > http://support.microsoft.com/kb/304692/EN-US/
> >
> > But I would consider using Detach/Attach if I change the hardware
or was
> > moving the locations once the system was running.
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
> >
> > This process should be documented as part of your disaster recovery
> > strategy. Rather than using the wizard I would favour writing
scripts as
> > this
> > would be easier to document and to get someone (remotely) to run
(although
> > you would need to make sure they are kept up-to-date).
> >
> > If you are releasing from a development environment to production,
then
> > you
> > should use a more controlled approach which would not normally
include
> > database restoration.
> >
> > John
> >
> > "Monty" wrote:
> >
> >> Noob here...
> >> I need to restore a complete backup set of user and System
databases to a
> >> new install of SQL2000sp3 on Server2K3 from a non-running (for
testing)
> >> install of same. I need to know the simplest way of doing this on
a
> >> somewhat
> >> regular basis, for example, if my boss wants to test how long it
would
> >> take
> >> to get a new server going from scratchto production..etc. I am
getting
> >> conflicting (to me anyway) information...it seems some say
> >> detach...attach
> >> the .mdf and ldf (which I can do although at the moment I only
have the
> >> BAK
> >> files)..other say back/restore. If restore is the best (most
simple and
> >> quickest) way....does the wizard have all the functionality I
need? We
> >> tried
> >> it earlier (unfortunately not a test) and had issues with
locations and
> >> users
> >> but got it to work.
> >> I will also be moving all databases from a C: on the original
server to
> >> D:
> >> on the new.
> >> I have formulated a possible TSQL query for the user DB:
> >> USE MASTER
> >> GO
> >> RESTORE FILELIST ONLY
> >> FROM MMS_CSS_DATA
> >> DISK =
'E:\MSSQL\BACKUP\MMS_CSS_DATA\MMS_CSS_DATA_db_200502011730.BAK'
> >> RESTORE DATABASE MMS_CSS_DATA
> >> FROM MMS_CSS_DATA
> >> DISK =
'E:\MSSQL\BACKUP\MMS_CSS_DATA\MMS_CSS_DATA_db_200502011730.BAK'
> >> WITH MOVE 'INVENTORYSQL_DAT' TO
'D:\MMS_CSS_SQL_DATA\MMS_CSS_DATA.MDF'
> >> MOVE 'INVENTORYSQL_LOG' TO
> >> 'D:\MMS_CSS_SQL_DATA\MMS_CSS_DATA_LOG.MDF'
> >>
> >> Can someone tell me if this is the right path I should continue to
> >> explore
> >> and what, if any differences would need to be entered for the
system
> >> DB's?
> >> I know there are a lot of questions here...any help would be
appreciated
> >> though.
- Next message: Paul S Randal [MS]: "Re: corrupt system catalog"
- Previous message: Allan Mitchell: "Re: DTS Programming Question -- how to remove custom transformations"
- In reply to: Monty: "Re: SQL 2000 restore to new server"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
