Comments
7 comments
-
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. -
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 -
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. -
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 -
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 -
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?
-
Thanks Peter! I talked with our IT person and they changed the login.
Have a great day!
Sandy
Add comment
Please sign in to leave a comment.
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