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

To	Do this
Set the recovery model	Execute the SQL statement USE master; GO ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE; GO
Verify the recovery model	Execute the SQL statement SELECT DATABASEPROPERTYEX ('AdventureWorks','Recovery')
Retrieve simple backup information	Execute the SQL statement 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
Find out which data and log files were affected by a backup id	Execute the SQL statement SELECT filegroup_name,logical_name, physical_name FROM msdb..backupfile WHERE backup_set_id = <backup id>
Retrive information directly from a backup device	Execute the SQL statements RESTORE HEADERONLY FROM DISK= 'T:\BACKUPS\ADVFULL.BAK' RESTORE FILELISTONLY FROM DISK= 'T:\BACKUPS\ADVFULL.BAK'
Restore a database using SQL Server Management Studio	Right-click the database in the Object Explorer window and select Tasks | Restore | Database.
Start the SQL Server Agent Service	Open SQL Server Configuration Manager. Open the SQL Server Agent properties window. Change the Start Mode item on the Service tab to Automatic, right-click the SQL Server Agen icon, and select Start.
Schedule a backup	In SQL Server Management Studio, open the Management folder in the Object Exlorer window. Select Maintenance Plan Wizard and follow the prompts.