master..sqlbackup is slow to complete after restore

turner732turner732 New JerseyPosts: 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
Bob


ALTER PROCEDURE [dbo].[usp_restorelog] ( @DBNAME varchar(100) )
as
BEGIN
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=''[email protected]''"'
set @RestStr= Replace(@RestoreStr,'XXX',@DBNAME);

EXECUTE master..sqlbackup @RestStr, @errorcode OUT, @sqlerrorcode OUT;
IF (@errorcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
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)
END
END

Comments

Sign In or Register to comment.