Cant restore a database from another server

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

SQL Server Administration (2008)

Cant restore a database from another server

Author

Topic

lmayer4
Starting Member

33 Posts

Posted - 2013-11-26 : 13:01:48

Hi there,

I have a test server v2008 I am trying to restore a backup from a sql 2000 box. The database was backed up and the backup was moved to the new server. I used the command RESTORE FILELISTONLY FROM CostguardBU
and got good results. the database is pretty big like 10gb.

Everytime I try to restore it it never gets past 0% and eventually fails. When I looked in the log I see the message :

During upgrade, database raised exception 5242, severity 25, state 3, address 02921D43. Use the exception number to determine the cause.
Error: 928, Severity: 20, State: 1.

Is there a trick to this? I thought I could just restore it and it would upgrade the database. It would appear not.

Any thoughts would be great.

Thanks

Laura

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-26 : 13:30:08

You should be able to restore from 2000 to 2008, but I suspect the backup or the database on 2000 is corrupted. Run DBCC CHECKDB on the 2000 box and additionally try to restore it to a 2000 box (if same box, use a different name).

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

lmayer4
Starting Member

33 Posts

Posted - 2013-11-26 : 13:38:13

Thanks Tara, I watched it this time and it seemed to restore it got to 100% but then said could not start the database. I ran the following steps:



use master
 select databaseproperty('costguard','isShutdown')
 
 use master
 alter database costguard set offline
 
  
 use master
 alter database costguard set online

It tries to bring it back online and gives me this message:


Converting database 'costguard' from version 539 to the current version 655.
Database 'costguard' running the upgrade step from version 539 to version 551.
Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed.

Is there anything that can be done to this?

Thanks

Laura

mdn143
Starting Member

4 Posts

Posted - 2013-11-27 : 06:25:45

Try the following steps. It may work in your case.

ALTER DATABASE DB_name SET emergency
GO

ALTER DATABASE DB_name SET single_user
GO

DBCC checkdb (DB_name, repair_allow_data_loss)

ALTER DATABASE DB_name SET multi_user
GO

Regards,
Dhamu

lmayer4
Starting Member

33 Posts

Posted - 2013-11-27 : 10:50:49

Thanks for your thoughts Dhamu but that didnt work either.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-27 : 13:03:38

Did you run DBCC CHECKDB on the source 2000 database? Did you try restoring is to a different 2000 server or same server using different name?

I am pretty sure you've either got a corrupt backup file or a corrupt source database.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

lmayer4
Starting Member

33 Posts

Posted - 2013-11-27 : 13:40:56

The source database is fine. I will try making another backup and moving it ober. Thanks for all your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-27 : 13:52:17

quote:
Originally posted by lmayer4

The source database is fine.

You verified that with DBCC CHECKDB? Or are you assuming it's fine because it is not throwing errors when accessing the data?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2013-12-03 : 10:33:00

quote:
Originally posted by lmayer4

I will try making another backup and moving it over.

Are you using FTP to move the backup to the other server? That might be corrupting the file (e.g. if you are not using Binary FTP)

Personally I would ZIP the Backup file on source server (if the file is big then use the least compression, it will compress more quickly), copy the ZIP file to destination server, and then UNZIP. The UNZIP process will give you an error if the file became corrupted during the move to the destination server (because ZIP automatically includes a CRC Checksum of the file)

Kristen
Test

22859 Posts

Posted - 2013-12-03 : 10:33:29

Sorry, just seen your post was a week or so ago - you've probably already solved the problem

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources