Automated Daily Backup

• 
  You could create jobs for SQL Server Agent to back up each database. Set up the naming convention first, so that each backup file will have a unique name. Then, you can use the following syntax:
  
  master..sqlbackup '-sql "BACKUP DATABASE pubs TO DISK = [E:\Temp\Backups\<AUTO>]"'
  
  Or if you only use one backup folder, you can set up the default folder, and simply use
  
  master..sqlbackup '-sql "BACKUP DATABASE pubs TO DISK = [<AUTO>]"'
  
  In the next version, you need not set up separate jobs for each database, nor be limited to a single folder as we'll be enhancing the syntax a little to allow you to do things like this:
  
  master..sqlbackup '-sql "BACKUP DATABASES [pubs, northwind, master] TO DISK = [F:\Backups\<DATABASE>\<AUTO>]"'
  
  or this (the inverse)
  
  master..sqlbackup '-sql "BACKUP DATABASES EXCEPT [tempdb, msdb] TO DISK = [F:\Backups\<DATABASE>\<AUTO>]"'

  petey October 06, 2005 22:14

  - 0 +
• 
  Thanks. Is there any way to have it automatically remove any files that are before a certain date?

  lionheart October 10, 2005 12:53

  - 0 +
• 
  Nevermind, I think I found it.

  lionheart October 10, 2005 12:57

  - 0 +
• 
  I've been using SQLBackup since June 2005, and I LOVE IT. It's truly a great product.
  
  I'm very anxious for the new version, as it's been talked about since I started using the product. In particular the automated script abilities mentioned above.
  
  Is there any news on the new version? I'm on 3.2.0, is that the most up-to-date? Is there a place I can check for more current versions?
  
  Thank you for the great product and support.

  scottcate November 10, 2005 12:42

  - 0 +
• 
  Hello,
  
  The UI comes with a check for updates item in the Help menu, like all of Red Gate's products. You can always use this to locate the most current version of the software.
  
  The next version will be released in a few months and contain wizards for log shipping and scheduling backup jobs. The backup scheduling wizard is going to be able to backup a selection of databases or even all of the databases on a server automatically!

  Brian Donahue November 11, 2005 12:05

  - 0 +
