TLOG Backup query works outside of a procedure, but not inside ...

Question

Post reply

TLOG Backup query works outside of a procedure, but not inside ...

Adam Bean

One Orange Chip

Points: 26631
More actions
February 12, 2008 at 12:04 pm

#179063

This is absolutely crazy ... If I run the following outside of a procedure, it works fine. Yet when I run it as a procedure, the TLOG backup piece doesn't happen. In both cursors (one for litespeed, one for native), it moves to the next database as soon as the verify step has completed (-- Verify backup is valid). Even if I put a print or select right after the verify step, it doesn't happen ... just moves onto the next database.
Any thoughts?
--------------
IF OBJECT_ID('DailyDatabaseBackups','P') IS NOT NULL
DROP PROCEDURE DailyDatabaseBackups
GO
/*******************************************************************************************************
**Name:admin.dbo.DailyDatabaseBackups
**Desc:Runs full SQL Server database and transaction log backups
**Auth:Adam Bean
**Date:02/04/2008
*******************************************************************************
**Change History
*******************************************************************************
**Date:Author:Description:
**-------------------------------------------------------
**
********************************************************************************************************/
CREATE PROCEDURE [dbo].[DailyDatabaseBackups]
AS
SET NOCOUNT ON
DECLARE
@ServerNameVARCHAR(96)
,@InstanceNameVARCHAR(96)
,@BULocationVARCHAR(256)
,@DBNameSYSNAME
,@BUPathVARCHAR(256)
,@BUNameVARCHAR(256)
,@DatabaseSYSNAME
,@ProductVersionVARCHAR(16)
,@ReturnCodeTINYINT
SET @BULocation = 'mybuloc'
SET @ReturnCode = 0
-- Setup table to hold database names
DECLARE @DBNames TABLE
(
name VARCHAR(256)
)
-- Determine backup path based on servername
SELECT @ServerName = @@SERVERNAME
IF CHARINDEX('\',@ServerName) > 1
BEGIN
SET @ServerName = LEFT(@ServerName,CHARINDEX('\',@ServerName) - 1)
SELECT @InstanceName = CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(96))
SET @BUPath = @BULocation + '\' + @ServerName + '\' + @InstanceName + '\'
END
ELSE
BEGIN
SET @ServerName = @ServerName
SET @BUPath = @BULocation + '\' + @ServerName + '\'
END
-- Determine SQL version
SELECT @ProductVersion = CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(16))
-- Populate table with database names
IF LEFT(@ProductVersion,1) = 8
BEGIN
INSERT INTO @DBNames
SELECT name FROM master.dbo.sysdatabases
WHERE name <> 'tempdb'
AND name NOT LIKE '%AdventureWorks%'
AND DATABASEPROPERTY(name, 'IsOffline') = 0
AND DATABASEPROPERTY(name, 'IsSuspect') = 0
ORDER BY NAME
END
ELSE
BEGIN
INSERT INTO @DBNames
SELECT NAME FROM master.sys.databases
WHERE name <> 'tempdb'
AND name NOT LIKE '%AdventureWorks%'
AND source_database_id IS NULL
AND state_desc = 'ONLINE'
AND user_access_desc = 'MULTI_USER'
AND is_in_standby = 0
ORDER BY NAME
END
-- Run Native or LiteSpeed backup
IF EXISTS(SELECT * FROM master.dbo.sysobjects WHERE name = 'xp_backup_database' AND xtype = 'x')
BEGIN -- Backup database in LiteSpeed
DECLARE DBList CURSOR FOR
SELECT name FROM @DBNames
OPEN DBList
FETCH NEXT FROM DBList INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Backup database
SET @BUName = @BUPath + @DBName + '.lsbak'
EXEC master.dbo.xp_backup_database
@Database=@DBName
,@FileName=@BUName
,@Init=1
,@Threads=2
,@CompressionLevel=2
-- Verify backup is valid
EXEC master.dbo.xp_restore_verifyonly @Filename = @BUName
-- Run TLOG backup if not in simple
IF (SELECT CAST(DATABASEPROPERTYEX(@DBName, 'Recovery') AS VARCHAR(24))) <> 'Simple'
BEGIN
SET @BUName = @BUPath + '\TLOGS\' + @DBName + '.lstrn'
EXEC master.dbo.xp_backup_log
@Database=@DBName
,@Filename=@BUName
,@Init=1
,@Threads=2
,@CompressionLevel=2
END
FETCH NEXT FROM DBList INTO @DBName
END
CLOSE DBList
DEALLOCATE DBList
END
ELSE
BEGIN -- Backup database in Native
DECLARE DBList CURSOR FOR
SELECT name FROM @DBNames
OPEN DBList
FETCH NEXT FROM DBList INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Backup database
SET @BUName = @BUPath + @DBName + '.bak'
BACKUP DATABASE @DBName
TO DISK = @BUName
WITH INIT
-- Verify backup is valid
RESTORE VERIFYONLY FROM DISK = @BUName
-- Run TLOG backup if recovery model is full
IF (SELECT CAST(DATABASEPROPERTYEX(@DBName, 'Recovery') AS VARCHAR(24))) <> 'Simple'
BEGIN
SET @BUName = @BUPath + '\TLOGS\' + @DBName + '.trn'
BACKUP LOG @DBName
TO DISK = @BUName
WITH INIT
END
FETCH NEXT FROM DBList INTO @DBName
END
CLOSE DBList
DEALLOCATE DBList
END
-- Return status
IF @@ERROR <> 0
BEGIN
SET @ReturnCode = @@ERROR
RETURN @ReturnCode
END
ELSE
BEGIN
RETURN @ReturnCode
END
SET NOCOUNT OFF

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Adam Bean One Orange Chip Points: 26631 More actions · Answer 1

Wow, this is nuts ...

So If I put the comment out the backup or put it into dynamic SQL:

SET @BUName = @BUPath + @DBName + '.bak'

BACKUP DATABASE @DBName

TO DISK = @BUName

WITH INIT

The whole process works fine ... so there is something happening after the backup of the db takes place that does not allow any additional commands to be run.

I tried wrapping the whole thing in a try/catch to pull back any information, got nothing ... successful return.

I am absolutely baffled.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

So this works ... just dumped the db backup into dynamic SQL:

I'd really like to hear any type of explanation ...

Thanks

IF OBJECT_ID('DailyDatabaseBackups','P') IS NOT NULL

DROP PROCEDURE DailyDatabaseBackups

GO

/*******************************************************************************************************

**Name:admin.dbo.DailyDatabaseBackups

**Desc:Runs full SQL Server database and transaction log backups

**Auth:Adam Bean

** Notes:Backup has to be done in Dynamic or the TLOG backup won't proceed

**Date:02/04/2008

*******************************************************************************

**Change History

*******************************************************************************

**Date:Author:Description:

**-------------------------------------------------------

**02.12.08ASBRemoved need for two cursors

**02.12.08ASBAdded TLOG backups

********************************************************************************************************/

CREATE PROCEDURE [dbo].[DailyDatabaseBackups]

AS

SET NOCOUNT ON

DECLARE

@ServerNameVARCHAR(96)

,@LSBUTINYINT

,@InstanceNameVARCHAR(96)

,@BULocationVARCHAR(256)

,@DBNameSYSNAME

,@BUPathVARCHAR(256)

,@BUNameVARCHAR(256)

,@DatabaseSYSNAME

,@ProductVersionVARCHAR(16)

,@SQLVARCHAR(512)

,@ReturnCodeTINYINT

SET @BULocation = '\\sql1\SQLBU'

SET @ReturnCode = 0

SET @LSBU = 0

-- Determine if server has litespeed

IF OBJECT_ID('master.dbo.xp_backup_database','X') IS NOT NULL

SET @LSBU = 1

-- Setup table to hold database names

DECLARE @DBNames TABLE

(

name VARCHAR(256)

)

-- Determine backup path based on servername

SELECT @ServerName = @@SERVERNAME

-- Temporary work around for WWR-SQL's servername

IF @ServerName = 'WWR-DW'

SET @ServerName = 'WWR-SQL'

IF CHARINDEX('\',@ServerName) > 1

BEGIN

SET @ServerName = LEFT(@ServerName,CHARINDEX('\',@ServerName) - 1)

SELECT @InstanceName = CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(96))

SET @BUPath = @BULocation + '\' + @ServerName + '\' + @InstanceName + '\'

END

ELSE

BEGIN

SET @ServerName = @ServerName

SET @BUPath = @BULocation + '\' + @ServerName + '\'

END

-- Determine SQL version

SELECT @ProductVersion = CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(16))

-- Populate table with database names

IF LEFT(@ProductVersion,1) = 8

BEGIN

INSERT INTO @DBNames

SELECT name FROM master.dbo.sysdatabases

WHERE name <> 'tempdb'

AND name NOT LIKE '%AdventureWorks%'

AND DATABASEPROPERTY(name, 'IsOffline') = 0

AND DATABASEPROPERTY(name, 'IsSuspect') = 0

ORDER BY NAME

END

ELSE

BEGIN

INSERT INTO @DBNames

SELECT NAME FROM master.sys.databases

WHERE name <> 'tempdb'

AND name NOT LIKE '%AdventureWorks%'

AND source_database_id IS NULL

AND state_desc = 'ONLINE'

AND user_access_desc = 'MULTI_USER'

AND is_in_standby = 0

ORDER BY NAME

END

-- Cursor through databases

DECLARE DBList CURSOR FOR

SELECT name FROM @DBNames

OPEN DBList

FETCH NEXT FROM DBList INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

IF @LSBU = 1 -- Backup database in LiteSpeed

BEGIN

-- Backup database

SET @BUName = @BUPath + @DBName + '.lsbak'

SET @sql=

'

EXEC master.dbo.xp_backup_database

@Database=''' + @DBName + '''

,@FileName=''' + @BUName + '''

,@Init=1

,@Threads=2

,@CompressionLevel=2

'

EXEC(@SQL)

-- Verify backup is valid

EXEC master.dbo.xp_restore_verifyonly @Filename = @BUName

-- Run TLOG backup if not in simple

IF (SELECT CAST(DATABASEPROPERTYEX(@DBName, 'Recovery') AS VARCHAR(24))) <> 'Simple'

BEGIN

SET @BUName = @BUPath + '\TLOGS\' + @DBName + '.lstrn'

EXEC master.dbo.xp_backup_log

@Database=@DBName

,@Filename=@BUName

,@Init=1

,@Threads=2

,@CompressionLevel=2

END

FETCH NEXT FROM DBList INTO @DBName

END

ELSE -- Backup database in Native

BEGIN

-- Backup database

SET @BUName = @BUPath + @DBName + '.bak'

SET @sql=

'

BACKUP DATABASE ' + @DBName + '

TO DISK = ''' + @BUName + '''

WITH INIT

'

EXEC(@SQL)

-- Verify backup is valid

RESTORE VERIFYONLY FROM DISK = @BUName

-- Run TLOG backup if recovery model is full

IF (SELECT CAST(DATABASEPROPERTYEX(@DBName, 'Recovery') AS VARCHAR(24))) <> 'Simple'

BEGIN

SET @BUName = @BUPath + '\TLOGS\' + @DBName + '.trn'

BACKUP LOG @DBName

TO DISK = @BUName

WITH INIT

END

FETCH NEXT FROM DBList INTO @DBName

END

CLOSE DBList

DEALLOCATE DBList

-- Return status

IF @@ERROR <> 0

BEGIN

SET @ReturnCode = @@ERROR

RETURN @ReturnCode

END

ELSE

BEGIN

RETURN @ReturnCode

END

SET NOCOUNT OFF