# SQL Server ERP Backup Script Generator: Full, Differential and Log Backups

> Source: https://netray.co/tools/sql-server-erp-backup-job-script-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Fill in the database name, backup folder and retention to get a T-SQL script with checksum, compression, verification and backup history cleanup.

## Category

developer

## Audience

- IT manager
- DBA
- ERP administrator
- Infrastructure engineer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Database name

**Key:** db

**Type:** text

**Default:** ERP_PROD

#### Backup folder (local or UNC)

**Key:** folder

**Type:** text

**Default:** D:\Backups\ERP_PROD

#### Compression

**Key:** compress

**Type:** select

**Options:**

- COMPRESSION
- NO_COMPRESSION

**Default:** COMPRESSION

#### History retention (days)

**Key:** retention

**Type:** number

**Default:** 35

#### Progress step (percent)

**Key:** stats

**Type:** number

**Default:** 10

**Blocks:**

#### Full backup with checksum

**Body:** DECLARE @stamp varchar(20) = REPLACE(REPLACE(REPLACE(CONVERT(varchar(19), GETDATE(), 120), '-', ''), ':', ''), ' ', '_');
DECLARE @file nvarchar(400) = N'{{folder}}\{{db}}_FULL_' + @stamp + N'.bak';

BACKUP DATABASE [{{db}}]
TO DISK = @file
WITH {{compress}}, CHECKSUM, INIT, FORMAT, STATS = {{stats}},
     NAME = N'{{db}} full backup';

RESTORE VERIFYONLY FROM DISK = @file WITH CHECKSUM;

#### Differential backup (run between fulls)

**Body:** DECLARE @stamp varchar(20) = REPLACE(REPLACE(REPLACE(CONVERT(varchar(19), GETDATE(), 120), '-', ''), ':', ''), ' ', '_');
DECLARE @file nvarchar(400) = N'{{folder}}\{{db}}_DIFF_' + @stamp + N'.bak';

BACKUP DATABASE [{{db}}]
TO DISK = @file
WITH DIFFERENTIAL, {{compress}}, CHECKSUM, INIT, STATS = {{stats}},
     NAME = N'{{db}} differential backup';

#### Transaction log backup (FULL recovery model only)

**Body:** DECLARE @stamp varchar(20) = REPLACE(REPLACE(REPLACE(CONVERT(varchar(19), GETDATE(), 120), '-', ''), ':', ''), ' ', '_');
DECLARE @file nvarchar(400) = N'{{folder}}\{{db}}_LOG_' + @stamp + N'.trn';

BACKUP LOG [{{db}}]
TO DISK = @file
WITH {{compress}}, CHECKSUM, INIT, STATS = {{stats}},
     NAME = N'{{db}} log backup';

#### Check recovery model and last backups

**Body:** SELECT d.name, d.recovery_model_desc,
       MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full,
       MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS last_diff,
       MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b ON b.database_name = d.name
WHERE d.name = N'{{db}}'
GROUP BY d.name, d.recovery_model_desc;

#### Clean up backup history older than retention

**Body:** DECLARE @cutoff datetime = DATEADD(DAY, -{{retention}}, GETDATE());
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @cutoff;

-- Backup files themselves are not removed by this call.
-- Delete old .bak and .trn files with a file system job or your backup tool.

**Notes:** Test the script on a non-production copy first and restore a backup to a scratch server to prove it works. COMPRESSION is not available on every edition of older SQL Server versions, so switch to NO_COMPRESSION if the statement fails. Log backups only apply when the database uses the FULL or BULK_LOGGED recovery model. Follow your ERP vendor's guidance for any additional databases it uses.

## Content

**Intro:** Most ERP outages that become data loss events trace back to a backup job that was never restored, never verified or never covered the transaction log. This generator produces a clean set of T-SQL statements you can schedule in SQL Server Agent.

**Sections:**

#### How to use the generator

**Body:** Enter the database name and a backup folder that is not on the same disk as the data files. Copy the full, differential and log statements into separate SQL Server Agent jobs, or into your existing maintenance plan. A typical pattern is a weekly full, a daily differential and a log backup every 15 minutes for databases in the FULL recovery model. Align that frequency with the recovery point objective the business has agreed to.

**Bullets:**

- Keep backups on separate storage from the database files
- Copy at least one set off site or to immutable storage
- Run the verification step and also test a real restore
- Alert on job failure and on a missing log backup

#### What each option does

**Body:** CHECKSUM makes SQL Server calculate and store page checksums so damaged pages are detected at backup and at restore. INIT overwrites the media set so you do not accumulate unlimited backups in one file. COMPRESSION reduces file size and transfer time and usually shortens the backup, at the cost of some CPU. RESTORE VERIFYONLY reads the backup and checks that it is complete and readable, but it does not prove that the data will restore correctly into a working ERP.

#### Retention and the log chain

**Body:** A differential backup depends on its base full backup, and a log backup depends on the chain before it. If you delete a full backup that a differential needs, the differential is useless. Plan retention in sets: keep the full, its differentials and the logs for the same period. The history cleanup in the script only trims the msdb records, so remove files separately with a scheduled job or the tool that manages the folder.

#### Common mistakes

**Body:** Teams often leave a database in the FULL recovery model without log backups, so the log file grows until the disk fills. Others back up to the same volume as the data, or never restore a backup until an emergency. Third party ERP systems can also use several databases, such as a separate application or reporting database, and the backup must cover all of them together.

**Cta:** Netray can help automate backup validation and restore drills for your ERP databases, including checks that the restored data answers real business queries.

**Faq:**

#### How often should I back up an ERP database?

**Answer:** It depends on the recovery point objective. A common pattern is weekly full, daily differential and log backups every 5 to 30 minutes in the FULL recovery model. Choose the log interval from how much data entry the business can afford to redo.

#### Does RESTORE VERIFYONLY prove my backup is good?

**Answer:** It confirms the backup is readable and complete, and with CHECKSUM it validates page checksums. It does not test that the database restores and the ERP application works. Run a real restore to a test server on a schedule.

#### Why is my transaction log growing?

**Answer:** In the FULL recovery model the log is only truncated after a log backup. If no log backups run, the file keeps growing. Add log backups or move a non critical database to the SIMPLE recovery model.

#### Can I use this for a SyteLine or Epicor database?

**Answer:** Yes, the statements are standard SQL Server. Use the actual database names from your install, and follow the vendor's guidance for any additional databases and for quiescing the application before a restore.

## Search terms

sql server backup script, t-sql full backup compression checksum, sql server differential backup script, erp database backup script, restore verifyonly, sql server transaction log backup, syteline sql backup, epicor sql server backup

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json