Is this back up good?

programmer76

Hi,

I ran the job using red gate to backup this database. Job status says failed in the sql server agent history. In the red gate error logs here is the message i'm seeing...

---

SQL Backup log file
8/4/2007 10:00:01 PM: Backing up ABC (full database) to:
\\99.98.97.233\SQLDB\ABC\FULL_SQLDB_ABC_20070804_220000.sqb

8/4/2007 10:00:01 PM: BACKUP DATABASE [ABC] TO DISK = '\\99.98.97.233\SQLDB\ABC\FULL_SQLDB_ABC_20070804_220000.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', COMPRESSION = 1, THREADS = 1

8/4/2007 10:00:16 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.)
8/6/2007 2:18:30 AM: SQL error 3013: BACKUP DATABASE is terminating abnormally.
8/6/2007 2:18:30 AM:
8/6/2007 2:18:31 AM: Memory profile
8/6/2007 2:18:31 AM: Type Maximum Minimum Average Blk count Total
8/6/2007 2:18:31 AM:

---

---

---

---

---

---

8/6/2007 2:18:31 AM: Commit 279953408 4096 263197 5671 1492590592
8/6/2007 2:18:31 AM: Reserve 15319040 8192 77383 4691 363008000
8/6/2007 2:18:31 AM: Free 163819520 4096 1459097 200 291819520
8/6/2007 2:18:31 AM: Private 279953408 4096 183266 9853 1805725696
8/6/2007 2:18:31 AM: Mapped 1060864 4096 104576 64 6692864
8/6/2007 2:18:31 AM: Image 7569408 4096 97033 445 43180032
8/6/2007 2:18:31 AM:
8/6/2007 2:18:31 AM: Warning 300: Backup failed. Retry attempt: 1
8/6/2007 2:18:33 AM: BACKUP DATABASE [ABC] TO DISK = '\\99.98.97.233\SQLDB\ABC\FULL_SQLDB_ABC_20070804_220000.sqb' WITH NAME = 'Database (ABC), 8/4/2007 10:00:01 PM', DESCRIPTION = 'Backup on 8/4/2007 10:00:01 PM Server: SQLDB Database: ABC', INIT, COMPRESSION = 1, THREADS = 1

8/7/2007 3:37:26 AM: Backup data size : 579.624 GB
8/7/2007 3:37:26 AM: Compressed data size: 456.893 GB
8/7/2007 3:37:26 AM: Compression rate : 21.17%

Processed 75598064 pages for database 'ABC', file 'ABC_Data' on file 1.
Processed 374062 pages for database 'ABC', file 'ABC_Log' on file 1.
BACKUP DATABASE successfully processed 75972126 pages in 91128.168 seconds (6.829 MB/sec).

---

I've two questions.

(1) it seems like even though job has failed the back up file created by red gate is good.At the end it does say back data ae successfully processed the pages. Am i correct?

(2) Also is there a way in red gate to see if the .sqb file is good enough to restore the database. Is there any command I can write on the file similar to restorefileheaderlist only etc.?



Please reply soon asap.

Thanks
(2)

Jason Cook

Hi,
The reason that the job "failed" is because SQL Backup issued a VDI error 1010, and a SQL Server error 3013; which will have been passed back to the SQL Server Agent, and raised as a failure.

Infact, what has happened is that the VDI error (caused mostly by memory allocation issues) causes SQL Backup to try again, with a smaller memory requirement, up to a maximum of 4 retries.

In your case, on the first retry the backup succeeded, so the backup will be fine.

If you want to do some checks, you can use the RESTORE SQBHEADERONLY, RESTORE HEADERONLY and RESTORE FILELISTONLY commands to do some basic checks on the backup, but ultimately for full peace-of-mind, the safest solution would be to restore to a temporary server if possible (although I realise that's easier said than done when talking about 600GB of data).

Hope that helps,
Jason

programmer76

Thanks for your prompt reply. What should have caused the memory allocation issues? Does it occur because SQL server requires more memory Or may be by accident for some time sql server services were stopped and restatred ( i'm checking that with network admin)?

Becuase for peace of mind i really want to see job successfully run message and it is difficult to test this big back up on other server because of size constraints. If i really need more memory then job will again fail with the same message. Right?

Also can you please let me know some samples on how to use some command to check integrity of this .sqb backup.
Thanks

Jason Cook

Normally, the memory allocations are caused by SQL Server fragmenting the memory space. However looking at the included memory profile, that doesn't appear to be the case, so could have been caused by the server being particularly busy at that period in time.

Some of the commands available for checking the backup are (with optional password keyword, can remove WITH PASSWORD... if a password is not required):

execute master..sqlbackup '-SQL "RESTORE FILELISTONLY FROM DISK = ''...'' WITH PASSWORD = ''...'' "';

execute master..sqlbackup '-SQL "RESTORE SQBHEADERONLY FROM DISK = ''...'' WITH PASSWORD = ''...'' "';

execute master..sqlbackup '-SQL "RESTORE VERIFYONLY FROM DISK = ''...'' WITH PASSWORD = ''...'' "';

* RESTORE FILELISTONLY will return a list of the database files that are included in the backup. Invalid information here would indicate a corrupted backup.
* RESTORE SQBHEADERONLY returns the header information about the backup... this is usually finalised at the end of the backup, so if the backup "failed" this information would be invalid or incomplete.
* RESTORE VERIFYONLY performs some basic validation checks on the file... but does not go as far as doing a full file integrity check. It may spot major problems with the file, but smaller issues like corrupted bytes of data would not be spotted with this command.

Hopefully the three commands will be enough to feel happy about the integrity of the backup... between them they should indicate that the backup not only started, but was finished successfully, despite what the job status may indicate.

Hope that helps,
Jason