Restore 2000 DB on 2005 DB with standby

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 2000 Forums

SQL Server Administration (2000)

Restore 2000 DB on 2005 DB with standby

Author

Topic

dbaadm
Starting Member

10 Posts

Posted - 2006-05-18 : 06:10:24

Will trying to restore a MS-SQL 2000 database on a MS-SQL 2005 server with standby file, I get the following error

Msg 3180, Level 16, State 1, Line 1
This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Here are the script that I use

RESTORE DATABASE [TestDB]
FROM DISK = N'T:\TestDB_Full.BAK'
WITH FILE = 1,
MOVE 'TestDB' TO 'E:\MSSQL_Data\TestDB.mdf',
MOVE 'TestDB_log' TO 'D:\MSSQL_Logs\TestDB_log.ldf',
REPLACE,
STANDBY = N'T:\bak\ROLLBACK_UNDO_TestDB.BAK',
NOUNLOAD,
STATS = 10
GO

Could someone advise on this issue?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-18 : 06:38:10

Did you read
This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.

It's saying it can't be done on a 2000 database restore to 2005.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2006-05-18 : 07:42:14

If you need to do this I reckon you are going to have to Restore to a temporary database on a 2005 server, backup (from that 2005 database) and THEN restore to the actual database on the target 2005 server - using WITH STANDBY

If you are trying to log-ship from 2000 to 2005 this will rapidly become a PITA!

Kristen

dbaadm
Starting Member

10 Posts

Posted - 2006-05-18 : 08:04:51

I did read the error message.
But what puzzled me was the fact that -
if the db has compalibiliy level 8 and is backup on a SQL Instance 2005, it works without any problem.
While a backup dump from a SQL Instance 2000 doesn't work.

I could have understand the issue if I was trying to restore a db with a different compalibiliy level.

Kristen
Test

22859 Posts

Posted - 2006-05-18 : 08:28:28

The backup from SQL Server 2000 has to be restored and then "upgraded" to the version on the target server (some additional information to that effect is output when you do the RESTORE).

So you can't put the database into Standby because it won't then be able to do the upgrade - presumably because that itself will create transactions.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources