RESTORE FAILS

grgjn1grgjn1 Posts: 11
edited December 12, 2005 12:59PM in SQL Backup Previous Versions
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: D:\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'.

Comments

  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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
  • 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).
  • 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
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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.
  • This is just a new disaster server. Anyway to wipe the MSDB database and start over?
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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.
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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.)
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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
  • 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!
Sign In or Register to comment.