Can't restore from backup

R J · November 5, 2007 12:51PM

Recently I have not been able to restore from our backup files which are about 685MB in size. I have a feeling the size is the problem, but not sure how to resolve it. I have searched the forums, but didn't find the same situation as mine, with a solution.

The backup is created on the server, then stored on a backup location. I am using my desktop system to restore the file. My desktop and the server are using SQLBackup 4.2

When I try to restore to my desktop, it runs for several hours and I finally just give up. I don't restore these files very often, but I believe in the past they usually have restored in about 45 minutes.

I tried installing the trial for SQLBackup 5 on another machine, and I tried restoring again. This time, I got the "VDI Error 1010: Failed to get confirmation from the server." message. It runs for quite a while before returning this message. And, yes, the permissions are fine and nobody else is using the database.

My backup application uses the following command to create the backup:

objCommand.CommandText = "master..sqlbackup N'-SQL ""BACKUP DATABASES &#91;" & strDatabaseName & "&#93;  " & _
	" TO DISK = ''" & strLocalBackupPath & "&lt;AUTO&gt;'' WITH NAME = ''&lt;AUTO&gt;'', " & _
	" DESCRIPTION = ''&lt;AUTO&gt;'', PASSWORD = ''&lt;ENCRYPTEDPASSWORD&gt;" & strBackupPassword & "&lt;/ENCRYPTEDPASSWORD&gt;'', " & _
	" KEYSIZE = 128, ERASEFILES = 30, COMPRESSION = 3, THREADS = 1""'"

I saw some posts about MAXTRANSFERSIZE, but not sure how to use it. Any help would be appreciated. Currently, I am using native SQL backup which takes hours...

petey · November 5, 2007 11:02PM

Download the SQB2MTF GUI application from here, and try converting the SQL Backup file. If it fails, what are the errors that are reported?

Thanks.

R J · November 5, 2007 11:35PM

Thanks for the response.

Interesting, the error was:
File read error for 46800 bytes. Backup file is incomplete or corrupted.

petey · November 6, 2007 12:10AM

Does this happen towards the beginning/middle/end of the file? Does it happen for all files?

Thanks.

R J · November 6, 2007 1:09PM

I believe it's towards the end, but can't be sure. Since the file is large, everything takes a while, so I don't sit and watch it the whole time.

It happens with all of the new files. Anything older than a couple weeks ago seems to be fine. I have a feeling it is because of the size. I saw some people mention changing compression to 2 instead of 3 for large files. So, I might try that.

petey · November 6, 2007 9:11PM

Could you please check if the logs for the backups indicate any errors? The default folder for the logs is <system folder>:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name>.

R J · November 6, 2007 10:57PM

11/3/2007 2:54:57 PM: 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.)
11/3/2007 2:55:02 PM: SQL error 3013: RESTORE DATABASE is terminating abnormally.
11/3/2007 2:55:02 PM: SQL error 3101: Exclusive access could not be obtained because the database is in use.

---

The database was not in use. The error message is the same, even after rebooting, or when trying to restore to a brand new database. If I select an older file, I can restore with no problem.

petey · November 6, 2007 11:51PM

Actually, I was wondering if you could check the logs for the backups, to see if there were any errors reported. Thanks.

R J · November 6, 2007 11:56PM

It looks like sometimes it gets an error, but it retries then everything is successful. On the 3rd, which is when the fiile I was working with was created, there were no errors in the log. On the 4th, there was an error:

11/4/2007 4:00:15 AM: 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:  &#40;-2139684861: The api was waiting and the timeout interval had elapsed.&#41;
11/4/2007 4:05:46 AM: SQL error 3013: BACKUP DATABASE is terminating abnormally.
11/4/2007 4:05:46 AM: 
11/4/2007 4:05:46 AM: Memory profile
11/4/2007 4:05:46 AM: Type             Maximum     Minimum     Average     Blk count   Total      
11/4/2007 4:05:46 AM: ---------------- ----------- ----------- ----------- ----------- -----------
11/4/2007 4:05:46 AM: Commit           657653760   4096        2275445     787         1790775296 
11/4/2007 4:05:46 AM: Reserve          1028096     4096        341584      147         50212864   
11/4/2007 4:05:46 AM: Free             161755136   4096        2070472     148         306429952  
11/4/2007 4:05:46 AM: Private          657653760   4096        3524985     510         1797742592 
11/4/2007 4:05:46 AM: Mapped           1060864     4096        132585      46          6098944    
11/4/2007 4:05:46 AM: Image            6156288     4096        98271       378         37146624   
11/4/2007 4:05:46 AM: 
11/4/2007 4:05:46 AM: Warning 300: Backup failed.  Retry attempt: 1

After the retry, it was successful.

petey · November 12, 2007 9:51PM

Would it be possible for you to send me a couple of the backup files that cannot be restored? How large are they?

R J · November 12, 2007 10:05PM

685MB. However, allowing others to access the data is basically impossible because of the type of data we're storing.

petey · November 12, 2007 10:54PM

Sorry, I forgot that you already mentioned the backup size in your first post.

Is your situation now as follows:
- every backup that you have created for that database in the last 2 weeks cannot be restored on your workstation
- no errors were reported for the backups (error codes >= 500). Backups that suceeded on retries are ok.

Do you restore directly from the original backup file, or from a copy? If it is from a copy, could you please compare if the original and copy are identical (e.g. using a hash generator).

Would it also be possible to create the backups on another disk, to eliminate the possibility that it is a disk issue?

Thanks.

R J · November 13, 2007 1:30AM

I wrote something to run native SQL backup then zip and ftp the file. That works, it just takes longer. I have been swamped with other things, so I will do some testing later. I read in some other threads where it was suggested to try setting the compression to 2, but I have not tried that yet.

petey · November 13, 2007 2:10AM

The error referred to in the other threads would result in a failed backup. In your case, the backups appear to be succeeded, since no errors were reported.

Can't restore from backup

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk