Results 1 to 4 of 4

Thread: backup error

  1. #1
    Join Date
    May 2011
    Posts
    9

    Unanswered: backup error

    Hi,

    we have an SP that is used in SQL 2005 to backup our logs

    USE [master]
    GO
    /****** Object: StoredProcedure [dbo].[dba_logbackup] Script Date: 03/07/2013 15:18:49 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[dba_logbackup]

    AS

    -- Transaction log backup (Full / Bulk Logged recovery model databases)

    SET NOCOUNT ON

    PRINT CONVERT(VARCHAR, GETDATE(), 113)
    SELECT ''

    DECLARE
    @isDefault NVARCHAR(255),
    @dataPath NVARCHAR(255),
    @defaultBackupPath NVARCHAR(255),
    @workPath NVARCHAR(255),
    @workBackupPath NVARCHAR(255),
    @regKey NVARCHAR(255),
    @cmd NVARCHAR(4000),
    @databaseName SYSNAME,
    @backupFileName NVARCHAR(255),
    @backupSetId INT,
    @backupSetName NVARCHAR(128)


    -- Get default backup directory
    SELECT @isDefault = COALESCE((CONVERT(VARCHAR(75),SERVERPROPERTY('Inst anceName'))),'MSSQLSERVER')
    SELECT @regKey = 'SOFTWARE\Microsoft\' + @isDefault + '\MSSQLSERVER'
    EXEC master.dbo.xp_regread
    @rootkey = 'HKEY_LOCAL_MACHINE',
    @key = @regKey,
    @value_name = 'BackupDirectory',
    @value = @defaultBackupPath OUTPUT


    -- Create working directory
    SELECT @workPath = @defaultBackupPath + '\BACKUP_JOB'

    SELECT @workBackupPath = @workPath + '\BACKUP'
    SELECT @cmd = 'MKDIR ' + @workBackupPath
    EXEC master.dbo.xp_cmdshell @cmd


    -- Backup all database transaction logs
    DECLARE db_cur CURSOR FOR
    SELECT name,
    @workBackupPath + '\' + name + '_Log.bak',
    name + '_log_backup'
    FROM master.dbo.sysdatabases
    WHERE dbid <> 3 -- model
    AND Databasepropertyex(name, 'Recovery') <> 'SIMPLE'
    AND NOT( status & 32 = 32
    OR status & 64 = 64
    OR status & 128 = 128
    OR status & 256 = 256
    OR status & 512 = 512
    OR status & 1024 = 1024
    OR status & 2048 = 2048
    OR status & 4096 = 4096
    OR status & 32768 = 32768
    OR status & 262144 = 262144 )

    OPEN db_cur

    FETCH NEXT
    FROM db_cur
    INTO @databaseName,
    @backupFileName,
    @backupSetName

    WHILE @@FETCH_STATUS = 0
    BEGIN
    PRINT 'Backing up DB log: ' + @databaseName

    BACKUP LOG @databaseName
    TO DISK = @backupFileName
    WITH NOFORMAT,
    NOINIT,
    NAME = @backupSetName,
    SKIP,
    NOREWIND,
    NOUNLOAD,
    STATS = 10

    IF @@ERROR <> 0
    BEGIN
    PRINT 'Backup failed'
    GOTO NextDatabase
    END

    SELECT @backupSetId = position
    FROM msdb..backupset
    WHERE database_name = @databaseName
    AND backup_set_id = ( SELECT MAX(backup_set_id)
    FROM msdb..backupset
    WHERE database_name = @databaseName )

    IF @backupSetId IS NULL
    BEGIN
    RAISERROR(N'Verify failed. Backup information for database %s not found.', 16, 1, @databaseName)
    PRINT 'Verify failed'
    GOTO NextDatabase
    END

    RESTORE VERIFYONLY
    FROM DISK = @backupFileName
    WITH FILE = @backupSetId,
    NOUNLOAD,
    NOREWIND

    NextDatabase:
    FETCH NEXT
    FROM db_cur
    INTO @databaseName,
    @backupFileName,
    @backupSetName

    PRINT CONVERT(VARCHAR, GETDATE(), 113)
    SELECT ''
    END

    CLOSE db_cur
    DEALLOCATE db_cur


    scripting it out & running it in 2008 R2 returns the following error

    Msg 3044, Level 16, State 2, Procedure dba_logbackup, Line 76
    Invalid zero-length device name. Reissue the BACKUP statement with a valid device name.
    Msg 3013, Level 16, State 1, Procedure dba_logbackup, Line 76
    BACKUP LOG is terminating abnormally.

    i've found this link which suggests we are using a NULL device name

    Invalid zero-length device name. Reissue the BACKUP statement with a valid device name Archives - SQL Server Consultancy & Training

    however the TO DISK line in out script is TO DISK = @backupFileName

    accidental dba here

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    My guess is the registry read has been secured down, and may be giving you a null path, or the switch in directory naming is causing you to query a non-existant key. Run everything up to the xp_regread procedure, then see what the variable has in it.

    Code:
    -- snipped a bunch of variables being declared and set
    
    EXEC master.dbo.xp_regread
    @rootkey = 'HKEY_LOCAL_MACHINE',
    @key = @regKey,
    @value_name = 'BackupDirectory',
    @value = @defaultBackupPath OUTPUT
    
    select @defaultBackupPath

  3. #3
    Join Date
    Mar 2013
    Posts
    10
    you could probably simplify this a whole lot with sp_msforeachdb, and get rid of that cursor.

  4. #4
    Join Date
    May 2011
    Posts
    9
    sorry for delayed response, this all came down to permissions, first to the registry & secondly to the output location.

    regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •