Backups to different folders based on week day

• marko.manchev

  SSC Enthusiast

  Points: 175

  More actions

  October 25, 2014 at 3:09 am

  #291855

  Hello,

  I am trying to create a job that would backup a DB dynamically to a different folder based on the week day. So if it's Saturday, backup to folder Saturday.

  I have this

  DECLARE @BackupLoc nvarchar (100)

  DECLARE @DayOfWeek nvarchar (100)

  set @BackupLoc = N'D:\Backup\'

  set @DayOfWeek = (SELECT DATENAME(dw,GETDATE()))

  set @BackupLoc = @backuploc + @DayOfWeek

  BACKUP DATABASE [Test] TO DISK = @BackupLoc WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = N'Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10

  GO

  declare @backupSetId as int

  select @backupSetId = position from msdb..backupset where database_name=N'Test' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'Test' )

  if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''Test'' not found.', 16, 1) end

  --RESTORE VERIFYONLY FROM DISK = @BackupLoc; WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

  But it's giving me permission errors, although the AGENT and MSSQL service accounts are members of the local admins group.

  Any chance of this happening or maybe of a more elegant way of doing it?

  Thanks upfront.

• durai nagarajan

  SSCoach

  Points: 15775

  More actions

  October 27, 2014 at 12:04 am

  #1754837

  TO DISK = @BackupLoc

  your @BackupLoc should return path with file name like "D:\Backup\Saturday\test.bak".

  hope this is the error.

  Regards
  Durai Nagarajan

• Jack Corbett

  SSC Guru

  Points: 184393

  More actions

  October 27, 2014 at 9:36 am

  #1754976

  durai nagarajan (10/27/2014)

  ---

  TO DISK = @BackupLoc

  your @BackupLoc should return path with file name like "D:\Backup\Saturday\test.bak".

  hope this is the error.

  The problem is that you don't have "\test.bak" in @BackupLoc and it is trying to create a file called Saturday (in this example) without an extension, but it can't because the name Saturday is already in use by the folder.

  So changing set @BackupLoc = @backuploc + @DayOfWeek to set @BackupLoc = @backuploc + @DayOfWeek + '\test.bak' should solve the problem.

  I'm not sure why you are doing it the way you are, I'm sure you have a reason, but in most places I've worked you just backup to the same location and append the date to the backup file name like test_YYYYMMDD.bak.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question