powershelldba.de · Uwe Janke

Yearly Archive Backups with SQL Server Agent

How to set up a year-end archive backup as its own SQL Server Agent job: COPY_ONLY full backups, a separate target and retention, a yearly schedule, and a real test restore.

The Short Version

Your regular backup plan answers one question: how do I get back to yesterday? A year-end archive answers a different one: what did the data look like on 31 December, and can I prove it in ten years? Those two goals should not share a job, a folder or a retention rule. The clean way to do it is a separate SQL Server Agent job that takes a COPY_ONLY full backup to its own target and is never touched by your normal cleanup.

1. Use COPY_ONLY

A copy-only full backup does not reset the differential base and does not affect the log chain. Your daily differentials and log restores keep working exactly as before, even though a second full backup exists. On an Availability Group secondary, copy-only is the only full backup SQL Server allows, so the same job works on any replica.

2. Give It Its Own Target and Retention

The most common mistake is writing the archive into the normal backup folder. The next cleanup run then deletes it after a few days like any other file. Use a separate path, put the year in the folder name, and do not set a cleanup time in the job at all. Retention for the archive is handled by the storage side: a retention policy on the share, an immutable (WORM) target, or the archive system itself.

3. The Job Step: Plain T-SQL

Create one Agent job with one step of type Transact-SQL script. A PowerShell step works too, but a T-SQL step has fewer moving parts and is much less likely to hang. With Ola Hallengren's Maintenance Solution the step looks like this:

DECLARE @Structure nvarchar(max) =
    N'{ServerName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}EndOfYear_'
    + CONVERT(nchar(4), YEAR(GETDATE()));

EXECUTE dbo.DatabaseBackup
    @Databases          = 'ALL_DATABASES',
    @Directory          = '\\fileserver\EndOfYear',
    @BackupType         = 'FULL',
    @CopyOnly           = 'Y',
    @Compress           = 'Y',
    @Checksum           = 'Y',
    @Verify             = 'Y',
    @CleanupTime        = NULL,
    @DirectoryStructure = @Structure,
    @LogToTable         = 'Y';

ALL_DATABASES includes master, msdb and model. That is intended: for an archive you want the whole instance. If your regular plan excludes certain databases, decide consciously whether the archive should include them too. Use AVAILABILITY_GROUP_DATABASES instead if the job should respect the preferred replica of an Availability Group.

4. The Schedule

The Agent has no "yearly" schedule type. Two practical options:

Pick a time after the year-end processing of your application has finished, and not at the same time as the regular full backup.

5. Job Settings That Matter

6. Do Not Forget the Things Outside the Database

A backup file alone is not always enough to restore in five years. Save these next to the archive:

7. Test the Restore Once

RESTORE VERIFYONLY checks that the file is readable. It does not prove that the database comes back. After the first run, restore one representative database to a test instance, run DBCC CHECKDB on it and write down how long it took. An archive that has never been restored is a hope, not a backup.

Check your retention rules. For accounting and tax data, statutory retention periods apply, often ten years. Clarify with your compliance team which data the archive must contain and for how long, before you decide on the target and on the schedule.

The Bottom Line

A year-end archive is one extra Agent job: copy-only full backup, separate target, no cleanup, a monthly schedule that repeats every 12 months, and one real test restore. It costs an afternoon to set up and it does not interfere with your regular backup chain.