RE: SQL 2000 restore to new server

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: John Bell (JohnBell_at_discussions.microsoft.com)
Date: 02/08/05 

Date: Tue, 8 Feb 2005 01:03:04 -0800

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.

Relevant Pages