Inherited a small SQL Server 2017 instance that has apparently never been backed up. I need a full backup now and then an automatic schedule. Standard edition, so I believe Agent is available.
How to create and schedule a full database backup in SQL Server?
Solved SQL & Databases
RK
Rachel Kim January 9, 2020
2 replies
6,170 views
Reviewed by moderators
Immediate backup: BACKUP DATABASE yourdb TO DISK='D:\Backups\yourdb_full.bak' WITH CHECKSUM, COMPRESSION;
1
Schedule: SQL Server Agent, new job, that command as a T-SQL step, nightly schedule. Add date to the filename with a variable so files do not overwrite.
2
If the database is in full recovery, add log backups every 15 to 30 minutes as a second job or the log will grow forever.
3
Test a restore to a scratch database once. An untested backup is a hope, not a backup.
Nightly full plus 30 minute log backups running and I restored a test copy successfully. Solved.