RESTORE FAILS

grgjn1

Not sure why everytime I try to restore the backup to a different machine it fails.




12/10/2005 8:30:47 AM: Restoring file: \Database Backups\FULL_(local)_ImageRight_20051209 200000.sqb
12/10/2005 8:30:47 AM: Restore started ...

---

SQL Server messages

---

Processed 3099488 pages for database 'ImageRight', file 'ImageRight_Data' on file 1.
Processed 34 pages for database 'ImageRight', file 'ImageRight_Log' on file 1.
Msg 823, Level 24, State 2, Server DISASTER, Line 7
I/O error 38(Reached the end of the file.) detected during read at offset 0x00000001736000 in file 'd:\Program
Files\Microsoft SQL Server\MSSQL\data\msdbdata.mdf'.

petey

Is it possible that you've got a bad page on your drive? Try restoring to another machine/drive, or try making a native backup/restore.

grgjn1

not sure what u mean by bad page, but we cant restore any backup made by redgate via enterprise manager backup script jobs. we get the same error. please help

grgjn1

backup restore works just fine using the enterprise manager maintenance job WITHOUT Red-gate script.

and the servers restoring too are brand new dell drives....this is not good, as we've been using red-gate backup for months (full backup nightly, and 10 minute backup logs daily).

grgjn1

why are the errors showing

Microsoft SQL Server\MSSQL\data\msdbdata.mdf'

when we are restoring to a database called

imageright.mdf, not msdbdata.mdf

petey

Good point, I didn't notice the file name.

Could you pls run a DBCC CHECKDB on the msdb database? After every backup/restore, SQL Server writes data to certain msdb tables to store details on the process.

grgjn1

Peter,

I am not very good with SQL or SQL commands. Can you please be PRECISE and let me know what to do step by step. Do you work for RED-GATE? If so, please email me the exact directions. Can you email me and I'll let you know our # so you can call me direct. Why is this happening?

Thanks,

George

petey

I think that parts of your msdb database may be corrupt. Running DBCC CHECKDB will verify the integrity of the database i.e. from Query Analyzer, run the following command

DBCC CHECKDB ('msdb')

If SQL Server reports errors, you can then run DBCC CHECKDB with options that can attempt to repair the errors. Look in SQL Books Online for the various options.

Yes, I do work for Red Gate, on a contractual basis.

grgjn1

Server: Msg 7995, Level 16, State 1, Line 1
Database 'msdb' consistency errors in sysobjects, sysindexes, syscolumns, or systypes prevent further CHECKDB processing.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

grgjn1

This is just a new disaster server. Anyway to wipe the MSDB database and start over?

petey

I've never had to deal with a damaged msdb database before. I suppose your options are:

- run DBCC CHECKDB with one of the repair options
- restore the msdb database from a prior backup (if not recent, might want to script out the SQL Server Agent jobs first, though I don't know how to handle DTC and replication items)
- reinstall SQL Server

Running some disk utilities to check that your drives are running reliably would also be reassuring.

grgjn1

I never restored the MASTER and MSDB database from the real server to this one, i guess i need to, right? Do i need to restore any of the others (temp, pub, etc.) when moving the actual database to a new server? I did the MSDB sucessfully, but the master errors out. Don't I need to do that one too (btw, i'm using red-gate to this too). I'm running the restore currently on the big one now to see if restoring the MSDB from the main server to the new one fixed the issue.

petey

You should not restore the master database using a backup of a master database made from another server. You need not restore the master database in this situation anyhow, unless DBCC CHECKDB also reported errors with this database. If that is the case, and you do not have a backup, you can rebuild the master database (see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_4g4w.aspand the topics below it).

Do you mean that you still encounter the consistency error even when restoring the msdb database from the production server? If so, you should check the consistency of the msdb database on that server.

grgjn1

1) MSDB restored OK
2) the main database now WORKS great during the restore!
3) MASTER FAILS (info below)

RESTORE failed. VDI error 1010:failed to get configuration from server. check that the sql server instance is running, and that you have the SQL server systems administrator server role. Error Code: -2139684861 (The api was waiting and the timeout interval had elapsed.)

petey

You cannot restore the master database using the SQL Backup GUI, because the SQL Server needs to be in single user mode. You can use the command line interface, but there really is no need for you to restore the master database at this point.

grgjn1

So help me here. I'm a SQL dummy. I read that when you MOVE a SQL database you need to move the MASTER and MSDB database along with it? It that true? Any other databases?

If so, then I only moved the MSDB, what about the MASTER? If I do need to move the master, should I just use QUERY ANALYZER, go into SINGLE USER mode, then use RED-GATE to restore?

Thanks

George

Brian Donahue

Hi George,

Hold on there a second -- moving the master database will essentially copy the whole server configuration from one server to another. You probably don't want to do that unless you intend on restoring all databases from one server to another. If you restore master to another server, all of the databases from the original server will appear in Enterprise Manager (as suspect/offline). There may also be security implications, too, due to SIDs being stored in your security configuration.

But your're correct, to restore MASTER, you need to start the server using the -m parameter to put it in single user mode. You may also need to be aware that using SQL Backup, you can only restore the master database using the command-line (sqlbackupc.exe) and not the UI, because the User Interface requires two connections to the server!

Hopefully this helps!