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.





Solid Quality Learning - Microsoft SQL Server 2005. Applied Techniques Step by Step
Microsoft SQL Server 2005: Applied Techniques Step by Step (Pro-Step by Step Developer)
ISBN: 0735623163
EAN: 2147483647
Year: 2006
Pages: 115

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net