master..sqlbackup is slow to complete after restore
turner732
Posts: 4 Bronze 1
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=''turnerr@i-netsolutions.us''"'
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
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=''turnerr@i-netsolutions.us''"'
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
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8