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:
- Monthly, every 12 months: schedule type Monthly, day 1, recurs every 12 month(s), starting on 1 January. The job runs in the night after year-end closing and repeats every year without maintenance.
- One-time schedule: simple and visible, but someone has to create the next one each year.
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
- Owner:
saor a dedicated service login, not a personal account that may be disabled by next December. - Retry attempts: 0. A half-finished archive should fail loudly, not retry quietly into the next day.
- Notification: operator e-mail on failure, and an output file for the step so the log is still there when you need it.
- Category: a name such as
EndOfYear, so the job is easy to find and easy to keep out of other jobs' filters.
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:
- TDE certificates and private keys, including their passwords, stored separately from the backups. Without the certificate a TDE backup cannot be restored.
- A script of logins with their SIDs and password hashes.
- A script of Agent jobs, linked servers and server-level settings.
- The SQL Server version and build, so you know what to restore onto.
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.