Backup multiple databases, excluding specific databases

• 
  Use the DATABASEPROPERTEX function e.g.
  
  set nocount on
  
  --
  --
  -- Description: Backs up all databases on the server via a call to the
  -- extended stored procedure for SQLBackup. Script will
  -- raise an error if the word "error" appears any where in
  -- the message string returned by the SQLBackup extended
  -- stored procedure call.
  --
  -- PreReq: The backup directory specified in @backuppath must already
  -- exist.
  --
  --
  
  declare @recoverymodel varchar(12)
  declare @datestamp varchar(30)
  declare @databasename varchar(200)
  declare @backuppath varchar(500)
  declare @filename varchar(500)
  declare @backupstring varchar(1000)
  declare @failcount int
  declare @failflag int
  
  create table #resultstring (message varchar (2500))
  create table #jobresults (message varchar(2500))
  
  set @failflag = 0
  set @backuppath = 'E:\DBBACKUP\SQLBackup\'
  
  declare dbs cursor for
  select NAME from sysdatabases
  where name != 'tempdb'
  order by dbid
  open dbs
  
  fetch dbs into @databasename
  
  @fetch_status != -1
  begin
  SELECT @recoverymodel = CAST(DATABASEPROPERTYEX(@databasename, 'RECOVERY') AS VARCHAR(12))
  IF @recoverymodel <> 'SIMPLE'
  BEGIN
  set @datestamp = left(replace(replace(replace(convert(varchar(30), getdate(), 120), '-', ''), ' ', ''), ':', ''), 12)
  set @filename = @backuppath + @databasename + '_db_(full)_' + @datestamp + '.sqb'
  set @backupstring = '-SQL "BACKUP DATABASE TO DISK = ''' + @filename + ''' WITH NAME = ''Database (' + @databasename + ') Full'', ERASEFILES_ATSTART = 1, COMPRESSION = 1" -E'
  
  insert into #resultstring exec master..sqlbackup @backupstring
  
  select @failcount = count(*) from #resultstring where patindex('%error%', message) > 0
  if @failcount > 0
  begin
  insert into #jobresults (message) select message from #resultstring
  set @failflag = 1
  end
  else begin
  insert into #jobresults (message) values ('Backup succeeded: ' + @databasename)
  end
  
  delete from #resultstring
  END
  
  fetch dbs into @databasename
  end
  
  close dbs
  deallocate dbs
  
  select * from #jobresults
  drop table #resultstring
  drop table #jobresults
  
  if @failflag = 1
  begin
  RAISERROR ('Backup failed to complete successfully for all databases.', 16, 1)
  end
  
  set nocount off

  petey May 13, 2005 12:30

  - 0 +
• 
  Script provided fails to backup. In Q-Analyzer It completes quickly with only a display of databases where recovery = full. Is it possible that the first db selected has recovery to simple so it fails there?
  
  Thanks in advance ~Chris

  stratburst May 13, 2005 13:59

  - 0 +
• 
  Yes, there was an error in the original script, but I modified it a couple of minutes later so it should work now.

  petey May 13, 2005 21:20

  - 0 +
• 
  There is an undocumented stored procedure in the master database of SQL Server that you could also use called sp_MSForEachDb. This works similar to a DOS for command to execute the same SQL against all of the databases on the system:

  EXEC master..sp_MSforeachdb "master..sqlbackup '-SQL ""BACKUP DATABASE [?] TO DISK=''<AUTO>''""'"
  

  Brian Donahue May 24, 2005 07:25

  - 0 +
• 
  How can I add the encryption password to this script?
  
  Thanks

  pchee373 May 26, 2005 14:13

  - 0 +
• 
  When you want to add an encrypted password you will need to add the option
  PASSWORD = 'password'

  Daniel Handley May 27, 2005 05:05

  - 0 +

Add comment

Please sign in to leave a comment.