When running a Maintenance Plan, the backup may succeed and the verify portion of the plan will fail. You may get a similar message as follows:
Backup added as file number: 1 ... Performing restore verification of backup [SQLSTAT Failed to login to SQL Server [SQLSTATE 42000] (Error 49999). The step failed.
However, you may be able to successfully verify the backup file using the xp_restore_verifyonly store procedure.
When doing a backup & “verify the backup when done”, the engine will request a password to connect to SQL Server. This is like a shared key with an expiration. The engine is free to open as many connections as it wants with SQL Server, using that password .
WORKAROUND1:
Add or modify the following settings in the registry:
Navigate to: My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Imceda\SQLLiteSpeed\Engine
Create DWORD values: ‘ConnectTimeout’ and ‘ConnectRetries’
Edit or add the ‘ConnectTimeout’ and ‘ConnectRetries’ registry keys.
‘ConnectTimeout’ edit to 45
‘ConnectRetries’ edit to 4.
Declare@iint
execmaster.dbo.xp_backup_database
@database=N'model',
@backupname=N'model - Full Database Backup',
@desc=N'Full Backup of model on 2/7/2011 9:47:39 AM',
@compressionlevel=1,
@filename=N'C:\Backups\SQL2005\model.bak',
@filenumber=@iout,
@init=0,
@with=N'SKIP',
@with=N'STATS = 10',
@logging=2
-- print @i
execmaster.dbo.xp_restore_verifyonly
@filename=N'C:\Backups\SQL2005\model.bak',
@filenumber=@i,
