SQL 2000 fullbackup and diff backup to SQL2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

SQL 2000 fullbackup and diff backup to SQL2005

Author

Topic

vna_mhars
Starting Member

37 Posts

Posted - 2010-10-04 : 20:50:07

Hi,

I have to migrate sql2000 to sql2005 in a different machine. The scenario is, I cannot do the Detach/Attach of database because of downtime. then my option is to make a full backup from SQL2000 then restore it to SQL2005, .. Is it possible to restore the differential backup of SQL2000 to SQL2005. so once I restore the FULL Backup of SQL2k to SQL2k5, to make sure that everything is update I restore diff backup after the fullbckup..

Thanks and regards

vamodente

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-05 : 01:57:26

Yes you can restore full, diff, tlog backups from 2000 to 2005.

I'd do this:

Restore full backup with norecovery option perhaps a day in advance (depends on the size of your database)
Run a diff perhaps an hour before the upgrade, restore the diff with norecovery option
Run a tlog backup after the system has been shutdown to ensure you are grabbing everything, restore it with recovery.

Run post-upgrade scripts, such as switching the recovery model to 90 (if it's been fully tested).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

vna_mhars
Starting Member

37 Posts

Posted - 2010-10-05 : 03:03:30

Thanks Tara, just a clarification..

1. From the full backup of SQL2000, I will restore it to the new machine using SQL2005 server.

2. get the differential backup of SQL2000 then restore it to the SQL2005

3. get the Transaction log backup of SQL2000 and then restore to the SQL2005

is these procedures to follow?

vamodente

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-05 : 08:48:21

quote:
Originally posted by vna_mhars

Thanks Tara, just a clarification..

1. From the full backup of SQL2000, I will restore it to the new machine using SQL2005 server. WITH NORECOVERY

2. get the differential backup of SQL2000 then restore it to the SQL2005 WITH NORECOVERY

3. get the Transaction log backup of SQL2000 and then restore to the SQL2005 WITH RECOVERY

is these procedures to follow?

vamodente

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources