MSSQL Backup Strategy on a Windows VPS: Full, Differential, and Log Backups

In short

A production MSSQL backup strategy on a Windows VPS should include full backups, differential backups, transaction log backups for databases in FULL recovery model, off-server copies, retention rules, and restore testing. For many SMB workloads, a practical starting point is daily FULL backups, DIFFERENTIAL backups every few hours, and LOG backups every 15 minutes when point-in-time recovery matters. The most important rule: an untested backup is not a real backup.

Quick verdict

The goal is not just to create .bak files. The goal is to know how much data you can lose, how fast you can recover, and whether the backup actually restores.

What we tested on Raff

We tested the backup workflow on a Raff Windows VPS running Windows Server 2025 Datacenter Evaluation and SQL Server 2025 Express.

Test environment:

We verified:

• SQL Server 2025 Express installation
• SQL Server service status
• sqlcmd command-line access
• SQL Server version query
• Sample database creation
• Recovery model check
• FULL recovery model configuration
• Backup folder creation
• FULL backup
• DIFFERENTIAL backup
• LOG backup
• Backup files written to disk
• RESTORE VERIFYONLY backup validation

We tested with SQL Server Express, so some production features differ. SQL Server Agent is not included in Express, and backup compression was not supported in our Express test. For Standard or Enterprise editions, SQL Server Agent and backup compression may be available depending on edition and configuration.

Why backup strategy matters

A SQL Server database can fail in several ways:

• Accidental delete
• Bad application update
• Corrupt data
• Ransomware
• Disk issue
• Failed migration
• Human error
• Broken Windows Update or software change
• VPS-level incident
• Application bug writing bad data

A local backup protects against some database-level mistakes. It does not protect against every risk.

A proper strategy needs:

1. Backups created on schedule
2. Backups copied away from the server
3. Backups retained for the right amount of time
4. Restore tests
5. Clear RTO and RPO targets

RTO and RPO: decide before designing backups

Before choosing a schedule, define two things.

If your business can only lose 15 minutes of transactions, you need transaction log backups around every 15 minutes.

If your business can tolerate losing one day of changes, a daily full backup may be enough.

Do not design backup schedules blindly. Start with the business risk.

The practical 3-3-2 backup rule

For many SMB SQL Server workloads, use this pattern:

A practical example:

This is a starting point. Adjust it based on your RTO, RPO, database size, and workload.

Recovery models: choose first

SQL Server recovery model controls whether transaction log backups are meaningful.

Check current recovery models:

SELECT name, recovery_model_desc
FROM sys.databases;

In our test, we created a sample database and checked its recovery model.

For point-in-time recovery, set the database to FULL recovery model:

ALTER DATABASE RaffBackupTest SET RECOVERY FULL;

Then confirm:

SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = 'RaffBackupTest';

Important: after switching to FULL recovery model, take a FULL backup to start the log backup chain.

SQL Server Express vs Standard / Enterprise

SQL Server Express is useful for small workloads and testing, but it has limitations that affect backup automation.

For SQL Server Express, use Windows Task Scheduler with sqlcmd or another automation tool.

For SQL Server Standard or Enterprise, use SQL Server Agent jobs.

Step 1 - Confirm SQL Server is installed

First, check whether a normal SQL Server instance is installed.

Run PowerShell as Administrator:

Get-Service | Where-Object {$_.Name -like "MSSQL*"} | Select-Object Name, Status, DisplayName

After installing SQL Server 2025 Express, our test server showed the SQLEXPRESS instance running.

We also confirmed that sqlcmd was installed:

sqlcmd -?

If sqlcmd is not recognized, install the SQL Server command-line tools or use SSMS for manual testing.

Step 2 - Confirm SQL Server version

Check the SQL Server version:

sqlcmd -S localhost\SQLEXPRESS -E -C -Q "SELECT @@VERSION AS SQLVersion;"

The -C flag tells sqlcmd to trust the server certificate. With newer ODBC drivers, encrypted connections are common by default, and local test instances may use certificates that are not trusted by the client.

In our test, SQL Server 2025 Express was installed successfully.

Step 3 - Create the backup directory

Create a local backup directory:

New-Item -ItemType Directory -Path 'C:\SQL\Backups' -Force

Grant SQL Server Express access to the folder:

icacls 'C:\SQL\Backups' /grant 'NT SERVICE\MSSQL$SQLEXPRESS:(OI)(CI)F'

Verify the folder exists:

Get-ChildItem C:\SQL

For production, do not rely only on the system drive. If possible, use a separate data or backup volume, then copy backups off-server.

Step 4 - Take a FULL backup

A FULL backup is the foundation of the restore chain.

For SQL Server Express, we used this command without COMPRESSION because Express did not support backup compression in our test:

