What are the challenges you face when working across database platforms? Take the survey

master..sqlbackup is slow to complete after restore

turner732turner732 Posts: 4 Bronze 1
edited October 14, 2014 12:51AM in SQL Backup Previous Versions
I am supporting a site with 70+ databases using SQL Backup 7.7

We use the stored procedure below to restore logs shipped from a remote server.
Recently the master..sqlbackup is slow to complete after all the logs for the database have been restored and moved to another directory

Do you have any insight to what might be going wrong?
Might it have anything to do with the SQL Server Compact db that SQL Backup logs to?
We rarely open the UI

Thank You

ALTER PROCEDURE [dbo].[usp_restorelog] ( @DBNAME varchar(100) )
DECLARE @errorcode INT
DECLARE @sqlerrorcode INT
declare @RestoreStr varchar(2000), @RestStr varchar(2000)
SET @RestoreStr='-SQL "RESTORE LOG [XXX] FROM DISK = ''\aso_sql2008LogShippingLogsLOG_XXX_*.sqb'' WITH STANDBY = ''C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBackupUNDO_XXX.dat'', MOVETO = ''B:LogshippingLogsRestored'',ERASEFILES = 10, FILEOPTIONS = 1,DISCONNECT_EXISTING,MAILTO_ONERRORONLY=''turnerr@i-netsolutions.us''"'
set @RestStr= Replace(@RestoreStr,'XXX',@DBNAME);

EXECUTE master..sqlbackup @RestStr, @errorcode OUT, @sqlerrorcode OUT;
IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
SET @RestoreStr = 'SQL Backup failed for :'+@DBNAME
print @RestoreStr
--RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @errorcode, @sqlerrorcode)


Sign In or Register to comment.