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

T-SQL script to launch sqlbackup 5.2

sskaarsskaar Posts: 6
edited February 13, 2008 10:29AM in SQL Backup Previous Versions
Good Afternoon,

I just upgraded to sqlbackup 5.2 and now my old extended stored proc is not working. I get an error of:
Error 505: No valid SQL Backup file names entered.
Could someone please advise what is wrong with my script - it worked fine in previous versions.

Thanks!

Here is my script:

DECLARE @filename varchar(255)
DECLARE @dbname varchar(255)
DECLARE @sql varchar(2000)

DECLARE databases CURSOR FOR
SELECT [name]
FROM master.dbo.sysdatabases
WHERE
status != 536 and --offline
dbid != 2 --tempdb
ORDER BY
[name]


OPEN databases
FETCH NEXT FROM databases INTO @dbname
@fetch_status = 0
BEGIN

SET @filename = convert(varchar,getdate(),112) + '.sqb'

SET @sql =
'-SQL "BACKUP DATABASE TO DISK = ''\\monstor\backups\engsql02\'+@dbname++@filename'' WITH COMPRESSION = 1, ERASEFILES = 3"'

EXEC master.dbo.sqlbackup @sql

FETCH NEXT FROM databases INTO @dbname
END
CLOSE databases
DEALLOCATE databases

Comments

  • Options
    peteypetey Posts: 2,358 New member
    This line

    SET @sql =
    '-SQL "BACKUP DATABASE TO DISK = ''\\monstor\backups\engsql02\'+@dbname++@filename'' WITH COMPRESSION = 1, ERASEFILES = 3"'

    needs to be changed to

    SET @sql =
    '-SQL "BACKUP DATABASE TO DISK = ''\\monstor\backups\engsql02\'+@dbname+@filename + ''' WITH COMPRESSION = 1, ERASEFILES = 3"'

    If that was just a typo on your part, could you please tell me if the backup fails for all databases, or for just a particular database? What is the generated backup command for that particular database?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Edited by Moderators to Remove Account Credentials

    Hi Peter,

    Thanks for your reply, yes, that was a typo (sorry about that)

    Here's what I have now, which is working - however, when I change the folder to the network file it fails..the file path I need to save to is:

    \\monstor\backups\engsql02\'+@dbname+@filename+'''

    here is my code that works today:

    DECLARE @filename varchar(255)
    DECLARE @dbname varchar(255)
    DECLARE @sql varchar(2000)
    DECLARE @exitcode int
    DECLARE @sqlerrorcode int

    DECLARE databases CURSOR FOR
    SELECT [name]
    FROM master.dbo.sysdatabases
    WHERE
    status != 536 and --offline
    dbid != 2 --tempdb
    ORDER BY
    [name]


    OPEN databases
    FETCH NEXT FROM databases INTO @dbname
    @fetch_status = 0
    BEGIN
    SET @filename = replace(replace(replace(convert(varchar,getdate(),120) + '.sqb','-',''),':',''),' ','')
    SET @sql =
    '-SQL "BACKUP DATABASE TO DISK = ''d:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\'+@dbname+@filename+'''
    WITH ERASEFILES_ATSTART = 2,
    COMPRESSION = 1"
    -U sa
    -P <snipped>'

    EXEC master.dbo.sqlbackup @sql

    FETCH NEXT FROM databases INTO @dbname
    END
    CLOSE databases
    DEALLOCATE databases
  • Options
    peteypetey Posts: 2,358 New member
    If you replace

    EXEC master.dbo.sqlbackup @sql

    with

    PRINT @sql

    and try running the generated script which backs up to the network share, does it still fail? Could you please post the generated script?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Edited by Moderators to Remove Account Credentials

    This is one of the variable values produced by the cursor:

    -SQL "BACKUP DATABASE [BLENDER] TO DISK = '\\monstor\backups\engsql02BLENDER20080213085401.sqb'
    WITH ERASEFILES_ATSTART = 2,
    COMPRESSION = 1"
    -U sa
    -P <snipped>

    Thanks!

    Sandy
  • Options
    Edited by Moderators to Remove Account Credentials

    This is one of the variable values produced by the cursor:

    -SQL "BACKUP DATABASE [BLENDER] TO DISK = '\\monstor\backups\engsql02\BLENDER20080213085545.sqb'
    WITH ERASEFILES_ATSTART = 2,
    COMPRESSION = 1"
    -U sa
    -P <snipped>

    Thanks!

    Sandy
  • Options
    peteypetey Posts: 2,358 New member
    I think the error is because SQL Backup does not have access to the network share (\\monstor\backups...). Could you please check if the SQL Backup Agent service startup user has read/write rights to the network folder?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Thanks Peter! I talked with our IT person and they changed the login.

    Have a great day!

    Sandy
Sign In or Register to comment.