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 thisDECLARE @BackupLoc nvarchar (100)DECLARE @DayOfWeek nvarchar (100)set @BackupLoc = N'D:\Backup\'set @DayOfWeek = (SELECT DATENAME(dw,GETDATE()))set @BackupLoc = @backuploc + @DayOfWeekBACKUP DATABASE [Test] TO DISK = @BackupLoc WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = N'Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10GOdeclare @backupSetId as intselect @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
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.
Backup DB to separate folders depending on weekday
Posted - 2014-10-25 : 05:48:34
