2

I want to create a scheduled task that will run (every night) a SQL script to fully backup my database X to a file. I want that file to store up to 7 full backups. The way I tried doing this is using the following code:

SELECT @SQL = '
BACKUP DATABASE [X] 
TO  DISK = N''\file.bak'' 
WITH NOFORMAT, NOINIT, NAME = N''name'', RETAINDAYS = 7
'

But this doesn't overwrite/delete full backups contained on that file that are older than seven days.

Any ideas? Suggestions for alternatives?

Thanks

Daniel Sánchez Vargas
  • 21
  • 1
  • 2

2 Answers2

4

I would recommend you to study and implement Ola Hallengren's SQL Server Backup solution. Its free and is widely tested and recognized in the community.

e.g :

Back up all user databases, using checksums and compression; verify the backup; and delete old backup files

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y',
@CleanupTime = 24

Automate and Improve Your Database Maintenance Using Ola Hallengren's Free Script will give you a good start.

Kin Shah
  • 62,545
  • 6
  • 124
  • 245
0

The RETAINDAYS option maps to the Backup set will expire in x days option in SSMS. See my previous answer on this here

Community
  • 1
James Anderson
  • 5,794
  • 2
  • 27
  • 43