T-SQL script to launch sqlbackup 5.2

sskaar

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

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.

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

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.

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

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

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?

sskaar

Thanks Peter! I talked with our IT person and they changed the login.

Have a great day!

Sandy