• 
  This response is to lionheart where you asked about detecting files of a certain age and deleting them. Very easy thing to do (see code below) - sorry about the format but you get the idea. Key is to use xp_getfiledetails Hope it helps Rich:
  
  INSERT #tmpFileDetails
  EXEC master..xp_getfiledetails @vchrBackupLocation
  
  -- Determine whether the file is too old to retain
  IF EXISTS
  (
  SELECT *
  FROM #tmpFileDetails
  WHERE
  DATEDIFF(
  day
  ,CASE WHEN LEN(LastWrittenTime) = 6 THEN
  CONVERT(DATETIME,LastWrittenDate + ' '
  + LEFT(LastWrittenTime,2) + ':'
  + LEFT(RIGHT(LastWrittenTime,4),2) + ':'
  + RIGHT(LastWrittenTime,2))
  ELSE -- Take into account when the time is not in the desired time formate from xp_getfiledetails function
  CONVERT(DATETIME,LastWrittenDate + ' '
  + LEFT('000000',2) + ':'
  + LEFT(RIGHT('000000',4),2) + ':'
  + RIGHT('000000',2))
  END
  ,GETDATE()
  ) >= @intRetainDays
  )
  BEGIN
  SET @vchrCmdLine = 'del ' + QUOTENAME(@vchrBackupFolder + '\' + @vchrDatabaseName + '\' + @oFileName,'"')
  EXEC @intErrorCode = master..xp_cmdshell @vchrCmdLine
  END

  richarde November 15, 2005 10:00

  - 0 +
• 
  This is what I wrote to backup all databases on a daily basis. It's a single stored procedure that you can schedule whenever. It will create a control table that lists the databases and defines whether they should be backed-up and for how long to retain previous backups.
  
  By default, backup = Y and retention period = 7 days. New databases are automatically added with these defaults (so you don't forget to add new databases to your backup schedule).
  
  The control table can then be manipulated manually, if you desire, to change the defaults. And, it could even be modified to include an encrpyt/decrypt value - the SP below automatically encrypts all databases for me.
  
  You'll have to generate your own password etc in the @... parameter at the end of the stored procedure, and modify the TO DISK string accordingly.
  
  Create the SP in the master database. The control table will also reside master.
  
  Not sure if this helps, but it shows what you can do ... or what SQLBackup 4.0 will possibly provide going forward
  

  ---

  --
  -- R.Grey
  -- 11-Nov-2005
  --
  -- Stored Procedure: myspEncBackupDaily
  --
  -- Procedure to be scheduled via SQL Server Agent as often as required
  -- to backup and encrypt all databases using SQL Backup 3.2.0
  --
  -- Backup files are retained for the period specified in the
  -- myspEncBackupDaily table (default 7 days)
  --

  ---

  --
  -- Backup file format
  --
  -- <dbname>_yyyymmdd_hhmmss.sqb
  --
  -- Usage:
  --
  -- exec myspEncBackup 'D:\SQLBackup\'
  --
  
  CREATE Procedure myspEncBackupDaily
  @Path varchar(128)
  as
  -- create _myspEncBackupDaily table
  if not exists (select * from dbo.sysobjects where id = object_id(N'[master].[dbo].[_myspEncBackupDaily]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  begin
  Create table [master].[dbo].[_myspEncBackupDaily]
  (
  Name varchar(128) primary key nonclustered ,
  BackupFlag varchar(1) not null check (BackupFlag in ('Y','N')) ,
  RetentionPeriod int not null
  )
  end
  
  set nocount on
  declare @... varchar(1000)
  
  -- Get all database names
  create table #DBName
  (
  ID int identity (1,1) ,
  Name varchar(128) not null ,
  RetentionPeriod int null
  )
  
  insert #DBName
  (Name)
  select name
  from master..sysdatabases
  
  -- Include any new databases in the backup
  insert _myspEncBackupDaily
  (
  Name ,
  BackupFlag ,
  RetentionPeriod
  )
  select #DBName.Name ,
  'Y' ,
  7 -- default 7 days
  from #DBName
  left outer join _myspEncBackupDaily
  on _myspEncBackupDaily.Name = #DBName.Name
  where _myspEncBackupDaily.Name is null
  and lower(#DBName.Name) not in ('tempdb','master','model','msdb')
  
  -- Remove any non-existant databases
  delete _myspEncBackupDaily
  where not exists
  (
  select *
  from #DBName
  where #DBName.Name = _myspEncBackupDaily.Name
  )
  
  delete #DBName
  
  -- loop through databases
  declare name varchar(128) ,
  @RetentionPeriod int ,
  @ID int ,
  @MaxID int
  
  insert #DBName
  (Name, RetentionPeriod)
  select Name, RetentionPeriod
  from _myspEncBackupDaily
  where BackupFlag = 'Y'
  
  select @MaxID = max(ID) ,
  @ID = 0
  from #DBName
  
  while @ID < @MaxID
  begin
  -- get next database to backup
  select @ID = min(ID) from #DBName where ID > @ID
  
  select name = Name ,
  @RetentionPeriod = RetentionPeriod
  from #DBName
  where ID = @ID
  
  declare @eID int ,
  @eMaxID int ,
  @eName varchar(128)
  
  -- now do the backup
  select @... =
  
  '-SQL "BACKUP DATABASE
  TO DISK = ''' + @Path + '\' + name + '_' + convert(varchar(10),getdate(),112) + '_' + replace(convert(varchar(10),getdate(),108), ':', '') + '.sqb''
  WITH NAME = ''' + name + ' ' + replace(convert(varchar(10),getdate(),6),' ','-') + ' ' + convert(varchar(8),getdate(),108) + ''',
  DESCRIPTION = ''Backup on ' + replace(convert(varchar(10),getdate(),6),' ','-') + ' ' + convert(varchar(8),getdate(),108) + ' Database: ' + name + ''',
  INIT,
  PASSWORD = ''<ENCRYPTEDPASSWORD>........</ENCRYPTEDPASSWORD>'',
  ERASEFILES = ' + convert(varchar(3),@RetentionPeriod) + ',
  COMPRESSION = 3" -E'
  
  exec master..sqlbackup @...
  
  end
  GO

  rgrey November 22, 2005 06:52

  - 0 +
• 
  Damn formatting ! The smilies at the end of the SP are actually an 8 followed by a )
  
  UPDATE: Aah ! Looks like Brian sorted it - my firefox (running with the MSIE user agent due to MS ISA Server rubbish) doesn't seem to like the WYSIWYG buttons for code block etc.
  
  :-(

  rgrey November 22, 2005 06:54

  - 0 +
• 
  Musn't forget that 'disable smilies' setting! Or you can enclose the whole shebang in a code block!

  Brian Donahue November 22, 2005 08:10

  - 0 +
• 
  rgrey,
  
  THANK YOU!!! Your post has caused me to get of my ??? and implement this strategy. Before today I was doing it manually daily.
  
  In my tests though, the remove portion isn't working.
  
  I have a value of 2, and it just keeps creating more and more files. If I run the backup sequentially, one right after the other, the file system just keeps building up. Is it because of the variable names changing with the date? Is it because the DateTime on the Files isn't > 2 days old?
  
  Can someone from Red-Gate look at the above script, and see why it's not removing the archives.
  
  Thank you!

  scottcate November 22, 2005 12:19

  - 0 +
• 
  Are you sure ?
  
  It's a day thing. If you've set the retention period to two days, you won't see anything being removed unless it is a backup from > 48 hours previously.
  
  And, considering I only posted this today, you can't have gone over two days yet
  
  My 7 day strategy is working good. The SP is scheduled to be run at 4am, 10am, 4pm and 10pm everyday. Looking at the directory right now (22 Nov 5.30pm), the earliest backup remaining is correctly (15 Nov 10pm) - this should be removed when the (22 Nov 10pm) backup is created as it will be greater than 7*24 hours old.

  rgrey November 22, 2005 12:27

  - 0 +
• 
  OK, I didn't know if the removal was by date, or by instance.
  
  Thank you.

  scottcate November 22, 2005 12:33

  - 0 +

Add comment

Please sign in to leave a comment.