Options
SQLBackup 5.4 Verify problem still exists with SQL2000
ChrisAVWood
Posts: 361
Hi,
I am starting to upgrade from 5.3 to 5.4 on my servers. One of the issues that was fixed in 5.4 was with a job that has VERIFY it would not always show the verify in the History and that the job would stay in the In Progress screen when it had finished.
I just ran an SQL Agent job to take a full backup with verify on SQL2000 SP4 build 2050 on W2K3 SP2 and both of these issues happened.
Here is the log entry
SQL Backup log file 5.4.0.55
-SQL "BACKUP DATABASE [LLINK_DEVL] TO DISK = 'G:\APPLBKUP\LLINK\DEVL\FULL\<AUTO>.sqb' WITH COMPRESSION = 2, ERASEFILES = 46h, VERIFY "
PROCESSES COMPLETED SUCCESSFULLY
04/12/2008 9:32:19 AM: Backing up LLINK_DEVL (full database) to:
04/12/2008 9:32:19 AM: G:\APPLBKUP\LLINK\DEVL\FULL\FULL_LLINK_DEVL_20081204_093219.sqb
04/12/2008 9:32:19 AM: BACKUP DATABASE [LLINK_DEVL] TO VIRTUAL_DEVICE = 'SQLBACKUP_FCCD48F7-8672-4CE6-828F-68CDADBF5495' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (LLINK_DEVL), 04/12/2008 9:32:19 AM', DESCRIPTION = N'Backup on 04/12/2008 9:32:19 AM Server: EITUT06 Database: LLINK_DEVL', FORMAT
04/12/2008 9:33:40 AM: Database size : 8.262 GB
04/12/2008 9:33:40 AM: Compressed data size: 1,014.083 MB
04/12/2008 9:33:40 AM: Compression rate : 88.01%
Processed 272 pages for database 'LLINK_DEVL', file 'LLINK_SYS' on file 1.
Processed 687000 pages for database 'LLINK_DEVL', file 'LLINK_DATA_001' on file 1.
Processed 1 pages for database 'LLINK_DEVL', file 'LLINK_LOG_001' on file 1.
BACKUP DATABASE successfully processed 687273 pages in 80.608 seconds (69.845 MB/sec).
04/12/2008 9:33:40 AM:
04/12/2008 9:33:40 AM: Verifying files:
04/12/2008 9:33:40 AM: G:\APPLBKUP\LLINK\DEVL\FULL\FULL_LLINK_DEVL_20081204_093219.sqb
04/12/2008 9:33:40 AM:
The backup set is valid.
04/12/2008 9:34:41 AM: SQL Backup process ended.
04/12/2008 9:34:41 AM: Deleted msdb entries older than 04/11/2008 9:34:41 AM
04/12/2008 9:34:41 AM: Deleted local history entries older than 04/11/2008 9:34:41 AM
Chris
I am starting to upgrade from 5.3 to 5.4 on my servers. One of the issues that was fixed in 5.4 was with a job that has VERIFY it would not always show the verify in the History and that the job would stay in the In Progress screen when it had finished.
I just ran an SQL Agent job to take a full backup with verify on SQL2000 SP4 build 2050 on W2K3 SP2 and both of these issues happened.
Here is the log entry
SQL Backup log file 5.4.0.55
-SQL "BACKUP DATABASE [LLINK_DEVL] TO DISK = 'G:\APPLBKUP\LLINK\DEVL\FULL\<AUTO>.sqb' WITH COMPRESSION = 2, ERASEFILES = 46h, VERIFY "
PROCESSES COMPLETED SUCCESSFULLY
04/12/2008 9:32:19 AM: Backing up LLINK_DEVL (full database) to:
04/12/2008 9:32:19 AM: G:\APPLBKUP\LLINK\DEVL\FULL\FULL_LLINK_DEVL_20081204_093219.sqb
04/12/2008 9:32:19 AM: BACKUP DATABASE [LLINK_DEVL] TO VIRTUAL_DEVICE = 'SQLBACKUP_FCCD48F7-8672-4CE6-828F-68CDADBF5495' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (LLINK_DEVL), 04/12/2008 9:32:19 AM', DESCRIPTION = N'Backup on 04/12/2008 9:32:19 AM Server: EITUT06 Database: LLINK_DEVL', FORMAT
04/12/2008 9:33:40 AM: Database size : 8.262 GB
04/12/2008 9:33:40 AM: Compressed data size: 1,014.083 MB
04/12/2008 9:33:40 AM: Compression rate : 88.01%
Processed 272 pages for database 'LLINK_DEVL', file 'LLINK_SYS' on file 1.
Processed 687000 pages for database 'LLINK_DEVL', file 'LLINK_DATA_001' on file 1.
Processed 1 pages for database 'LLINK_DEVL', file 'LLINK_LOG_001' on file 1.
BACKUP DATABASE successfully processed 687273 pages in 80.608 seconds (69.845 MB/sec).
04/12/2008 9:33:40 AM:
04/12/2008 9:33:40 AM: Verifying files:
04/12/2008 9:33:40 AM: G:\APPLBKUP\LLINK\DEVL\FULL\FULL_LLINK_DEVL_20081204_093219.sqb
04/12/2008 9:33:40 AM:
The backup set is valid.
04/12/2008 9:34:41 AM: SQL Backup process ended.
04/12/2008 9:34:41 AM: Deleted msdb entries older than 04/11/2008 9:34:41 AM
04/12/2008 9:34:41 AM: Deleted local history entries older than 04/11/2008 9:34:41 AM
Chris
English DBA living in CANADA
Comments
I was hoping that this was part of 5.4. Please see that it can make it into 5.5 or would that be 6.0? Does you fix include the waittype MSQL_XP fix?
Chris
As Nigel mentioned, we hope that it will be fixed in the next release. Because of the visible nature of the problem, if it is included in the next release, it will be covered in the release notes (or alternatively you can ask the support team for an update specifically on bug issue SB-3421 and they will be able to confirm if it has been fixed or not).
As for the "waittype MSQL_XP fix", I have looked back at the previous thread at http://www.red-gate.com/messageboard/vi ... php?t=7908 and I believe there may have been some confusion.
The MSQL_XP wait type in SQL Server indicates that SQL Server is waiting for the extended stored procedure to complete. Because of the way SQL Backup functions (that is, everything is done through the extended stored procedure call), the connection will indicate one of two states - either idle (task hasn't started, or has finished) or MSQL_XP (waiting for master..sqlbackup to return).
It would have no effect on the speed of the backup, it's just a status update.
Now, if a backup or restore task is taking substantially longer than expected, there is one feature within SQL Backup that can help with identifying the state of the backup, which is the master..sqbstatus command:
Using SQL Backup 5, execute the following:
This will tell you how much of the backup has been completed, so it can be used to give an idea of progress so far.
If you need any further information on this, please let us know.
Hope that helps,
Jason
Chris
Chris