How can we help you today? How can we help you today?

T-SQL script to launch sqlbackup 5.2

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
sskaar
0

Comments

7 comments

  • petey
    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.
    petey
    0
  • sskaar
    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
    sskaar
    0
  • petey
    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.
    petey
    0
  • sskaar
    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
    sskaar
    0
  • sskaar
    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
    sskaar
    0
  • petey
    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?
    petey
    0
  • sskaar
    Thanks Peter! I talked with our IT person and they changed the login.

    Have a great day!

    Sandy
    sskaar
    0

Add comment

Please sign in to leave a comment.