sqlcmd -S localhost\SQLEXPRESS -E -C -Q "BACKUP DATABASE [RaffBackupTest] TO DISK = N'C:\SQL\Backups\RaffBackupTest_FULL.bak' WITH INIT, CHECKSUM, STATS = 10;"

For editions that support backup compression, you can use:

BACKUP DATABASE [YourDB]
TO DISK = N'C:\SQL\Backups\YourDB_FULL.bak'
WITH INIT, COMPRESSION, CHECKSUM, STATS = 10;

Use CHECKSUM whenever possible. It helps detect backup corruption during the backup process.

Use INIT when you want to overwrite the existing backup file instead of appending to it.

For production, prefer timestamped file names instead of overwriting the same file every day.

Step 5 - Take a DIFFERENTIAL backup

A DIFFERENTIAL backup captures changes since the most recent FULL backup.

Run:

sqlcmd -S localhost\SQLEXPRESS -E -C -Q "BACKUP DATABASE [RaffBackupTest] TO DISK = N'C:\SQL\Backups\RaffBackupTest_DIFF.bak' WITH DIFFERENTIAL, INIT, CHECKSUM, STATS = 10;"

Differential backups reduce restore complexity.

Without differentials, a restore may require yesterday's full backup plus many transaction log files. With differentials, you restore the latest full backup, the latest differential, then only the log files after that differential.

Step 6 - Take a LOG backup

For a database in FULL recovery model, transaction log backups are required for point-in-time recovery.

Run:

sqlcmd -S localhost\SQLEXPRESS -E -C -Q "BACKUP LOG [RaffBackupTest] TO DISK = N'C:\SQL\Backups\RaffBackupTest_LOG.trn' WITH INIT, CHECKSUM, STATS = 10;"

If you use FULL recovery model but never take log backups, the transaction log can keep growing until it fills the disk.

That is one of the most common SQL Server backup mistakes.

Step 7 - Confirm backup files exist

After running FULL, DIFFERENTIAL, and LOG backups, check the backup folder:

Get-ChildItem 'C:\SQL\Backups' | Select-Object Name, Length, LastWriteTime

You should see:

RaffBackupTest_FULL.bak
RaffBackupTest_DIFF.bak
RaffBackupTest_LOG.trn

The exact file sizes and timestamps will differ.

What matters:

Step 8 - Verify the backup file

A backup file existing on disk is not enough. Verify that SQL Server can read it.

Run:

sqlcmd -S localhost\SQLEXPRESS -E -C -Q "RESTORE VERIFYONLY FROM DISK = N'C:\SQL\Backups\RaffBackupTest_FULL.bak' WITH CHECKSUM;"

Expected result:

The backup set on file 1 is valid.

RESTORE VERIFYONLY does not replace a real restore drill, but it is a useful first check.

For production, perform periodic restore tests to a separate database or separate test server.

Recommended production schedule

For a production database in FULL recovery model, use this as a starting point:

Adjust the schedule for your workload.

If the database changes rarely, you may not need aggressive log backups.

If the database handles orders, accounting, payments, or customer operations, 15-minute log backups may be appropriate.

Use timestamped backup files in production

Our test used fixed file names for clarity:

RaffBackupTest_FULL.bak
RaffBackupTest_DIFF.bak
RaffBackupTest_LOG.trn

For production, use timestamped file names.

Example naming pattern:

YourDB_FULL_20260526_020000.bak
YourDB_DIFF_20260526_100000.bak
YourDB_LOG_20260526_101500.trn

This prevents accidental overwrite and makes restore sequencing easier.

Automating backups on SQL Server Express

SQL Server Express does not include SQL Server Agent.

Use Windows Task Scheduler with sqlcmd.

Example full backup command:

sqlcmd -S localhost\SQLEXPRESS -E -C -Q "BACKUP DATABASE [YourDB] TO DISK = N'C:\SQL\Backups\YourDB_FULL.bak' WITH INIT, CHECKSUM, STATS = 10;"

Create separate scheduled tasks for:

• FULL backup
• DIFFERENTIAL backup
• LOG backup
• Off-server copy
• Cleanup

For SQL Server Standard or Enterprise, use SQL Server Agent jobs instead.

Automating backups on SQL Server Standard or Enterprise

For paid SQL Server editions with SQL Server Agent:

1. Open SQL Server Management Studio.
2. Expand SQL Server Agent.
3. Create a new Job.
4. Add a T-SQL backup step.
5. Add a schedule.
6. Configure notifications.
7. Test the job manually.
8. Review job history.

Use separate jobs or separate job steps for FULL, DIFFERENTIAL, and LOG backups.

Off-server backup is not optional

