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.
| Author |
Topic |
|
Warwick
Starting Member
7 Posts |
 Posted - 2007-06-03 : 20:58:15
|
| With Migrating from SQL 2000 to SQL 2005 I realise that there are a few options available.A SQL 2000 backup is able to be restored into 2005 and is workable however there are some issues with internals that have changed. I have identified some DDL which would need to be modified to work correctly.If the Backup is restored and left in a recoverable state does 2005 allow for 2000 TLog backups to be restored as well ? I am assuming that as you can restore a full db backup that this is allowed.I am asking the question as i have a very short and fixed deadline of moving from 2000 to 2005. A new datacentre is being moved to and the old DC is being turned off. all new hardware is being placed in the new DC so i need to move from the old to the new with very little downtime to the DB or app.My approach is as follows1. Full DB Backup (2000) copied across to 2005 server and loaded being left in a recoverable state.2. Tlog backups copied across on scheduled basis and loaded to a point in time.3. Test new 2005 DB with app/web frontend4. Bring both in sync again and then perform 1 final Tlog backup, copy and load bringing app online again with very little downtime.Has anybody tried this approach ? does anybody see any issues with my approach ? all comments welcome on this.Thx |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-03 : 22:59:09
|
| No, should leave the db in norecovery or standby mode. Same as in sql2k. |
 |
|
|
Warwick
Starting Member
7 Posts |
Posted - 2007-06-03 : 23:44:45
|
| Sorry my typo. I would be leaving the DB in standby to allow for the DB to be in sync with the 2000 DB. Bring it online and start testing.Then once happy Go back through the process again to get it in sync so as to minimise the amount of downtime required. Once being brought online again prior to making it available apply the DDL changes required to ensure it complies with the new architecture.But this is a completly valid and viable approach to the migration with the minimal downtime ?THx |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-04 : 16:41:18
|
| Should be since apply last log is faster than restore from full backup. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-06-04 : 17:11:33
|
| I don't think you will be able to use SQL 2000 transaction log backups to bring the database to STANDBY mode in SQL 2005. I believe you will only be able to do RECOVERY or NORECOVERY.However, I haven't tried it, so you should try testing it.CODO ERGO SUM |
|
|
|
|
|
|
SQL 2000 TLog restores in 2005