AUTO SCHEDULE DATABASE BACKUP BY CREATING AGENT JOB
13How to schedule a SQL Server backup,
SQL Agent Jobs: Creating a Scheduled Database Backup Plan
How to schedule a SQL Server backup
Having a good backup and restore plan is an important part of a disaster recovery strategy. This article will describe 3 different solutions/approaches for creating a scheduled backup in SQL Server
As a part of a backup strategy several types of backup can be used together.
Backup types
Full database backups include all database objects, system tables, data, and transactions that occur during the backup. Full database backups allow performing a complete restoration to a state before the backup is performed.
Differential backups contain data that has changed since the last full backup was performed and transactions that occur during the backup process. A differential backup is used alongside with the last full database backup. After taking a differential backup, as it is taken after the last full backup all of the old differential backups become obsolete.
Transaction log backups records all the transactions that have occurred on a database since the previous transaction log backup and then truncates the transaction log and then truncates a transaction log. A transaction log backup ensures database recovery to a specific point of time e.g. to a moment prior to data loss.
File and filegroup backups option is most suitable for backing up very large databases. A file backup will contain all the data in one or more files or filegroups. A transaction log backup has also be performed to span all the file backups from start to finish when using file backups to restore a database.
Copy-only backups are mostly used when it’s needed to backup a database without affecting the processes of backup and restore for a specific database. Functionality of a copy-only backup is the same as full database with a difference that a transaction log backup will backup all transactions since the last full backup is performed and ignore the existence of the copy backup, therefore a copy backup cannot be used as the basis for differential and transaction log backups.
Recommended backup strategy practices
Backup location
It is advisable that backups are not stored on the same location (physical drive) where database files are stored. In cases when a physical drive fails use the drive or a network location to perform restore. If a file location is not specified when creating a database SQL Server will store database files on the default database locations.
Note that changing the default locations won’t move the current data and log files to a new location. This will only be applicable to databases created after this change.
Scheduled and automated backups
To prevent and backup safety and reliability is to set up (automate) the backup process through the means of schedules. Creating backup schedules is important as the time passes current backups get obsolete and out of time.
Keep yourself protected and be sure that you always have at hand a way to reestablish your data up until the point where the database failed. Scheduled backups provide an accurate data history.
The specified frequency of a backup depends on the company business needs etc., and is defined by Recovery Point Objective (RPO). For example, if an organization’s Service Level Agreement (SLA) specifies that no more than an hour’s data can be lost from a database, the RPO is one hour.
Test backups
Backup and recovery strategy cannot be complete until backups are successfully restored on a test server and verified that backup can be restored to fulfill all the requirements and conditions including all the combinations that the recovery strategy requires. There is a variety of factors to consider such as: the organizations requirements regarding the usage of data, protection etc.
Backup verification
Verifying backup ensures that a backup is created correctly, intact physically, that all the files in the backup are readable and can be restored in the event that the user needs to use it, and that all the transactions are consistent. It is important to understand that verifying a backup does not verify the structure of the data on the backup. However, if the backup was created using WITH CHECKSUMS, verifying the backup using WITH CHECKSUMS can provide a good indication of the reliability of the data on the backup.
USE DILIP
GO
BACKUP DATABASE DILIP
TO DISK = N'F:\Backup\+GETDATE()+.bak'
WITH CHECKSUM;