Local backups are useful, but they are not enough.

Local backups do not protect you from:

• VPS deletion
• Compromised administrator account
• Ransomware deleting backup files
• Disk failure
• Accidental cleanup
• Region/provider-level incident

Copy backups to another location.

Options include:

• Object storage such as S3-compatible storage
• Backblaze B2
• Cloudflare R2
• Another backup server
• Managed backup storage
• Separate Raff storage option, if available for your plan

A simple pattern:

rclone copy 'C:\SQL\Backups\' 'remote:sql-backups/' --transfers 4

Schedule the off-server copy after each backup window.

Retention policy

Do not keep backups forever on the VPS. You will fill the disk.

A practical SMB retention policy:

Retention depends on compliance, storage cost, and business need.

For production, document the retention policy and verify cleanup does not delete files needed for restore.

Restore sequence

A typical restore sequence for FULL recovery model:

1. Restore the most recent FULL backup with NORECOVERY.
2. Restore the most recent DIFFERENTIAL backup with NORECOVERY.
3. Restore LOG backups in order with NORECOVERY.
4. Restore the final LOG with RECOVERY, or run RESTORE DATABASE ... WITH RECOVERY.

Example:

RESTORE DATABASE [YourDB_RestoreTest]
FROM DISK = 'D:\BackupCopy\YourDB_FULL.bak'
WITH NORECOVERY, REPLACE;

RESTORE DATABASE [YourDB_RestoreTest]
FROM DISK = 'D:\BackupCopy\YourDB_DIFF.bak'
WITH NORECOVERY;

RESTORE LOG [YourDB_RestoreTest]
FROM DISK = 'D:\BackupCopy\YourDB_LOG_001.trn'
WITH NORECOVERY;

RESTORE DATABASE [YourDB_RestoreTest] WITH RECOVERY;

After restore, run:

DBCC CHECKDB('YourDB_RestoreTest') WITH NO_INFOMSGS;

Document how long the restore took. That is your real RTO.

Common mistakes

No off-server backup

If backups only live on the same VPS, they can disappear with the server. Copy them somewhere else.

FULL recovery model without LOG backups

This causes transaction log growth and can eventually fill the disk. If you use FULL recovery model, take log backups.

Never testing restores

A backup strategy is incomplete until you prove restore works.

Backing up to the same disk as the database

It is better than nothing, but not enough. Use a separate volume or off-server location where possible.

Assuming SQL Server Express has SQL Server Agent

It does not. Use Task Scheduler and sqlcmd for Express.

Copying backup scripts without checking edition support

In our SQL Server 2025 Express test, BACKUP ... WITH COMPRESSION was not supported. Use compression only on SQL Server editions that support it.

Overwriting the same file forever

Fixed file names are fine for a lab. Production should use timestamped files and retention rules.

What Raff recommends

For production SQL Server workloads on a Windows VPS, Raff recommends:

1. Choose the right recovery model.
2. Use FULL recovery for business-critical databases.
3. Take regular FULL, DIFFERENTIAL, and LOG backups.
4. Copy backups off-server.
5. Use CHECKSUM.
6. Verify backups with RESTORE VERIFYONLY.
7. Run real restore drills.
8. Monitor disk usage.
9. Document RTO and RPO.
10. Review the backup schedule after application or workload changes.

A backup is only useful if you can restore it in time.

Tested on

Tested on Raff Windows VPS, Windows Server 2025 Datacenter Evaluation, SQL Server 2025 Express, SQLEXPRESS instance, SQL Server version 17.0.1000.7 RTM, 2026-05-26. We installed SQL Server Express, verified the SQL service and sqlcmd, created the RaffBackupTest database, set FULL recovery model, created FULL, DIFFERENTIAL, and LOG backups, confirmed backup files on disk, and validated the full backup with RESTORE VERIFYONLY. Tester: Serdar Tekin.

What's next

• Install MSSQL Server 2025 on a Windows VPS - install and prepare SQL Server on a Raff Windows VPS
• Windows Update Strategy on Production Servers - patch Windows Server safely with snapshots and maintenance windows
• Configure Windows Firewall on a Windows VPS - review inbound rules for SQL Server, RDP, and IIS
• Windows Server Hardening Checklist - secure a Windows Server VPS before production
• Raff Windows VPS - deploy a Windows Server VPS for SQL Server, IIS, business apps, and remote administration

Sources

• Microsoft Learn - Back up and restore of SQL Server databases
• Microsoft Learn - Recovery models
• Microsoft Learn - BACKUP Transact-SQL
• Microsoft Learn - RESTORE VERIFYONLY Transact-SQL
• Microsoft Learn - SQL Server editions and supported features
• Date last verified: 2026-05-26