T-SQL script to launch sqlbackup 5.2

• 
  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 February 13, 2008 00:19

  - 0 +
• 
  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 February 13, 2008 09:34

  - 0 +
• 
  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 February 13, 2008 09:48

  - 0 +
• 
  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 February 13, 2008 09:54

  - 0 +
• 
  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 February 13, 2008 09:55

  - 0 +
• 
  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 February 13, 2008 10:00

  - 0 +
• 
  Thanks Peter! I talked with our IT person and they changed the login.
  
  Have a great day!
  
  Sandy

  sskaar February 13, 2008 10:29

  - 0 +

Add comment

Please sign in to leave a comment.