Restore Issue?

thomascroththomascroth Posts: 17
edited May 1, 2008 10:00AM in SQL Backup Previous Versions
I've been trying to restore one of our production databases. The size of the database is approximately 260GB. The size of the backup file is 22.4GB. The restore process has been running for more than 48 hours now. A normal backup of this database taken approximately 3 hours to a network drive.

Is there a way for me to check the status of the restore? I can see the mdf file. The ldf file doesn't appear yet. The database doesn't appear in Enterprise Manager.

Background information: SQL Server 2000 SP4, Backup file created using SQL Backup 5.1.0.2781. Since the backup was taken, SQL Backup was upgraded to 5.3.0.178. The size of the database is so large, that I have to restore it to a network drive. I'm actually creating a new database from the backup so we can run some comparisons between the current database and the database restored from the backup.

I ran the SQL Backup Check File Integrity Utility and received the following response:
- Encrypted
- Encryption mode: enc256v4
- Single device file
- Compression level: 3
- File size: 24,111,174,656
- File read stopped at position: 24,111,174,584
- Remaining: 72
- Validating remaining bytes...
- File appears to be valid
- File read ended at position: 24,111,174,656

Any help would be great.
Tom
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    Could you please run the following:

    EXEC master..sqbutility 9997

    which will then generate a file named SQBCoreService_bugreport.txt in the folder where the SQL Backup Agent (SQBCoreService.exe) is installed. Please send me that file.

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Thanks for sending the file.

    The log doesn't show that any active restore process running within SQL Backup. However, it also shows that the service has only been running for 33 minutes.

    Was the service restarted while the restore was running?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • thomascroththomascroth Posts: 17
    After more than 48 hours, we stopped the restore. We are trying to come up with another solution that may work. We were told that the restore process would be as faster, if not faster than the backup process. That obviously isn't the case.
    Tom
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Given that you were creating a new database from the restore, SQL Server would have first preallocated 260 GB of space for the new database. That would have taken some time, depending on the speed of your drives.

    If you do attempt the restore again using SQL Backup, you can use

    EXEC master..sqbstatus

    to monitor the restore progress.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • jlucasjlucas Posts: 4
    I have the same issue where it is locking up, but shows the restore already being completed. However, with my issue, I cannot get the restore to stop altogether. This is a very small restore I am working with (4MB).

    I closed management studio and stopped and started the SQL Backup Agent (twice). Red Gate and my server still show the database is being restored.

    Any thoughts on what I should do?
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Could you please send me the backup file? Could you also please run
    EXEC master..sqbtutility 9997
    which generates a log file named SQBCoreService_bugreport.txt in the folder where the SQL Backup Agent service executable (SQBCoreService.exe) is installed, and send me that file?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi all,

    I'm collating all of these reports of 'hanging restore jobs' and I am having difficulty finding any commonality at all.

    jlucas -- you're saying that the database is in a restoring state, what is the indication? Is the word (restoring) next to the database name in SQL Server Manaagement Studio and SQL Backup? If so, you may have simply forgot to restore the database using the WITH RECOVERY option.

    Thomas -- are you running this restore job from SQL Backup, using the sqlbackup extended stored procedure in a query tool like Enterprise Manager, or as part of a scheduled SQL Agent job? What is the indication that the restore is taking place still, for instance that SQL Agent shows the job as running, or an sp_who2 query, or the sqbstatus extended stored procedure?

    Looking forward to your replies.
    · Share on Twitter
  • jlucasjlucas Posts: 4
    Brian Donahue wrote:
    jlucas -- you're saying that the database is in a restoring state, what is the indication? Is the word (restoring) next to the database name in SQL Server Manaagement Studio and SQL Backup? If so, you may have simply forgot to restore the database using the WITH RECOVERY option.

    Yes, that is correct. What is quirky about this all is that Red Gate says the restore is complete. I also noticed that it did not allow me any way to break the restore process (I was trying just ot get into my database at this point). I did try to stop the SQL Backup Agent, but it didn't work.

    What I ended up doing was:
    - Wait a few hours
    - Verify it still showed restoring next to the db name in Red Gate and Management Studio
    - I then did a differential restore which did the restore once again, but opened my database immediately

    This is really all the information I have to go off of. Is there a better way to handle this in the future? I already plan on creating a secondary backup process.
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Thanks for writing back.

    SQL Server is designed to support different levels of recovery. This means that you can complete a restore operation in a way that allows the server to accept additional restores afterwards. For instance, you can restore a full, then a differential, but if you want to do that you need to restore the full with NORECOVERY and in that circumstance, the status of the database is displayed as restoring in SSMS and SQL Backup.
    · Share on Twitter
  • jlucasjlucas Posts: 4
    That is exactly what happened and then the restoring never went away and I was locked out from doing anything to the database. In the future, I will do restores with the Operational selection.

    Do you know if there are any other ways to stop the restore if it seems frozen? I understand it may only restore portions of the database if I do this. I stopped the SQL Backup Agent and restarted it, but that didn't stop it.
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi,

    In your situation, the restore had completed successfully. The SQL Server is waiting for additional restores to be done so that the database can be brought online. If you specified WITH NORECOVERY by mistake and don't have additional backups to restore, then you can bring the database online by doing a 'fake' restore:

    RESTORE [MyDatabase] WITH RECOVERY

    This will put the database online.
    · Share on Twitter
  • jlucasjlucas Posts: 4
    Perfect! Thanks for the help.
    · Share on Twitter
Sign In or Register to comment.