Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Backup DB to separate folders depending on weekday

Author  Topic 

mariachi202
Starting Member

2 Posts

Posted - 2014-10-25 : 05:48:34
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



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.

sunder.bugatha
Yak Posting Veteran

66 Posts

Posted - 2014-10-31 : 03:16:09
did you make sure that SQL Agent account have sufficient permissions on the folder?

Hema Sunder
Go to Top of Page
   

- Advertisement -