question

KranthiDBA-1529 avatar image
0 Votes"
KranthiDBA-1529 asked GuoxiongYuan-7218 commented

Script to backup database on the last day of the week and last day of the month

Hi All,

Could you please help provide script which backups up the databases like below.

  1. Daily full backup should happen to Daily backup folder.

  2. Last day of the week backup should happen Last day of Week folder.

  3. Last day of the month backup should happen to Last day of month folder.

If you could help in the logic that need to be used for above it would be helpful.

Thanks in Advance!






sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered GuoxiongYuan-7218 edited

Use the IF statement to check if the current date is the last day of the month or the last day of the week. Otherwise it is a normal day:

 -- Last day of the month
 IF CONVERT(date, GETDATE()) = EOMONTH(GETDATE())
 BEGIN
  BACKUP DATABASE TestDB TO DISK = 'C:\SQLServerBackups\Last_Day_Of_Month\TestDB_YYYYMMDD.bak'
     WITH FORMAT;
 END
 -- Last day of the week
 ELSE IF DATEPART(weekday, GETDATE())  % 7 = 0
 BEGIN
  BACKUP DATABASE TestDB TO DISK = 'C:\SQLServerBackups\Last_Day_Of_Week\TestDB_YYYYMMDD.bak'
     WITH FORMAT;
 END
 -- Daily
 ELSE
 BEGIN
  BACKUP DATABASE TestDB TO DISK = 'C:\SQLServerBackups\Daily_Backup\TestDB_YYYYMMDD.bak'
     WITH FORMAT;
 END
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered GuoxiongYuan-7218 commented

Hi,

The above answer provides good logic. But to verify whether the current date is Last day of the week (Sunday) may need to use the following statement:

  -- Last day of the week
  ELSE IF DATEPART(weekday, DATEDIFF(d,1,getdate())) % 7 = 0

And note that the EOMONTH function can be used in SQL Server 2012 and higher.

you can dynamically specify the backup path and file name. Please refer to the script in the following articles.
https://solutioncenter.apexsql.com/create-daily-database-backups-with-unique-names-in-sql-server/
https://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Criszhan for the correction!

0 Votes 0 ·

Thank you for the correction. I updated the answer.

0 Votes 0 ·