A disaster recovery approach must include a solid backup and restore plan. This piece will outline 3 distinct methods/solutions for setting up a scheduled backup in SQL Server.
A backup plan may include the use of multiple backup types.
Backup Types
1. During a full database backup, all database objects, system tables, data, and events are included. Complete restoration to a condition prior to the backup is possible with full database backups.
2. Differential backups include both transactions that take place during the backup process and data that has changed since the last complete backup. Along with the most recent complete database backup, a differential backup is used. A differential backup is made, and all previous differential backups become obsolete because it is done after the most recent full backup.
3. Transaction log backups keep track of all database operations that have taken place since the last backup of the transaction log before truncating the transaction log. A transaction log backup guarantees database recovery to a particular instant in time, such as just before data loss.
4. The file and filegroup backups choice is best suited for backing up extremely large databases. A file copy will include all the information in one or more files or filegroups. When using file backups to restore a database, a transaction log copy has also been made to span every file backup from beginning to end.
5. Copy-only backups are typically employed when it’s necessary to backup a database without altering the backup and restoration procedures for a particular database. A transaction log backup will backup all transactions since the last full backup was performed and ignore the existence of the copy backup, so a copy backup cannot be used as the basis for differential and transaction log backups. A copy-only backup performs the same functions as a full database backup with the exception that it will backup all transactions since the last full backup is performed.
Recommended backup strategy practices
Backup location
It is recommended that backups not be kept on the same physical disk as the database files. Use the drive or a network location to do restores when a physical drive dies. When establishing a database, if a file location is not given, SQL Server will store database files in the default database locations.
Please take note that altering the default locations will not relocate the existing data and log files. The only databases that will be affected by this are those made after the update.
Scheduled and automated backups
Set up (automate) the backup procedure using schedules in order to avoid and backup security and dependability. It’s critical to establish backup schedules because as time goes on, current backups become dated and ineffective.
Keep yourself safe and make sure you always have a solution to restore your data up until the database failure on hand. An accurate data history is provided by scheduled backups.
Recovery Point Goal, which is determined by business demands, among other factors, determines the required frequency of a backup (RPO). For instance, the RPO is one hour if an organization’s Service Level Agreement (SLA) stipulates that no more than an hour’s worth of data cannot be lost from a database.
Test backups
Prior to successfully restoring backups on a test server and confirming that backups can be restored to satisfy all circumstances and criteria, including all the combinations that the recovery strategy demands, the backup and recovery strategy cannot be considered complete. There are several things to take into account, including the organization’s needs for data consumption, protection, etc.
Backup verification
In order to use a backup effectively, it must be verified that it was produced correctly, that it is physically intact, that all of its files can be read and restored, and that all of its transactions are consistent. It’s crucial to realize that examining a backup does not examine the data’s organizational structure. But, if the backup was generated using WITH CHECKSUMS, then validating the backup using WITH CHECKSUMS can give you a decent idea of how reliable the data on the backup is.
By using T-SQL:
Including the CHECKSUM statement ensures consistency of data on the backup destination. To include CHECKSUM use the following query:
BACKUP DATABASE [AdventureWorks2012] TO DISK = N'F:\Backup\AW12.bak' WITH CHECKSUM;
SQL Server Management Studio also provides options to include backup verification a CHECKSUM check when creating a backup as a task:
The Verify backup, when finished option and Perform checksum before writing to media, are used as an insurance that both backup and its data are consistent.
We will also show how to include verifications when scheduling backups.
In this article, we will create a SQL Server scheduled backup by using a SQL Server Agent job and SQL Server Maintenance Plans.
How to Schedule SQL Backup Using SQL Server Agent
To automate and schedule a backup with SQL Server Agent:
1. In the Object Explorer pane, under the SQL Server Agent node, right click Jobs and select New job from the context menu:
2. In the New Job dialog enter a job’s name
3. Under the Steps tab click on the New button and create a backup step by inserting a T-SQL statement. In this case the CHECKSUM clause has to be included in T-SQL code:
USE AdventureWorks2012 GO BACKUP DATABASE [AdventureWorks2012] TO DISK = N'F:\Backup\AW12.bak' WITH CHECKSUM;
To create a differential backup use the following T-SQL script:
USE AdventureWorks2012 GO BACKUP DATABASE [AdventureWorks2012] TO DISK = N'F:\Backup\AW12.bak' WITH CHECKSUM; BACKUP DATABASE [AdventureWorks2012] TO DISK = N'F:\Backup\AWD12.bak' WITH DIFFERENTIAL; WITH CHECKSUM; GO
To backup transaction log use the following script:
BACKUP LOG [AdventureWorks2012] TO DISK = N'F:\Logs\AWD12.log'; GO
4. Click ok to add a step, and click OK to create a job:
5. To schedule a job, in the New Job dialog, under the Schedule tab click New.
6. In the Job Schedule select an occurring frequency and a start date and click OK:
To check a created job in the Object Explorer pane and under the SQL Server Agent ➜ Jobs node right-click the job create above and select the Start job at step option:
There are two methods to use SQL Server Agent for backing up all databases under one instance, both of which involve some manual labor. One method is to construct an SSIS package using the SSIS toolbar’s Backup Database Task option, then schedule it using a SQL Server Agent task.
The alternative strategy is to create a T-SQL script that will backup every database in the SQL Server Agent Job Step dialog.
Conclusion
We hope that article above can help you to schedule your SQL db backup. Backup is important for your business in case there is something wrong or you accidentally delete your database.
Use ASPHostPortal’s reputable domain hosting service if you’re searching for domain, hosting, and SSL to have the best website with the most traffic possible. Start your hosting as low as $1.00/month.
Andriy Kravets is writer and experience .NET developer and like .NET for regular development. He likes to build cross-platform libraries/software with .NET.
