Scheduling Backups Using the SQL Maintenance Plan Wizard

Scheduling Backups Using the SQL Maintenance Plan Wizard

Until now you have performed our backups through a Query Window in SQL Server Management Studio. Of course, in production systems, backups need to be scheduled to run automatically, without any user input. This can be done using any scheduling software. SQL Server has a built-in scheduler as part of the SQL Server Agent Service. With SQL Server Agent Jobs, it is possible to define T-SQL batches to run on given schedules. This feature provides the functionality you need to automate your recovery strategy.

Have a look at another easy way to schedule backups by using a wizard. The SQL Maintenance Plan Wizard is a tool that helps perform standard tasks , like Backup, Index Defragmentation, and Check Database Integrity. It relies on the SQL Server Agent Services because it schedules jobs that are started by SQL Server Agent at requested times. Therefore, the SQL Server Agent Service has to be running at all times. You can configure the SQL Agent Service using SQL Server Configuration Manager. Configure it to start up automatically and start it if necessary by using the following steps:

Starting SQL Server Agent Automatically

Using the SQL Maintenance Plan Wizard

Conclusion

In this chapter, you have seen the different backup types SQL Server provides. The combination and scheduling of these different types should form a backup strategy that meets your system performance and data integrity needs. These strategies range from simple models using only full database backups to full models using transaction log backups to provide the functionality to recover data right up to the time of data corruption.

Remember that a backup strategy should be planned, implemented, and tested for every database you have. Don't wait to test your strategy until after data corruption has already occurred!

Chapter 3 Quick Reference

USE master;
GO
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE;
GO

SELECT DATABASEPROPERTYEX
('AdventureWorks','Recovery')

USE msdb
GO
SELECT backup_start_date, type,
    physical_device_name, backup_set_id
FROM backupset bs
inner join backupmediafamily bm
    ON bs.media_set_id = bm.media_set_id
WHERE database_name ='AdventureWorks'
ORDER BY backup_start_date desc

SELECT filegroup_name,logical_name,
    physical_name
FROM msdb..backupfile
WHERE backup_set_id = <backup id>

RESTORE HEADERONLY FROM DISK=
    'T:\BACKUPS\ADVFULL.BAK'
RESTORE FILELISTONLY FROM DISK=
    'T:\BACKUPS\ADVFULL.BAK'