As far as I am concerned, if a DBA maintains proper backups and can guarantee recovery of data up to the point required by the business process, they have done the job they were hired for. Every other activity is a simple bonus on top of this.

As you passed through these chapters, you may have noticed that the amount of detail provided has been purposely sparse. We have sought to provide all of the basic and reference information you will need to perform all of your daily activities. While this chapter will still hold true to those same principles, due to the importance of this topic it will depart slightly by providing a significant amount of detail and explanation. We will begin with a brief overview for those already familiar with SQL Server. After that we will cover each of the backup options, backup media, restoration scenarios, and finally constructing a backup strategy and disaster recovery strategy. The other departure from the norm is that this chapter will focus heavily on using the TSQL commands to perform backups instead of Enterprise Manager. This is done simply because any production environment I have been in utilizes stored procedures or TSQL batches to perform backup operations. The only time Enterprise Manager is used is for performing manual backups in support of other activities in your environment.

Just about anyone can start up Enterprise Manager, point and click, and create a backup. Understanding everything that goes on underneath Enterprise Manager when you click that OK button is the goal of this chapter. This is also a very good method to follow with every operation you can perform. Enterprise Manager simply provides an interface to execute Transact SQL commands. You should always understand the exact series of commands that Enterprise Manager will execute before clicking any button or selecting any menu item that will change something on yoru server.

When you are done with this chapter, you should have a complete framework for applying backups to your systems.

Enhancements

By this point, you may have noticed that with version 7.0, Microsoft has made using SQL Server much easier than previous versions. Don’t let this ease of use fool you. SQL Server has become a much more complex product and demands an increased skill level and knowledge to manage effectively.

For those familiar with previous versions of SQL Server, you may be wondering where the backup commands are. Don’t worry, they have just undergone a needed change in name. Now instead of performing a dump and load, you perform a backup and restore. This brings the commands into line with accepted terminology. With this change in terminology, the backup and restore commands have undergone a significant enhancement. There is an additional type of backup that is available and the backup and restore commands have a large number of additional options.

Backup Overview 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32