CA ERwin® Data Modeler

tag:blogger.com,1999:blog-53454715435231533002012-02-16T06:01:57.107-08:00CA ERwin® Data ModelerJaxmimhttp://www.blogger.com/profile/18006578535182915347noreply@blogger.comBlogger61100tag:blogger.com,1999:blog-5345471543523153300.post-81305639452172288132009-07-10T07:31:00.000-07:002009-07-10T07:35:07.013-07:00C H A P T E R 7<style>  </style> C H A P T E R 7<o:p></o:p> Database Backups, Recovery,<o:p></o:p> and Maintenance<o:p></o:p> Now that we have created a major part of the database in the previous chapters, and before<o:p></o:p> moving on to inserting and manipulating the data, this is a good point to take a moment to back up<o:p></o:p> the database, just so that if things go wrong, it will be possible to recover back to a stable point.<o:p></o:p> What is abundantly clear when working with any sort of system where data is held is that there<o:p></o:p> must be a comprehensible and workable backup and recovery strategy in place for when things go<o:p></o:p> wrong. The recovery may also be required to cater to problems from a hardware failure to an act of<o:p></o:p> God. In any of these instances, we may move to an offsite location, which is a building a safe distance<o:p></o:p> away from our current building housing the computing equipment. That is quite a dramatic step and<o:p></o:p> is a decision that would be taken at a higher level of authority than we probably have; however, we<o:p></o:p> must create a backup of our system and store it according to the recommendations of our board of<o:p></o:p> directors, whether they are for in-house or offsite storage. Companies have gone bust because a good<o:p></o:p> and secure backup storage plan wasn’t in place when their building burned down, for example. This<o:p></o:p> is, of course, a worst-case scenario, and there are times that moving out of the current building to a<o:p></o:p> second secure location is not necessary.<o:p></o:p> This chapter looks at different backup strategies that can be implemented by you as a developer<o:p></o:p> or an administrator and shows you how to implement them. I also show you scenarios where the<o:p></o:p> database is in use 24 hours a day, 7 days a week, and how you need to form a backup strategy around<o:p></o:p> such scenarios. From there, I show you how to perform an ad hoc backup of the database as well as<o:p></o:p> scheduled transaction log backups. I make it clear in this chapter when you would perform both of<o:p></o:p> these types of backups and when they would be useful. Of course, after the backup, you have to test<o:p></o:p> that the backup can be restored. Generally, this backup is restored onto a nonproduction system.<o:p></o:p> Some companies have complete environments established to test their disaster-recovery scenarios.<o:p></o:p> What you have to realize, and what will be demonstrated, is that there are different methods of<o:p></o:p> taking backups depending on what you are trying to achieve. The most common scenarios are<o:p></o:p> discussed and demonstrated in this chapter, but you will also get to look at database maintenance<o:p></o:p> plans.<o:p></o:p> It is imperative that you get the correct backup strategy in place and that it works. This point will<o:p></o:p> be repeated throughout the chapter.<o:p></o:p> So, in this chapter you will learn about:<o:p></o:p> • Backup strategies<o:p></o:p> • When a problem might occur during the backup and restore process<o:p></o:p> • How to take a database offline and then bring it back online<o:p></o:p> • How to create a backup<o:p></o:p> • Different media for building a backup and what needs to be considered<o:p></o:p> 182 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE<o:p></o:p> • The transaction log and how to back it up<o:p></o:p> • When to back up the data, and when to back up the transaction log<o:p></o:p> • Scheduling backups, and what happens if the server isn’t running at the scheduled time<o:p></o:p> • Restoring a database<o:p></o:p> • Detaching and attaching a database<o:p></o:p> • Working with users still attached to the database when you need them not to be connected<o:p></o:p> • Building SQL statements for backing up the database structure, and when it is useful to<o:p></o:p> have them<o:p></o:p> • Building a maintenance plan and when to use it<o:p></o:p> • Implementing Database Mail in your maintenance plan<o:p></o:p> Transaction Logs<o:p></o:p> Data within the database is stored, of course, on your hard drive, or on a hard drive on a server.<o:p></o:p> Imagine the work involved on the disk controller, and imagine the work SQL Server has to do every<o:p></o:p> time data is added, amended, or removed. Writing data is a slow process compared to other memorybased<o:p></o:p> processes, so inevitably, SQL Server slows down every time data is written. A good comparison<o:p></o:p> is to think how long it takes you to insert, modify, or erase a sentence, even using MS Word, compared<o:p></o:p> to how long it takes you to read a sentence. What if part of the way through writing the data, a power<o:p></o:p> outage occurred and you had no uninterruptible power supply (UPS) in place? What a mess you<o:p></o:p> would be in, not knowing what had been written to disk and therefore your tables within your database,<o:p></o:p> and what hadn’t!<o:p></o:p> It is also possible in SQL Server to update several tables at once. How would you work around<o:p></o:p> the fact that some of the tables had been updated, but when it came to updating a specific table,<o:p></o:p> the update failed? Well, this is where transaction logs come into play. Transactions themselves are<o:p></o:p> covered in Chapter 8, but very simply, a transaction is a single unit of work that can contain one or<o:p></o:p> more table modifications that are either all successful and committed to the database or, if some are<o:p></o:p> unsuccessful, all discarded. It is also possible to roll back a transaction so that no changes are made<o:p></o:p> to the database, which can either be invoked by SQL Server or by issuing a specific T-SQL command.<o:p></o:p> But you must be wondering what all this has to do with a transaction log, and you’re probably even<o:p></o:p> wondering what a transaction log is. Before we move on, there is one last area of background information<o:p></o:p> we need to discuss first.<o:p></o:p> The log file is a complex item that contains virtual, logical, and physical log files, but to keep it<o:p></o:p> simple, the transaction log has a physical size—in other words, it is like any other file on the hard<o:p></o:p> drive. The transaction log increases in size as necessary to accommodate the transactions. It is a<o:p></o:p> wraparound file. This means that when SQL Server reaches the end of the file as defined by the size<o:p></o:p> when it was set up, it wraps around to the beginning again, looking for free space to use. This allows<o:p></o:p> it to wrap around without increasing in size when there is free logical transaction space. The logical<o:p></o:p> transaction log is “contained” within the physical transaction log and is determind by a physical<o:p></o:p> point in the physical log file at where the last checkpoint or truncation action occurred, and it<o:p></o:p> continues until the next checkpoint or truncation point occurs. Virtual log files are files of no fixed<o:p></o:p> size and are built internally by SQL Server as part of the physical creation of the transaction log.<o:p></o:p> Every database within a SQL Server instance has its own transaction log. Every time SQL Server<o:p></o:p> is requested to do any data modifications—whether these are additions, deletions, or modifications—<o:p></o:p> a record is kept of the action. There are several reasons for this.<o:p></o:p> First of all, a piece of code could in fact do several different updates at once, either to different<o:p></o:p> rows of data or to rows of data in different tables or even databases. If one of the updates fails, for<o:p></o:p> example, when you are attempting to place ASCII characters into a column that only allows numerics,<o:p></o:p> CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 183<o:p></o:p> then you may wish to return the values in all the updated fields to their original value. This is called<o:p></o:p> rolling back a transaction. SQL Server achieves this, in part, by looking at the data held in the transaction<o:p></o:p> log. However, any successful action where all the updates are valid could be permanently<o:p></o:p> stored on file—a process called committing a transaction.<o:p></o:p> As more and more actions are placed in the transaction log, it will become full. Some of these<o:p></o:p> actions will still be within a transaction, and others may form part of a completed transaction ready<o:p></o:p> to be committed to the database. At certain points, SQL Server will want to remove all the actions<o:p></o:p> it can to relieve some space in the transaction log for further actions. One point could be when the<o:p></o:p> transaction log reaches 70% full. SQL Server would then issue a checkpoint. The use of a checkpoint<o:p></o:p> ensures that any data modifications are actually committed to the database and not held in any<o:p></o:p> buffers, so that if a problem occurs, such as a power failure, there is a specific point that you can start<o:p></o:p> from. Therefore, at the end of a checkpoint transaction, you know the database is in a consistent and<o:p></o:p> valid state. As SQL Server knows that at a checkpoint all is well within the database, there is no need<o:p></o:p> to keep the completed transactions recorded in the transaction log stored up to the checkpoint. SQL<o:p></o:p> Server therefore issues a truncation of the transaction log to remove these records, minimizing the<o:p></o:p> size of the log on the computer. This is known as truncating the transaction log. It is thus necessary<o:p></o:p> to ensure that you have a large enough transaction log defined to hold the largest valid uncommitted<o:p></o:p> transaction, as these transactions obviously will not be truncated. A transaction log can become full<o:p></o:p> with a rogue query as well—that is, one that is coded incorrectly and keeps adding more and more<o:p></o:p> uncommitted transactions. When the transaction log reaches 70%, there is nothing to checkpoint<o:p></o:p> and eventually the transaction log will fill up, and your SQL Server will stop. This is where you will<o:p></o:p> need the help of an experienced database administrator.<o:p></o:p> If you have a power failure, you might have to “replay” all the work completed since the last<o:p></o:p> backup, and in certain scenarios, you could use the transaction log to do this. When a data modification<o:p></o:p> is completed via a T-SQL command, the details are recorded first in the transaction log. These<o:p></o:p> changes are flushed to disk and therefore no longer in memory, before SQL Server starts to make<o:p></o:p> changes to the tables that you are affecting. SQL Server doesn’t write data immediately to disk. It is<o:p></o:p> kept in a buffer cache until this cache is full or until SQL Server issues a checkpoint, and then the data<o:p></o:p> is written out. If a power failure occurs while the cache is still filling up, then that data is lost. Once<o:p></o:p> the power comes back, though, SQL Server would start from its last checkpoint state, and any updates<o:p></o:p> after the last checkpoint that were logged as successful transactions will be performed from the<o:p></o:p> transaction log.<o:p></o:p> ■Note A disk cache is a space in the system that holds changes to the tables within the database. This allows<o:p></o:p> a whole block of data to be written at once, saving on the slow process of disk head movement.<o:p></o:p> Transaction logs are best kept, if at all possible, on a hard drive separate from that holding the<o:p></o:p> data. The reason for this is that data is written serially when it is written to a transaction log. Therefore,<o:p></o:p> if nothing else is on the hard drive except the transaction log, the disk heads will be in the right<o:p></o:p> place to continue writing each time. This is a minor overhead, but if performance is an issue, this is<o:p></o:p> worth considering.<o:p></o:p> Backup Strategies<o:p></o:p> Backing up a database, no matter how large or small, should form part of your database solution.<o:p></o:p> Even if a backup is taken only once a week or even once a month, it is crucial that you sit down and<o:p></o:p> decide which backup strategy is right for you. Much of this decision lies in the hands of the product<o:p></o:p> owners for your company, since they must weigh the risk they’re willing to take against the cost of<o:p></o:p> minimizing that risk. Many different strategies can be adopted within your overall main backup<o:p></o:p> strategy, depending on days of the week or perhaps periods within the month.<o:p></o:p> 184 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE<o:p></o:p> Based on the strategy that you choose, you have to decide what type of backup you need. Full<o:p></o:p> database backups take a complete snapshot of a database at any given point. A differential backup<o:p></o:p> backs up only the data that has changed since the last full backup. Finally, a transaction log backup<o:p></o:p> backs up only the data in the transaction log, which consists of transactions that were recently<o:p></o:p> committed to the database. All of these types of backups can be done while your SQL Server is online<o:p></o:p> and while users are actively hitting your database. To be able to restore a database to any point in<o:p></o:p> time, you have to use a combination of these backup types.<o:p></o:p> When determining your backup strategy, first look at your application and ask yourself the<o:p></o:p> following questions:<o:p></o:p> • How much of the data can be lost, if any, at any point of failure? In other words, how crucial<o:p></o:p> is it that no data is lost?<o:p></o:p> • How often is the data updated? Do you need regular backups from a performance viewpoint<o:p></o:p> as well as a recovery viewpoint? For historical databases that have their data modified only<o:p></o:p> periodically, you would, at most, complete a backup postpopulation.<o:p></o:p> • Do you need to back up all the data all of the time, or can you do this periodically, and then<o:p></o:p> only back up the data that has altered in the meanwhile?<o:p></o:p> • How much data needs to be backed up, and how long do you need to keep the copies of the<o:p></o:p> backups?<o:p></o:p> • In the event of catastrophic failure, how long will it take to completely rebuild the database, if<o:p></o:p> it’s even possible?<o:p></o:p> Many more questions can be asked, but for the moment, these are the most crucial questions<o:p></o:p> that need answers. If you can afford to allow data updates to be lost, then you need a straightforward<o:p></o:p> periodic database backup—for example, you need to back up the whole database once a week. This<o:p></o:p> is simple and easy to complete and requires little thought. However, this scenario is rare and is usually<o:p></o:p> found in data-warehousing systems or those with a large amount of static data that can be rebuilt.<o:p></o:p> Looking at the next question, if a large number of updates are taking place, then a more complex<o:p></o:p> solution is required. For every data modification, a record is kept in the transaction log file, which has<o:p></o:p> a limited amount of space. This amount of space was defined when you set up the database as a fixed<o:p></o:p> maximum size or, if you are allowing it to grow unrestrictedly, equals the amount of hard drive there<o:p></o:p> is. If you back up and clear the transaction log file, this will free up the space logically initially and<o:p></o:p> also aid performance. This is known as logical log truncation. The smaller the active part of the<o:p></o:p> transaction log file, the better. The more transactions there are in the transaction log file, the longer<o:p></o:p> it will take to recover from a corrupt database. This is due to the fact that a restore will have to restore<o:p></o:p> the data and then every transaction log backup to the point of failure. That is, each transaction log<o:p></o:p> will have to be restored to update the database, not just the latest log file. If you have multiple small<o:p></o:p> files and they are held on media that has to be mounted each time, such as a tape, then you will have<o:p></o:p> to take mounting time into consideration as well.<o:p></o:p> The third question, though, covers the real crux of the problems. If you need to back up all the<o:p></o:p> data each time, how often will that need to take place? This could well be every night, and many<o:p></o:p> production systems do just this. By completing a full data backup every night, you are allowing yourself<o:p></o:p> to be in a state where only one or two restores may need to occur to get back to a working state<o:p></o:p> in a disaster scenario. This would be the data backup, followed by the single transaction log backup,<o:p></o:p> if one was taken in the meantime. This is much better than having one data backup to be restored,<o:p></o:p> and then a log file for every day since the data file backup. What happens if the failure is on a Friday<o:p></o:p> at lunchtime and you completed your last whole database backup on a Saturday evening? That<o:p></o:p> would take one data file and six transaction log file restores to complete.<o:p></o:p> Sit down and take stock. As often as you can, take a full database backup, then take a differential<o:p></o:p> backup, followed by transaction log backups. However, you have to weigh the time that a full backup<o:p></o:p> takes against a differential backup or a transaction log; how much processing time you have to complete<o:p></o:p> these backups; and the risk level of having to complete, for example, six transaction log restores.<o:p></o:p> CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 185<o:p></o:p> The problem is, there is no universally right answer. Each situation is different, and it is only<o:p></o:p> through experience, which comes from testing your solution thoroughly, that you find out what is<o:p></o:p> best for your situation.<o:p></o:p> Whatever your backup strategy, the best possible scenario is to complete a backup when nobody<o:p></o:p> is working within the database. If there are times when you can make the database unavailable, then<o:p></o:p> this is an ideal opportunity to take the backup. Although SQL Server can perform full backups while<o:p></o:p> the database is online and active, you will gain performance benefits by having an inactive database.<o:p></o:p> The first example, shortly, demonstrates one method of doing this.<o:p></o:p> When Problems May Occur<o:p></o:p> Obviously, when taking a backup, it must work; otherwise, you have wasted your time, but crucially,<o:p></o:p> you are leaving your database and organization in a vulnerable position if the backup fails. If you<o:p></o:p> have time within your backup window to verify that a backup has been successful, then you must do<o:p></o:p> it. As you will see, SQL Server gives you different options for doing this. It cannot be stressed strongly<o:p></o:p> enough that verifying a backup is just as crucial as taking the backup in the first place. There have<o:p></o:p> been situations where a backup has been taken every night; however, no one has noticed that the<o:p></o:p> backup has failed. When there is a hardware failure, there’s no backup to use as a restore on a new<o:p></o:p> machine. In one case I know of, almost a week’s worth of data was lost. Luckily, the weekend backups<o:p></o:p> had succeeded; otherwise, the company would have been in a major data loss situation. The cause<o:p></o:p> of the failed backups was that the tapes being inserted for the backup were not large enough to hold<o:p></o:p> the backup being performed. Therefore, the tapes became full, and the backup failed. Obviously,<o:p></o:p> this company failed not only to verify the backup, but also to have processes in place to check that<o:p></o:p> its backup strategy was still working after a period of implementation. The only sure and positive<o:p></o:p> way of ensuring a backup has succeeded is to restore the database to a specific restore test location<o:p></o:p> and check the data. Although you will see SQL Server does have a method of checking a backup, this<o:p></o:p> still isn’t a guarantee that the backup worked. Do take time to complete regular restores to a location<o:p></o:p> to test that everything is OK.<o:p></o:p> You should always review your backup strategy on a regular basis. Even better, put in place jobs<o:p></o:p> that run each day that give some sort of space report so that it is possible to instantly see that a potential<o:p></o:p> problem is looming. SQL Server Reporting Services is a new tool that would be ideal for<o:p></o:p> producing and distributing space reports to database administrators and developer owners alike.<o:p></o:p> Taking a Database Offline<o:p></o:p> SQL Server does not have to be offline to perform a backup, as you will see as we go through the book<o:p></o:p> and work through creating SQL Server–defined backups using wizards and T-SQL. In most environments,<o:p></o:p> you will not have the luxury of taking a database offline before backing it up, because users<o:p></o:p> are constantly making data changes. Backing up a database can take a long time, and the longer it<o:p></o:p> takes, the longer users cannot be working with the data while it is offline.<o:p></o:p> By taking your database offline, you do not have to use SQL Server to perform the backup. This<o:p></o:p> strategy is one where you take a disk backup, which means the hard drive is backed up, rather than<o:p></o:p> a specific database within a server. However, don’t forget that by taking your database offline, you<o:p></o:p> will have to take a backup of the directory using some sort of drive backup.<o:p></o:p> If you have your database on a server, no doubt some sort of server backup strategy is in place,<o:p></o:p> and so your database will be backed up fully and successfully through this method; if you can take<o:p></o:p> your database “out of service” in time for those backups, then you should do so. This does allow you<o:p></o:p> to think about your SQL Server deployment strategy. If you have several databases that can be taken<o:p></o:p> offline as part of the backup, then it is worth considering whether they can all reside on the same<o:p></o:p> physical server, and you can set your server backup times accordingly. However, this is a rare scenario,<o:p></o:p> and even rarer within a production environment. Taking the database offline means taking your<o:p></o:p> 186 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE<o:p></o:p> database out of service. Nobody can update or access the data, modify table structures, and so on. In<o:p></o:p> this next section, we will take ApressFinancial offline, allowing a physical backup to be taken. Just to<o:p></o:p> reiterate and clarify: this is being demonstrated only to complete your knowledge of backups, and it<o:p></o:p> is rare that you will perform this action in a live scenario.<o:p></o:p> Try It Out: Taking a Database Offline<o:p></o:p> 1. Open SQL Server Management Studio and open a Query Editor pane. Enter and execute the following code:<o:p></o:p> USE master<o:p></o:p> GO<o:p></o:p> ALTER DATABASE ApressFinancial<o:p></o:p> SET OFFLINE<o:p></o:p> 2. Try to click some of the nodes for the ApressFinancial database—for example, the Tables node. As shown<o:p></o:p> in Figure 7-1, we are reminded that the database is offline and therefore cannot be viewed or modified. We are<o:p></o:p> also not able to access the database through any application such as Query Editor.<o:p></o:p> Figure 7-1. The database is offline and therefore unable to be opened.<o:p></o:p> To take a database offline, SQL Server must be able to gain exclusive access to the database. This means that no user can<o:p></o:p> be in the database when we issue the command. If users are connected, then the query will continue to execute until all<o:p></o:p> users are disconnected.<o:p></o:p> As said earlier, that’s all there is to it. Our database is now no longer available for any updates or modifications, so we can<o:p></o:p> back it up using any backup utility that takes files from a hard drive.<o:p></o:p> If you have to restore from a backup completed this way, don’t forget to take the database offline first, then restore from<o:p></o:p> the backup, and then bring the database back online, ready for use:<o:p></o:p> USE master<o:p></o:p> go<o:p></o:p> ALTER DATABASE ApressFinancial<o:p></o:p> SET ONLINE<o:p></o:p> There is one area to note when using backup strategies that employ these methods. If you have a server backup that runs,<o:p></o:p> for example, at 0200 hours, do you fancy getting up every night, just before 2 a.m., taking the database offline, and then<o:p></o:p> bringing the server back up once the backup is complete? No—not many people would. Of course, there are installations<o:p></o:p> where people are working through the night, so this is less of a problem, but what if they are busy? Or forget? Then your<o:p></o:p> whole backup will fail because the files are in use, and therefore the server will not back up these files.<o:p></o:p> So let’s now look at a more friendly method of backing up the data by using SQL Server instead.<o:p></o:p> CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 187<o:p></o:p> Backing Up the Data<o:p></o:p> The majority of readers will use SQL Server to back up the database. By using SQL Server, we are<o:p></o:p> keeping the backup of the database under the control of an automated process that can control its<o:p></o:p> own destiny, and as you will find out later, it can also control the system when things go wrong.<o:p></o:p> The backup will be split into two parts. The first part, which will be covered here, will be when<o:p></o:p> we perform the backup manually each time. Obviously, this means we have to be available to perform<o:p></o:p> the backup, but this can be rectified quite easily. Once this has been covered, the next section will<o:p></o:p> show you how to schedule a backup to run at a specific time, which relieves us of needing to be available<o:p></o:p> to complete a backup at the specified time.<o:p></o:p> Let’s start by looking at the manual backup.<o:p></o:p> Try It Out: Backing Up the Data<o:p></o:p> 1. Ensure SQL Server Management Studio is running. Find our database, right-click, select Tasks, and then click<o:p></o:p> Back Up.<o:p></o:p> 2. This brings up the SQL Server Back Up Database dialog box. Take a moment to peruse this dialog box. As<o:p></o:p> Figure 7-2 shows, a lot appears on this screen, which will be dealt with a section at a time.<o:p></o:p> Figure 7-2. Backing up a database (Full Recovery mode)<o:p></o:p> 188 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE<o:p></o:p> Although we have chosen our own database to back up, we could alter which database by changing the value in the combo<o:p></o:p> box. Next is the backup type, of which we have three options to choose from: Full, Differential, and Transaction Log.<o:p></o:p> The first possibility, full backup, is straightforward. Selecting the Full option ensures that the whole database will be backed<o:p></o:p> up. All the tables, the data, and so on are backed up if we use this option. We also back up enough of the transaction log<o:p></o:p> (transactions committed via code, but not physically applied to the relevant tables at the point of backup). This backup<o:p></o:p> would be seen as any starting point for a restore from a database failure.<o:p></o:p> The second possibility is the differential backup, also known as an incremental backup. Use the Differential backup option<o:p></o:p> when the circumstances are such that a full backup is either not required or not possible. This just performs a backup<o:p></o:p> on data changed since the last backup. For example, we may take a full backup on the weekend and then a differential<o:p></o:p> backup every night. When it comes to restoring, we would restore the weekend full backup, and then add to that the latest<o:p></o:p> differential backup taken from that point. A word of warning here: if you do take a week’s worth of differential backups, and<o:p></o:p> you back up to magnetic tape rather than a hard drive, do keep at least enough different tapes for each differential backup.<o:p></o:p> Therefore, use a complete backup when necessary and then in the interim, use a differential backup.<o:p></o:p> The last possibility, the transaction log backup, should be used as the most frequent method of backup providing that the<o:p></o:p> database is not in Simple Recovery mode. As data is modified, details of the modifications are stored in the transaction log.<o:p></o:p> These remain in place until an action “truncates” the transaction log, which means that the transaction log will increase<o:p></o:p> constantly in size if not in Simple Recovery. When you issue a transaction log backup, you are just backing up the transaction<o:p></o:p> log, which issues a checkpoint, and all committed transactions are stored onto the backup. This means that if a<o:p></o:p> system failure occurs, you will restore from a full backup, then from your differential backups for the week, and finally from<o:p></o:p> any transaction log backups after that point.<o:p></o:p> You are probably wondering why you can’t just use differential backups. Transaction logs can fill up during the working day,<o:p></o:p> or perhaps you have set differential backups to happen weekly because there is so little data modification. However, you<o:p></o:p> do need to account for when a transaction log may fill up before you reach the next differential backup. By taking a backup<o:p></o:p> of the transaction log, this is a great deal faster than the other two methods. Certainly in heavily used databases, you may<o:p></o:p> have several transaction log backups in the day. You see how to do this using T-SQL after we take our first full backup. At<o:p></o:p> least one backup must exist before we can take a transaction log backup, as we need a point at which the transaction log<o:p></o:p> can roll committed transactions forward from.<o:p></o:p> ■Note If we were backing up the master database, then the only option that would be available to us would be<o:p></o:p> a complete database backup via the Full option.<o:p></o:p> A name and a description of your backup are always useful. You will create different backups over time, so a good description<o:p></o:p> is always something that will help at a later date. I recommend that you use some sort of date and time as part of the<o:p></o:p> description, as this will make it easier to find, and which mode of backup you have chosen.<o:p></o:p> Different types of backups will have different expiry dates. This means that after the defined date, the media you have<o:p></o:p> stored your backup on will allow the data to be overwritten if using SQL Server (you can't delete the file manually!). For<o:p></o:p> example, you might have a weekly full backup that you want to keep three instances of, and then the first full backup of<o:p></o:p> the month you may wish to keep for six months, or even longer if it is a database that you must keep for government legislation.<o:p></o:p> In this option, you can retain the backup for a set number of days (for example, 21 days) or for a set period of time<o:p></o:p> (a specific date covers for uneven days in a month, or a year, for example).<o:p></o:p> A default destination is defined, which might be more than acceptable. It will be on our hard drive, in a location below<o:p></o:p> where our data is. It is best to have a directory set aside for backups so that they are easy to find, perhaps with a name<o:p></o:p> such as SQL Server Backups. However, this is not recommended in production. What if the hard drive fails? We can gain<o:p></o:p> a substantial performance improvement by backing up the database to a separate disk or to a RAID 1 drive if one is available.<o:p></o:p> This is because data is written to the backup file in a sequential manner. It is also advisable to give the backup file a meaningful<o:p></o:p> name. In this instance, it has been given the name of the database, ApressFinancial.<o:p></o:p> CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 189<o:p></o:p> 3. Move to the Options tab, as shown in Figure 7-3, where we can define what options we want to happen as part<o:p></o:p> of this backup.<o:p></o:p> Figure 7-3. Database backup options<o:p></o:p> The first section of this dialog box deals with what you want to happen on second and subsequent backups. The first time<o:p></o:p> the backup is run, it creates the backup files, but when you run subsequent backups, do you want to append to the current<o:p></o:p> data or overwrite it? If this is a full backup, then you may overwrite, as you should be placing this full backup over an old<o:p></o:p> nonrequired backup. However, if this is a differential backup, where it is perhaps the second or third of the week, then you<o:p></o:p> will append to the existing backup set. This will be after the previous differential backups and means that if you need to do<o:p></o:p> a restore, all the backups will be one after another and therefore will provide the fastest retrieval.<o:p></o:p> The Check Media Set Name option forces the backup to check that where the data is going to be backed up to is still a valid<o:p></o:p> name and, if appending, that the data set has not expired.<o:p></o:p> You use the Back Up to a New Media Set, and Erase All Existing Backup Sets option when any previous backups are no<o:p></o:p> longer required. This is ideal when moving the database from development to either user testing or even production, and<o:p></o:p> you don’t want to be able to restore from an incorrect backup. There is no point in wishing to restore a production server<o:p></o:p> from a development backup, after all.<o:p></o:p> The second section deals with the reliability of the backup. It is possible to simply back up the data and trust that everything<o:p></o:p> worked perfectly, meaning no data transmission errors occurred between your SQL Server and the backup device, or<o:p></o:p> that no errors occurred when writing the data. A situation such as this is unusual, but there will be times when it does<o:p></o:p> happen. Do you trust that those times will occur when you will not need a backup? I suggest this is something you cannot<o:p></o:p> and should not rely on. Therefore, although it will increase the amount of time the backup takes, it is good to choose one<o:p></o:p> 190 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE<o:p></o:p> of the two options in this section. The first option allows a verification of the backup where SQL Server compares what has<o:p></o:p> been backed up with what it expects to have been backed up, and the second option allows for checksum processing<o:p></o:p> whereby SQL Server performs a mathematical calculation on the data to back up, which generates a checksum that can<o:p></o:p> then be compared once the data has been transmitted from SQL Server to the backup device. If you select the second<o:p></o:p> option, you can also specify whether to continue if you get a checksum error.<o:p></o:p> If you are doing a transaction log backup, the next area of the dialog box will be enabled. You can logically shrink the transaction<o:p></o:p> log by removing all entries that have just been backed up by selecting the first option, Truncate the Transaction Log.<o:p></o:p> To save processing time, the physical transaction log is not shrunk. The second option, Back Up the Tail of the Log, is used<o:p></o:p> when there has been some sort of database corruption. If you wish to back up transaction log records that have not been<o:p></o:p> backed up prior to performing a restore to correct the corruption, then you would use this option. To clarify, a database<o:p></o:p> becomes corrupt, and you need to be able to restore up to the last backup, then add all the transactions that have occurred<o:p></o:p> since the last backup. By executing a backup of the tail of the log, you can restore the database and then use this tail log<o:p></o:p> backup to add the missing transactions.<o:p></o:p> The penultimate area of the dialog box is available if you are using tapes as your backup medium. You can eject the tape<o:p></o:p> once the backup has finished. This is a useful option, as the computer operators would know to remove the tape for dispatch<o:p></o:p> to the safe backup area. The second option, which specifies a rewind, is useful for full backups. On differential backups,<o:p></o:p> however, SQL Server would be halted when running the next backup while the tape device got to the right place on the tape<o:p></o:p> to continue the backup.<o:p></o:p> Finally, it is possible to set the compression level for the backup. This is ideal to keep space taken for each backup to a<o:p></o:p> minimum. Notice that by default, it takes the compression level set for the server, but it’s possible to overwrite this with a<o:p></o:p> new setting. If you do use compressed backups, then you will save on I/O and the backup operation will complete in less<o:p></o:p> time, but completing the compression will increase the amount of processing SQL Server is using. It is possible to reduce<o:p></o:p> the priority of completing a compressed backup so that it doesn’t affect SQL Server by using the resource governor. This<o:p></o:p> is an advanced topic and not covered in this book.<o:p></o:p> Clicking OK starts the backup. Once the backup is finished, you should see the dialog box shown in Figure 7-4.<o:p></o:p> Figure 7-4. A successful backup<o:p></o:p> The first backup of the ApressFinancial database has now taken place and should have been successful. If we now<o:p></o:p> move to the directory on the hard drive where the backup took place, then we will see the ApressFinancial file.<o:p></o:p> Recall that it was mentioned earlier that a company lost a week’s worth of data. It had set up the option to append to<o:p></o:p> media, the tape had become full, and the administrator had not set up the proper scenario to alert someone when a<o:p></o:p> problem occurred. So there was not just one failure in the system, but two; however, it still highlights that if you are using<o:p></o:p> the option to append to media, you must check that enough room is available on the medium that you are appending to for<o:p></o:p> the backup to succeed.<o:p></o:p> Creating a backup of your database and the data is the most crucial action in building a database solution. Get it wrong,<o:p></o:p> and you may as well go home. Well, not quite, but if (or when) things go wrong, and you don’t have a valid or recent enough<o:p></o:p> backup that is acceptable to the users of your database, it will take a long time for you as a developer to recover from that<o:p></o:p> situation and get back to the excellent working relationship you had beforehand.<o:p></o:p> The backup taken in the preceding example is the simplest backup to perform. It is a complete backup of our particular<o:p></o:p> SQL Server database, and it happens while we are watching. If it goes wrong, we will instantly see and be able to deal with<o:p></o:p> it. However, most backups do not happen when you are there and instead happen throughout the night. In the next section,<o:p></o:p> CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 191<o:p></o:p> you will see more about scheduling jobs and how to schedule a task to run through the night. However, it doesn’t cover<o:p></o:p> what to do when things go wrong. This is a difficult area to discuss and should be integrated with our database maintenance<o:p></o:p> plan, which is covered later in this chapter in the section “Creating a Database Maintenance Plan.” This example<o:p></o:p> demonstrates how to complete a backup manually rather than as an automated process.<o:p></o:p> Before moving on, there are a couple more points concerning backups that you must keep in mind, and it is recommended<o:p></o:p> strongly that these directions be followed. First of all, keep a regular and up-to-date backup of the master and msdb<o:p></o:p> system databases. SQL Server very rarely becomes corrupted, but it can happen for any number of reasons, from a hard<o:p></o:p> drive failure to a developer altering the database in error. It really doesn’t matter, but if you don’t have a backup of the<o:p></o:p> master database, you could find yourself in trouble. However, be warned. Restoring the master database should not be<o:p></o:p> performed unless you really have to, and only if you are experienced with SQL Server. Restoring the master database is<o:p></o:p> not like restoring other databases, and it has to be completed outside SQL Server Management Studio. This book quite<o:p></o:p> deliberately does not cover having to restore the master database, since it is a very advanced topic. If you wish to know<o:p></o:p> more, then take a look at Books Online for more details.<o:p></o:p> When it comes to the msdb database and when to back it up, it could be that a daily backup is required. If you recall, this<o:p></o:p> database holds job schedules and other information pertinent to the SQL Server Agent for scheduling. If you have jobs that<o:p></o:p> run each day, and you need to keep information about when jobs were run, a daily backup may be required. However, if you<o:p></o:p> only wish to keep a backup of jobs that are set up and there is no need to know when certain jobs ran and whether they<o:p></o:p> were successful or not, then perhaps look at backing up this database weekly.<o:p></o:p> The model database should be backed up if any details within the model database have been altered. This should be<o:p></o:p> pretty infrequent, and therefore backing up this database need not be as regular as any other database; once a week is<o:p></o:p> probably frequent enough.<o:p></o:p> Backing up tempdb is not necessary, as this should be seen as a transient database, which has no set state. Therefore, it<o:p></o:p> would be very unusual to back up this database.<o:p></o:p> ■Note When SQL Server is restarted, tempdb is dropped and is re-created as part of the startup process.<o:p></o:p> As you can see, it is not just your own databases that need to be considered and remembered when it comes to dealing<o:p></o:p> with your backup strategy. A database within SQL Server is not an insular arrangement and affects the system databases<o:p></o:p> just as much. If in doubt, back it up more frequently than is required!<o:p></o:p> Backing Up the Database Using T-SQL<o:p></o:p> Now that we have backed up the database using the wizard, it is useful to demonstrate performing a<o:p></o:p> backup with T-SQL. These commands and statements can be used within a stored procedure that<o:p></o:p> can be scheduled to run at required intervals as part of an overnight task.<o:p></o:p> There are two different types of backups. It is possible to back up either the database or specific<o:p></o:p> file groups or files that are part of the database. The code for the database backup follows. The highlighted<o:p></o:p> code demonstrates which of the two possible options is the optional default used when neither<o:p></o:p> option is specified:<o:p></o:p> BACKUP DATABASE { database_name | @database_name_var }<o:p></o:p> TO <> [ ,...n ]<o:p></o:p> [ [ MIRROR TO <> [ ,...n ] ] [ ...next-mirror ] ]<o:p></o:p> [ WITH<o:p></o:p> [ BLOCKSIZE = { blocksize | @blocksize_variable } ]<o:p></o:p> [ [ , ] { CHECKSUM | NO_CHECKSUM } ]<o:p></o:p> [ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ]<o:p></o:p> 192 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE<o:p></o:p> [ [ , ] DESCRIPTION = { 'text' | @text_variable } ]<o:p></o:p> [ [ , ] DIFFERENTIAL ]<o:p></o:p> [ [ , ] EXPIREDATE = { date | @date_var }<o:p></o:p> | RETAINDAYS = { days | @days_var } ]<o:p></o:p> | { COMPRESSION | NO_COMPRESSION }<o:p></o:p> [ [ , ] PASSWORD = { password | @password_variable } ]<o:p></o:p> [ [ , ] { FORMAT | NOFORMAT } ]<o:p></o:p> [ [ , ] { INIT | NOINIT } ]<o:p></o:p> [ [ , ] { NOSKIP | SKIP } ]<o:p></o:p> [ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]<o:p></o:p> [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]<o:p></o:p> [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]<o:p></o:p> [ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]<o:p></o:p> [ [ , ] { NOREWIND | REWIND } ]<o:p></o:p> [ [ , ] { NOUNLOAD | UNLOAD } ]<o:p></o:p> [ [ , ] STATS [ = percentage ] ]<o:p></o:p> [ [ , ] COPY_ONLY ]<o:p></o:p> ]<o:p></o:p> If instead you just wish to back up specific files or file groups, the difference in the code is highlighted<o:p></o:p> in the BACKUP DATABASE statement shown here:<o:p></o:p> BACKUP DATABASE { database_name | @database_name_var }<o:p></o:p> <file_or_filegroup> [ ,...f ]<o:p></o:p> TO <backup_device> [ ,...n ]<o:p></o:p> [ [ MIRROR TO <backup_device> [ ,...n ] ] [ ...next-mirror ] ]<o:p></o:p> [ WITH<o:p></o:p> [ BLOCKSIZE = { blocksize | @blocksize_variable } ]<o:p></o:p> [ [ , ] { CHECKSUM | NO_CHECKSUM } ]<o:p></o:p> [ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ]<o:p></o:p> [ [ , ] DESCRIPTION = { 'text' | @text_variable } ]<o:p></o:p> [ [ , ] DIFFERENTIAL ]<o:p></o:p> [ [ , ] EXPIREDATE = { date | @date_var }<o:p></o:p> | RETAINDAYS = { days | @days_var } ]<o:p></o:p> | { COMPRESSION | NO_COMPRESSION }<o:p></o:p> [ [ , ] PASSWORD = { password | @password_variable } ]<o:p></o:p> [ [ , ] { FORMAT | NOFORMAT } ]<o:p></o:p> [ [ , ] { INIT | NOINIT } ]<o:p></o:p> [ [ , ] { NOSKIP | SKIP } ]<o:p></o:p> [ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]<o:p></o:p> [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]<o:p></o:p> [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]<o:p></o:p> [ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]<o:p></o:p> [ [ , ] { NOREWIND | REWIND } ]<o:p></o:p> [ [ , ] { NOUNLOAD | UNLOAD } ]<o:p></o:p> [ [ , ] STATS [ = percentage ] ]<o:p></o:p> [ [ , ] COPY_ONLY ]<o:p></o:p> I can now give a brief description of all the options that are available. We looked at some of these<o:p></o:p> options previously with the Back Up Database dialog box. Seeing these descriptions allows you to<o:p></o:p> compare options within T-SQL and within the backup dialog boxes:<o:p></o:p> CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 193<o:p></o:p> • database_name | @database_name_var: Either the name of a database or a local variable that<o:p></o:p> gives the name of the database to back up.<o:p></o:p> • file_or_filegroup: The name of the file or file group to back up.<o:p></o:p> • backup_device: The name of the logical or physical backup device to use.<o:p></o:p> • MIRROR TO: The backup file is mirrored to two to four different file locations.<o:p></o:p> • BLOCKSIZE: The block size to use. For example, if backing up to CD-ROM, then you would set<o:p></o:p> a block size of 2048.<o:p></o:p> • CHECKSUM | NO_CHECKSUM: Specifies whether to perform checksum calculations to ensure the<o:p></o:p> transmission of data.<o:p></o:p> • STOP_ON_ERROR | CONTINUE_AFTER_ERROR: Specifies whether to stop on a checksum error or not.<o:p></o:p> • DESCRIPTION: A description of the backup.<o:p></o:p> • DIFFERENTIAL: If this is a differential backup, then specify this option. Without this option, a<o:p></o:p> full backup is taken.<o:p></o:p> • EXPIREDATE: The date the backup expires and is therefore available to be overwritten.<o:p></o:p> • RETAINDAYS: The number of days the backup will be kept before the system will allow it to be<o:p></o:p> overwritten.<o:p></o:p> • COMPRESSION: Lets you compress your backup and reduce the amount of space taken.<o:p></o:p> ■Note Compression backups are only available on the Developer and Enterprise editions of SQL Server. If you<o:p></o:p> don’t define this option, then the server instance setting will be used. You can change it at the server level via the<o:p></o:p> sp_configure system stored procedure.<o:p></o:p> • PASSWORD: The password associated with the backup. This must be supplied when interrogating<o:p></o:p> the backup for any restore operation. There is no strong encryption on this option, so there is<o:p></o:p> the potential that it could be broken easily.<o:p></o:p> • FORMAT | NOFORMAT: Specifies whether to format the storage medium or not.<o:p></o:p> • INIT | NOINIT: Keeps the media header created with the format but erases the contents.<o:p></o:p> • NOSKIP | SKIP: If you want to skip the checking of expiredate or retaindays when using the<o:p></o:p> media set, then select the SKIP option. Otherwise, expiredate and retaindays will be checked.<o:p></o:p> ■Note A media set is an ordered set of backups on the same disk or tape.<o:p></o:p> • MEDIADESCRIPTION: Gives a description to the media set.<o:p></o:p> • MEDIANAME: Names the media set.<o:p></o:p> • MEDIAPASSWORD: Gives the media set its password.<o:p></o:p> • NAME: Names the backup set.<o:p></o:p> • NOREWIND | REWIND: Specifies whether to rewind a tape or not.<o:p></o:p> • NOUNLOAD | UNLOAD: Specifies whether the tape is unloaded or kept on the tape drive.<o:p></o:p> 194 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE<o:p></o:p> • STATS [ = percentage ]: SQL Server provides a message at this percentage interval telling you<o:p></o:p> how much of the approximate backup has completed. It’s useful for gauging the progress of<o:p></o:p> long-running backups.<o:p></o:p> • COPY_ONLY: Tells SQL Server that this is a copy of the data. It cannot be used as a full backup<o:p></o:p> point for differential backups, as the differential backups will be in line with the last “pure”<o:p></o:p> full backup. This option is ideal if you take weekly backups for dumping the data to a user test<o:p></o:p> region, as it will not affect the production backup process.<o:p></o:p> The only remaining option is for files or file groups where you can name the file or file group that<o:p></o:p> the backup is for. The preceding options do not change for files or file groups.<o:p></o:p> Try It Out: Backing Up the Database Using T-SQL for a Full and Differential Backup<o:p></o:p> 1. Open up a fresh Query Editor window. It doesn’t matter which database it is pointing to, as the BACKUP<o:p></o:p> DATABASE statement defines the database we will be working with.<o:p></o:p> 2. The T-SQL that we need for our full backup follows. Enter the code (keeping the name of where the backup is<o:p></o:p> located via the TO DISK option and the WITH NAME option all on one line). Notice that there are no options<o:p></o:p> defined for several of the options, as we are taking the default.<o:p></o:p> BACKUP DATABASE ApressFinancial<o:p></o:p> TO DISK = 'C:\Program Files\Microsoft SQL<o:p></o:p> Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\<o:p></o:p> ApressFinancial.bak'<o:p></o:p> WITH NAME = 'ApressFinancial-Full Database Backup',<o:p></o:p> SKIP,<o:p></o:p> NOUNLOAD,<o:p></o:p> STATS = 10<o:p></o:p> 3. Execute the code, and you will see results similar to those that follow. The main points to notice are the stats<o:p></o:p> messages that come out in approximations of 10 percentage points. It then lists the number of data pages backed<o:p></o:p> up and the number of log pages backed up. The on file part of the message details which file within the media<o:p></o:p> set the backup now is. In this case, this is the third backup. You will possibly see on file 2 unless a second<o:p></o:p> or subsequent backup has been taken in the meantime. The final message is the one of greatest interest, as it<o:p></o:p> shows that the backup was successful, and it displays the amount of time taken.<o:p></o:p> 13 percent processed.<o:p></o:p> 22 percent processed.<o:p></o:p> 31 percent processed.<o:p></o:p> 40 percent processed.<o:p></o:p> 54 percent processed.<o:p></o:p> 63 percent processed.<o:p></o:p> 72 percent processed.<o:p></o:p> 81 percent processed.<o:p></o:p> 90 percent processed.<o:p></o:p> Processed 176 pages for database 'ApressFinancial', file 'ApressFinancial'<o:p></o:p> on file 2.<o:p></o:p> 100 percent processed.<o:p></o:p> Processed 1 pages for database 'ApressFinancial', file 'ApressFinancial_log'<o:p></o:p> on file 2.<o:p></o:p> BACKUP DATABASE successfully processed 177 pages in 0.380 seconds (3.815 MB/sec).<o:p></o:p> CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 195<o:p></o:p> 4. Although useful to see, not many of the options were used. However, Figure 7-5 shows the next backup of the<o:p></o:p> database to be taken, which is a differential backup. We will not allow this backup to expire until 60 days have<o:p></o:p> elapsed, as shown in Figure 7-5. We will also be adding this differential backup to the full backup. At the end of<o:p></o:p> the screen shots, don’t back up, but click the Generate Script button which will place the T-SQL equivalent code<o:p></o:p> in a new query window where you will then be asked to run it.<o:p></o:p> Figure 7-5. Backing up a database (differential)<o:p></o:p> 5. Figure 7-6 shows that we are appending to the same media set as the full backup and that we have included<o:p></o:p> some reliability checking. Make sure your version matches the figure.<o:p></o:p> 6. The code that would be the equivalent of these two figures has been split in two. The first part is the differential<o:p></o:p> backup. Again, ensure that the TO DISK, DESCRIPTION, and NAME options are all on the same line of the Query<o:p></o:p> Editor window pane.<o:p></o:p> BACKUP DATABASE [ApressFinancial] TO DISK =<o:p></o:p> N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\<o:p></o:p> Backup\ApressFinancial.bak'<o:p></o:p> WITH DIFFERENTIAL ,<o:p></o:p> DESCRIPTION = 'This is a differential backup',<o:p></o:p> RETAINDAYS = 60, NOFORMAT, NOINIT,<o:p></o:p> MEDIANAME = N'ApressBackup',<o:p></o:p> NAME = N'ApressFinancial-Differential Database Backup',<o:p></o:p> NOSKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM<o:p></o:p> GO<o:p></o:p> 196 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE<o:p></o:p> Figure 7-6. Options for backing up a database (differential)<o:p></o:p> 7. The second part is where the reliability checking takes place. This is more complex T-SQL than we have covered,<o:p></o:p> so for the moment just trust that it works and that it does what it is supposed to. You will encounter this code<o:p></o:p> once more when looking at more complex T-SQL later in the book in Chapter 11. However, the basis of the code<o:p></o:p> is that a check is made in the msdb database to retrieve the last backup set that was taken, that we do a “restore”<o:p></o:p> of the database as verification only without actually restoring any data, and that the restore can complete successfully.<o:p></o:p> If it can’t verify the backup set or that the restore is OK, then you will get an error message.<o:p></o:p> declare @backupSetId as int<o:p></o:p> select @backupSetId = position<o:p></o:p> from msdb..backupset<o:p></o:p> where database_name=N'ApressFinancial'<o:p></o:p> and backup_set_id=<o:p></o:p> (select max(backup_set_id)<o:p></o:p> from msdb..backupset<o:p></o:p> where database_name=N'ApressFinancial' )<o:p></o:p> if @backupSetId is null<o:p></o:p> begin<o:p></o:p> raiserror(N'Verify failed. Backup information for database ''ApressFinancial''<o:p></o:p> not found.', 16, 1)<o:p></o:p> end<o:p></o:p> RESTORE VERIFYONLY FROM<o:p></o:p> CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 197<o:p></o:p> DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER<o:p></o:p> \MSSQL\Backup\ApressFinancial.bak'<o:p></o:p> WITH FILE = @backupSetId,<o:p></o:p> NOUNLOAD,<o:p></o:p> NOREWIND<o:p></o:p> 8. When the code is executed, you will see something like the results that follow. Again, they contain details of the<o:p></o:p> amount of data backed up as well as which file number on the media set the backup is.<o:p></o:p> 19 percent processed.<o:p></o:p> 39 percent processed.<o:p></o:p> 58 percent processed.<o:p></o:p> 78 percent processed.<o:p></o:p> 97 percent processed.<o:p></o:p> Processed 40 pages for database 'ApressFinancial',<o:p></o:p> file 'ApressFinancial' on file 3.<o:p></o:p> 100 percent processed.<o:p></o:p> Processed 1 pages for database 'ApressFinancial',<o:p></o:p> file 'ApressFinancial_log' on file 3.<o:p></o:p> BACKUP DATABASE WITH DIFFERENTIAL successfully processed 41 pages<o:p></o:p> in 0.433 seconds (0.774 MB/sec).<o:p></o:p> The backup set on file 3 is valid.<o:p></o:p> Transaction Log Backup Using T-SQL<o:p></o:p> You can back up not only the data, but also, and just as importantly, the transaction log for the database.<o:p></o:p> Just to recap, the transaction log is a file used by databases to log every transaction, including<o:p></o:p> DML actions such as rebuilding indexes. In other words, every data modification that has taken<o:p></o:p> place on any table within the database will be recorded within the transaction log. The transaction<o:p></o:p> log is then used in many different scenarios within a database solution, but where it is most useful,<o:p></o:p> from a database recovery point of view, is when a database crashes. In this case, the transaction log<o:p></o:p> can be used to move forward from the last data backup, using the transactions listed within the<o:p></o:p> transaction log.<o:p></o:p> If a database crash occurs, then the full and differential backups will only take you to the last<o:p></o:p> valid backup. For data entered since that point, the only way to restore the information is to then<o:p></o:p> “replay” the transactions that were committed and recorded as committed in the transaction log.<o:p></o:p> Any actions that were in progress at the time of the failure that were within a transaction that was still<o:p></o:p> in progress would have to be rerun from the start.<o:p></o:p> So, to clarify, if you were in the process of deleting data within a table and the power was switched<o:p></o:p> off, you would use your full and differential backups to restore the data. You would then use the<o:p></o:p> information within the transaction log to replay all successful transactions, but because the delete<o:p></o:p> had not been successful, the table would have all the data still within it.<o:p></o:p> Backing up the transaction log is a good strategy to employ when a large number of updates<o:p></o:p> occur to the data through the day. A transaction log backup should take place at set times throughout<o:p></o:p> the day depending on how large the transaction log has grown and how crucial it was to get your<o:p></o:p> system back up and running after any unexpected outage. When a transaction log is backed up, the<o:p></o:p> transaction log itself is logically shrunk in size so that the transaction log is kept small. It also gives<o:p></o:p> you point-in-time recoverability; this means that you can quickly restore to any time in the past<o:p></o:p> where the transaction was backed up.<o:p></o:p> 198 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE<o:p></o:p> Backing up a transaction log is similar to backing up a database. The full syntax is as follows and<o:p></o:p> really only differs from a database backup by using the LOG keyword instead of DATABASE and the<o:p></o:p> options NO_TRUNCATE and NORECOVERY/STANDBY:<o:p></o:p> BACKUP LOG { database_name | @database_name_var }<o:p></o:p> {<o:p></o:p> TO <backup_device> [ ,...n ]<o:p></o:p> [ [ MIRROR TO <backup_device> [ ,...n ] ] [ ...next-mirror ] ]<o:p></o:p> [ WITH<o:p></o:p> [ BLOCKSIZE = { blocksize | @blocksize_variable } ]<o:p></o:p> [ [ , ] { CHECKSUM | NO_CHECKSUM } ]<o:p></o:p> [ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ]<o:p></o:p> [ [ , ] DESCRIPTION = { 'text' | @text_variable } ]<o:p></o:p> [ [ ,] EXPIREDATE = { date | @date_var }<o:p></o:p> | RETAINDAYS = { days | @days_var } ]<o:p></o:p> | { COMPRESSION | NO_COMPRESSION }<o:p></o:p> [ [ , ] PASSWORD = { password | @password_variable } ]<o:p></o:p> [ [ , ] { FORMAT | NOFORMAT } ]<o:p></o:p> [ [ , ] { INIT | NOINIT } ]<o:p></o:p> [ [ , ] { NOSKIP | SKIP } ]<o:p></o:p> [ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]<o:p></o:p> [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]<o:p></o:p> [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]<o:p></o:p> [ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]<o:p></o:p> [ [ , ] NO_TRUNCATE ]<o:p></o:p> [ [ , ] { NORECOVERY | STANDBY = undo_file_name } ]<o:p></o:p> [ [ , ] { NOREWIND | REWIND } ]<o:p></o:p> [ [ , ] { NOUNLOAD | UNLOAD } ]<o:p></o:p> [ [ , ] STATS [ = percentage ] ]<o:p></o:p> [ [ , ] COPY_ONLY ]<o:p></o:p> ]<o:p></o:p> }<o:p></o:p> Now let’s look at the options not covered earlier when looking at backing up the database:<o:p></o:p> • LOG: Determines that we wish to produce a backup of the transaction log rather than a backup<o:p></o:p> of a database or files/file groups.<o:p></o:p> • NO_TRUNCATE: Doesn’t truncate the log after the backup. If the database is corrupt, using this<o:p></o:p> option will allow the backup to be attempted at least. Without this option, you will get an<o:p></o:p> error message.<o:p></o:p> • NORECOVERY | STANDBY: After the backup, the database will be in a state whereby it looks to<o:p></o:p> anyone trying to connect as if it is still being restored.<o:p></o:p> ■Note The LOG options NO_TRUNCATE and NORECOVER | STANDBY are used when the database is corrupt and<o:p></o:p> you wish to back up the transaction log prior to performing a restore.<o:p></o:p> CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 199<o:p></o:p> Try It Out: Backing Up the Transaction Log Using T-SQL<o:p></o:p> 1. In a Query Editor pane, enter the following T-SQL code. This backs up the transaction log to the same media set<o:p></o:p> as the full and differential backups. While developing and learning SQL Server, this is a valid scenario, and in<o:p></o:p> some production setups you may want to back up to the same place as your daily full backup. However, the<o:p></o:p> downside is that if you take several transaction log backups between each differential backup and full backup,<o:p></o:p> then SQL Server will have to “skip” these if they were not required as part of the restore operation. On a tape<o:p></o:p> drive, this could cause significant overhead. In this scenario, you would be better off saving the transaction log<o:p></o:p> files to a different media set.<o:p></o:p> BACKUP LOG ApressFinancial<o:p></o:p> TO DISK = 'C:\Program Files\Microsoft SQL<o:p></o:p> Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\<o:p></o:p> ApressFinancial.bak'<o:p></o:p> WITH NOFORMAT, NOINIT,<o:p></o:p> NAME = N'ApressFinancial-Transaction Log Backup',<o:p></o:p> SKIP, NOREWIND, NOUNLOAD,<o:p></o:p> STATS = 10<o:p></o:p> 2. This code replicates the Truncate the Transaction Log option, as shown in Figure 7-7. Execute the code.<o:p></o:p> Figure 7-7. Backing up a transaction log<o:p></o:p> 200 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE<o:p></o:p> 3. After execution, you should see output similar to the following, where the transaction log has been successfully<o:p></o:p> backed up and placed on file 4:<o:p></o:p> 100 percent processed.<o:p></o:p> Processed 4 pages for database 'ApressFinancial', file<o:p></o:p> 'ApressFinancial_log' on file 4.<o:p></o:p> BACKUP LOG successfully processed 4 pages in 0.135 seconds (0.235 MB/sec).<o:p></o:p> Restoring a Database<o:p></o:p> Now that the data has been backed up, what if you need to complete a restore? As has been mentioned,<o:p></o:p> there are two scenarios where a restore could be required: from a backup or when a media failure<o:p></o:p> occurs. The second type of restore is not one you wish to perform, but you could set it up by creating<o:p></o:p> a long-running transaction and then simply switching your computer off—not one of life’s greatest<o:p></o:p> ideas to do deliberately! This book therefore will not be demonstrating this option, and it is not really<o:p></o:p> for a beginner to attempt. However, I will discuss the concept within this section of the chapter. The<o:p></o:p> first option, a simple restore, is easy to replicate and perform, and this will be the option we will be<o:p></o:p> looking at.<o:p></o:p> You can choose between two means to restore the database: SQL Server Management Studio<o:p></o:p> and T-SQL. This is a scenario that you hope you will never perform in a production environment, but<o:p></o:p> it will happen. If you just need a restore within the development environment to remove test data<o:p></o:p> and get back to a stable predefined set of data to complete the testing, then this next section should<o:p></o:p> help you. It might also be that you do a weekly refresh of your user test region from a production<o:p></o:p> backup. Before completing the restore, let’s first modify the ApressFinancial database to prove that<o:p></o:p> the restore works, as there is no data within the database yet to prove the restore has worked by that<o:p></o:p> method. Keep in mind, however, that a restore will restore not only the data structures, but also the<o:p></o:p> data, the permissions, and other areas of the database not yet covered in the book—for example,<o:p></o:p> views, stored procedures, and so on.<o:p></o:p> Restoring Using SQL Server Management Studio<o:p></o:p> The restore demonstrated in the following example will be a complete database restore of our<o:p></o:p> ApressFinancial database. In other words, it will restore all the full and differential backups taken.<o:p></o:p> Try It Out: Restoring a Database<o:p></o:p> 1. Add a new column to the ShareDetails.Shares table using the following code in a Query Editor pane:<o:p></o:p> USE ApressFinancial<o:p></o:p> GO<o:p></o:p> ALTER TABLE ShareDetails.Shares<o:p></o:p> ADD DummyColumn varchar(30)<o:p></o:p> CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 201<o:p></o:p> 2. Once you have confirmed that the column has been added by looking in the Object Explorer, we can now use the<o:p></o:p> backup we finished earlier to restore the database, which will remove the changes we have just completed. From<o:p></o:p> the Object Explorer window, select the ApressFinancial database, right-click, and select Tasks ➤ Restore ➤<o:p></o:p> Database. This brings up the dialog box shown in Figure 7-8. It is possible to change the database you wish to<o:p></o:p> restore by changing the name in the To Database combo box or by simply overwriting the name that is there. The<o:p></o:p> second option, To a Point in Time, is used if you are restoring the database as well as rolling forward changes<o:p></o:p> recorded in the transaction log. This situation is similar to the scenario mentioned earlier about a power failure<o:p></o:p> or hard drive failure. As we are not doing this here, leave this option as it is. When taking a backup, details are<o:p></o:p> stored in msdb, but it is possible to restore a database from a backup that is not in msdb. For example, if you are<o:p></o:p> rebuilding a server due to corruption, and msdb was one of the databases corrupted, it is necessary to have the<o:p></o:p> option of finding a backup file and restoring from that instead. Or perhaps the last full backup taken is not the<o:p></o:p> backup you wish to restore. This might occur in a development scenario where you wish to restore to a backup<o:p></o:p> before major changes were done that you wish to remove. There would be no transaction log involved or required<o:p></o:p> to be involved, therefore restoring to a point in time would not be a valid scenario. This is where you could use<o:p></o:p> the From Device option. By selecting this option and clicking the ellipsis to the right, you can navigate to any old<o:p></o:p> backup files. Finally, you can click which of the items in the backup you wish to restore. The default is all files to<o:p></o:p> be selected, as you can see in Figure 7-8. The settings shown will give us a backup that is as fresh as possible<o:p></o:p> (the Most Recent Possible value for the To a Point in Time setting).<o:p></o:p> Figure 7-8. Restoring a database—General tab<o:p></o:p> 202 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE<o:p></o:p> 3. Moving to the Options page, shown in Figure 7-9, there are a number of points to consider:<o:p></o:p> • Overwrite the Existing Database: This is the most likely option to be enabled for a normal restore. You<o:p></o:p> would disable it if you wished to create a restore on the same server but where the restore would alter the<o:p></o:p> name of the database. You cannot have any items not backed up within the transaction log; if you do, the<o:p></o:p> restore will fail.<o:p></o:p> • Preserve the Replication Settings: A more advanced option for when a database is sending changes to<o:p></o:p> another database. For the time being, leave this option disabled.<o:p></o:p> • Prompt Before Restoring Each Backup: If you wish a prompt message before each restore file is activated,<o:p></o:p> then select this. This is ideal if you need to swap media over.<o:p></o:p> • Restrict Access to the Restored Database: You may wish to check out the database after a restore to ensure<o:p></o:p> the restore is what you wish, or in a production environment to run further checks on the database integrity.<o:p></o:p> • Restore the Database Files As: This grid allows you to move or rename the MDF and LDF files.<o:p></o:p> • Leave the Database Ready to Use: This option defines whether users can immediately connect and work<o:p></o:p> with the data after the restore. If a transaction is in progress, such as deleting rows within a table, then<o:p></o:p> the connection could occur once the deletion has been rolled back and the table is back in its “original” state.<o:p></o:p> • Leave the Database Non-operational: With this option, you can indicate that the database has been partially<o:p></o:p> restored and you are unsure if you need to perform additional actions. If a transaction is in progress, such<o:p></o:p> as deleting a table, then whatever has been deleted will still be deleted and will not be rolled back.<o:p></o:p> • Leave the Database in Read-Only Mode: A combination of the first two options. If a transaction is in progress,<o:p></o:p> such as deleting rows in a table, then the connection could occur once the deletion has been rolled back.<o:p></o:p> However, the changes are also kept in a separate file, so that any of these actions that have been rolled<o:p></o:p> back can be reapplied. This might happen if there are several actions within a transaction and some can<o:p></o:p> be reapplied.<o:p></o:p> CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 203<o:p></o:p> Figure 7-9. Restoring a database—Options tab<o:p></o:p> 4. Once you have the option settings you require, a quick click of OK performs the restore. You should see the<o:p></o:p> message in Figure 7-10. If you then move back to the database after clicking OK, you will see that the column<o:p></o:p> we just added has been “removed” when you look in Object Explorer.<o:p></o:p> Figure 7-10. Restore successful<o:p></o:p> 204 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE<o:p></o:p> Restoring Using T-SQL<o:p></o:p> Using the wizard is a pretty fast way to restore a database, and when under pressure, it may even<o:p></o:p> be the best way forward. However, it is not the most flexible way of performing a restore, as some<o:p></o:p> options that are available via T-SQL are not in this wizard. Some of these options were covered when<o:p></o:p> we performed a backup, such as performing checksums when transferring data from the media<o:p></o:p> device back to the database or unloading media at the end of the restore. If there is also a password<o:p></o:p> on the backup medium, this option is not available within the wizard, but you can use passwords<o:p></o:p> with T-SQL. Being comfortable building a restore via T-SQL is important in becoming a more proficient<o:p></o:p> and professional developer or administrator.<o:p></o:p> The syntax for restoring a database is similar to that for database backups. After looking at the<o:p></o:p> syntax, we will then go through the options you will not be familiar with.<o:p></o:p> RESTORE DATABASE { database_name | @database_name_var }<o:p></o:p> [ FROM <backup_device> [ ,...n ] ]<o:p></o:p> [ WITH<o:p></o:p> [ { CHECKSUM | NO_CHECKSUM } ]<o:p></o:p> [ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]<o:p></o:p> [ [ , ] FILE = { file_number | @file_number } ]<o:p></o:p> [ [ , ] KEEP_REPLICATION ]<o:p></o:p> [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]<o:p></o:p> [ [ , ] MEDIAPASSWORD = { mediapassword |<o:p></o:p> @mediapassword_variable } ]<o:p></o:p> [ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]<o:p></o:p> [ ,...n ]<o:p></o:p> [ [ , ] PASSWORD = { password | @password_variable } ]<o:p></o:p> [ [ , ] { RECOVERY | NORECOVERY | STANDBY =<o:p></o:p> {standby_file_name | @standby_file_name_var }<o:p></o:p> } ]<o:p></o:p> [ [ , ] REPLACE ]<o:p></o:p> [ [ , ] RESTART ]<o:p></o:p> [ [ , ] RESTRICTED_USER ]<o:p></o:p> [ [ , ] { REWIND | NOREWIND } ]<o:p></o:p> [ [ , ] STATS [ = percentage ] ]<o:p></o:p> [ [ , ] { STOPAT = { date_time | @date_time_var }<o:p></o:p> | STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }<o:p></o:p> [ AFTER datetime ]<o:p></o:p> | STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }<o:p></o:p> [ AFTER datetime ]<o:p></o:p> } ]<o:p></o:p> [ [ , ] { UNLOAD | NOUNLOAD } ]<o:p></o:p> ]<o:p></o:p> The options we have not yet covered are as follows:<o:p></o:p> • KEEP_REPLICATION: When working with replication, consider using this option. Replication is<o:p></o:p> when changes completed in one database are automatically sent to another database. The<o:p></o:p> most common scenario is when you have a central database replicating changes to satellite<o:p></o:p> databases, and possibly vice versa.<o:p></o:p> • MOVE: When completing a restore, the MDF and LDF files that are being restored have to be placed<o:p></o:p> where they were backed up from. However, by using this option, you can change that location.<o:p></o:p> • RECOVERY | NORECOVERY | STANDBY: These three options are the same, and in the same order, as<o:p></o:p> their counterparts (in parentheses) in the wizard:<o:p></o:p> CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 205<o:p></o:p> • RECOVERY (Leave the Database Ready to Use): This option defines that after the restore is<o:p></o:p> finished, users can immediately connect to and work with the data. If a transaction is in<o:p></o:p> progress, such as updating rows in a table, then not until the updates have been rolled back<o:p></o:p> and therefore the table is back in its “original” state will connections to the database<o:p></o:p> be allowed.<o:p></o:p> • NORECOVERY (Leave the Database Non-operational): With this option, you are indicating<o:p></o:p> that the database has been partially restored, and you are unsure whether you need to<o:p></o:p> perform additional actions. If a transaction is in progress, such as inserting rows in a table,<o:p></o:p> then the insertions will not be rolled back. This allows additional restores to get to a specific<o:p></o:p> point in time.<o:p></o:p> • STANDBY (Leave the Database in Read-Only Mode): A combination of the first two options.<o:p></o:p> If a transaction is in progress, such as deleting rows in a table, then the deletion will be<o:p></o:p> rolled back. However, the changes are also in a separate file, so that any of these actions<o:p></o:p> that have been rolled back can be reapplied. This might happen if several actions occurred<o:p></o:p> within a transaction and some can be reapplied.<o:p></o:p> • REPLACE: This works the same as the wizard option Overwrite the Existing Database.<o:p></o:p> • RESTART: If a restore is stopped partway through, then using this option will restart the restore<o:p></o:p> at the point it was stopped.<o:p></o:p> • RESTRICTED_USER: Use this with the RECOVERY option to only allow users in specific restricted<o:p></o:p> groups to access the database. Use this to allow further checking by a database owner, or by<o:p></o:p> the dbowner, dbcreator, or sysadmin roles.<o:p></o:p> • STOPAT | STOPATMARK | STOPBEFOREMARK: Used to specify a specific date and time at which to stop<o:p></o:p> the restore.<o:p></o:p> The syntax for restoring the transaction log is exactly the same, with the only difference being<o:p></o:p> the definition: you are completing a LOG rather than a DATABASE restore:<o:p></o:p> RESTORE LOG { database_name | @database_name_var }<o:p></o:p> <file_or_filegroup_or_pages> [ ,...f ]<o:p></o:p> [ FROM <backup_device> [ ,...n ] ]<o:p></o:p> [ WITH<o:p></o:p> [ { CHECKSUM | NO_CHECKSUM } ]<o:p></o:p> [ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]<o:p></o:p> [ [ , ] FILE = { file_number | @file_number } ]<o:p></o:p> [ [ , ] KEEP_REPLICATION ]<o:p></o:p> [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]<o:p></o:p> [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]<o:p></o:p> [ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]<o:p></o:p> [ ,...n ]<o:p></o:p> [ [ , ] PASSWORD = { password | @password_variable } ]<o:p></o:p> [ [ , ] { RECOVERY | NORECOVERY | STANDBY =<o:p></o:p> {standby_file_name | @standby_file_name_var } }<o:p></o:p> ]<o:p></o:p> [ [ , ] REPLACE ]<o:p></o:p> [ [ , ] RESTART ]<o:p></o:p> [ [ , ] RESTRICTED_USER ]<o:p></o:p> [ [ , ] { REWIND | NOREWIND } ]<o:p></o:p> [ [ , ] STATS [=percentage ] ]<o:p></o:p> [ [ , ] { STOPAT = { date_time | @date_time_var }<o:p></o:p> | STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }<o:p></o:p> [ AFTER datetime ]<o:p></o:p> 206 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE<o:p></o:p> | STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }<o:p></o:p> [ AFTER datetime ]<o:p></o:p> } ]<o:p></o:p> [ [ , ] { UNLOAD | NOUNLOAD } ]<o:p></o:p> ]<o:p></o:p> Try It Out: Restoring Using T-SQL<o:p></o:p> 1. Open up an empty Query Editor pane and enter the following code. This will add the column that we want to see<o:p></o:p> “removed” after a restore.<o:p></o:p> USE ApressFinancial<o:p></o:p> GO<o:p></o:p> ALTER TABLE ShareDetails.Shares<o:p></o:p> ADD DummyColumn varchar(30)<o:p></o:p> 2. Now replace this code with the restore code that follows. Don’t execute any of the code just yet, as this piece of<o:p></o:p> code is the first part only. Recall that when performing the backups, FILE 2 was the FULL backup taken. This<o:p></o:p> is what the first restore will do.<o:p></o:p> ■Note Ensure that the FROM DISK option is all on one line. Also recall that FILE = 2 may be FILE = 3 or any<o:p></o:p> other number, depending on the backups taken, and this may be the case with different file numbers as you progress.<o:p></o:p> USE Master<o:p></o:p> GO<o:p></o:p> RESTORE DATABASE [ApressFinancial]<o:p></o:p> FROM DISK = 'C:\Program Files\Microsoft SQL<o:p></o:p> Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\<o:p></o:p> ApressFinancial.bak' WITH FILE = 2,<o:p></o:p> NORECOVERY, NOUNLOAD, REPLACE, STATS = 10<o:p></o:p> GO<o:p></o:p> 3. Continue the code with the second part of the restore, which will be the differential backup restore. This uses<o:p></o:p> FILE 3 from the backup set.<o:p></o:p> RESTORE DATABASE [ApressFinancial]<o:p></o:p> FROM DISK = 'C:\Program Files\Microsoft SQL<o:p></o:p> Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\<o:p></o:p> ApressFinancial.bak' WITH FILE = 4,<o:p></o:p> NORECOVERY, NOUNLOAD, REPLACE, STATS = 10<o:p></o:p> GO<o:p></o:p> 4. The final part of the restore operation is to restore the transaction log file. Once all this code is in, you can run it all.<o:p></o:p> RESTORE LOG [ApressFinancial]<o:p></o:p> FROM DISK = 'C:\Program Files\Microsoft SQL<o:p></o:p> Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\<o:p></o:p> ApressFinancial.bak' WITH FILE = 5,<o:p></o:p> NOUNLOAD, STATS = 10<o:p></o:p> 5. Once the code has fully executed, you should see results similar to those listed here:<o:p></o:p> CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 207<o:p></o:p> 13 percent processed.<o:p></o:p> 22 percent processed.<o:p></o:p> 31 percent processed.<o:p></o:p> 40 percent processed.<o:p></o:p> 54 percent processed.<o:p></o:p> 63 percent processed.<o:p></o:p> 72 percent processed.<o:p></o:p> 81 percent processed.<o:p></o:p> 90 percent processed.<o:p></o:p> 100 percent processed.<o:p></o:p> Processed 176 pages for database 'ApressFinancial', file 'ApressFinancial'<o:p></o:p> on file 2.<o:p></o:p> Processed 1 pages for database 'ApressFinancial',<o:p></o:p> file 'ApressFinancial_log' on file 2.<o:p></o:p> RESTORE DATABASE successfully processed 177 pages in 0.284 seconds<o:p></o:p> (5.105 MB/sec).<o:p></o:p> 24 percent processed.<o:p></o:p> 48 percent processed.<o:p></o:p> 72 percent processed.<o:p></o:p> 97 percent processed.<o:p></o:p> 100 percent processed.<o:p></o:p> Processed 32 pages for database 'ApressFinancial', file 'ApressFinancial'<o:p></o:p> on file 3.<o:p></o:p> Processed 1 pages for database 'ApressFinancial',<o:p></o:p> file 'ApressFinancial_log' on file 3.<o:p></o:p> RESTORE DATABASE successfully processed 33 pages in 0.066 seconds<o:p></o:p> (4.088 MB/sec).<o:p></o:p> 100 percent processed.<o:p></o:p> Processed 0 pages for database 'ApressFinancial', file 'ApressFinancial'<o:p></o:p> on file 4.<o:p></o:p> Processed 4 pages for database 'ApressFinancial',<o:p></o:p> file 'ApressFinancial_log' on file 4.<o:p></o:p> RESTORE LOG successfully processed 4 pages in 0.013 seconds (2.441 MB/sec).<o:p></o:p> We can now move back to the ShareDetails.Shares table and check that the column added has now been removed.<o:p></o:p> You may have to perform a refresh within Object Explorer first to see the changes.<o:p></o:p> Restoring a database in production will in most instances take place under pressure, as the database will have become<o:p></o:p> corrupt or been inadvertently damaged. The production system is obviously down and not working, and we have irate<o:p></o:p> users wanting to know how long before the system will be up. This is hopefully the worst-case scenario, but it is that sort<o:p></o:p> of level of pressure that we will be working under when we have to restore the database. Therefore, having the correct<o:p></o:p> backup strategy for your organization based on full, differential, and transaction log backups is crucial. Full database<o:p></o:p> backups for a system that requires high availability so that the restore takes the least amount of time may be what you need.<o:p></o:p> Detaching and Attaching a Database<o:p></o:p> Now that we can back up and restore a database, we have other methods available for dealing with<o:p></o:p> the database. There may be a time in the life of our SQL Server database when we have to move it<o:p></o:p> from one server to another, or in fact just from one hard drive to another. For example, perhaps we<o:p></o:p> currently have ApressFinancial on our C drive, and this is getting full, so we would like to move our<o:p></o:p> database to another hard drive. Or perhaps we are moving from an old slower server to a new faster<o:p></o:p> 208 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE<o:p></o:p> server or a server on a better network. By detaching and reattaching the database, we can do this<o:p></o:p> simply and easily.<o:p></o:p> I would like to make a couple of points here; they may seem straightforward and really obvious,<o:p></o:p> but better to mention them than cause problems at a later stage. First of all, no updates can be occurring,<o:p></o:p> no jobs can be running, and no users can be attached. Secondly, just in case, take a full backup<o:p></o:p> before moving the database. This may add time to the process, but it is better to be safe than sorry.<o:p></o:p> Ensure that where you are moving the database to has enough disk space, not only for the move, but<o:p></o:p> also for expected future growth; otherwise, you will be moving your database twice. You should not<o:p></o:p> attach your database to a server without immediately completing a backup on the new server afterward;<o:p></o:p> this way, you can ensure that the databases are protected in their new state.<o:p></o:p> Detaching a database physically removes the details from the SQL Server master and msdb databases,<o:p></o:p> but does not remove the files from the disk that it resides on. However, detaching the database<o:p></o:p> from SQL Server then allows you to safely move, copy, or delete the files that make up the database,<o:p></o:p> if you so desire. This is the only way that a database should be physically removed from a server for<o:p></o:p> moving it.<o:p></o:p> Detaching and Attaching Using SQL Server Management Studio<o:p></o:p> We’ll start by using SSMS to detach and attach a database.<o:p></o:p> Try It Out: Detaching a Database<o:p></o:p> 1. First of all, it is necessary to ensure that nobody is logged in to the database, and even if someone is, that<o:p></o:p> the user is not doing any updates. For the moment, I want you to ignore this and to have a connection. Ensure<o:p></o:p> that SQL Server Management Studio is running and that there is a Query Editor pane with a connection to the<o:p></o:p> ApressFinancial database. Find the ApressFinancial database in Object Explorer and ensure that is<o:p></o:p> selected. Right-click and select Tasks ➤ Detach.<o:p></o:p> 2. This brings up the Detach Database dialog box for the ApressFinancial database, as shown in Figure 7-11.<o:p></o:p> We haven’t removed all the connected users, so you can do this by selecting the Drop Connections check box.<o:p></o:p> The second option, Update Statistics, means that the SQL Server statistics for indexes and so on will be updated<o:p></o:p> before the database is detached. The information is stored separately from the other data files in SQL Server, so<o:p></o:p> selecting this option ensures that when the database is detached that the files are not lost and therefore don’t<o:p></o:p> need re-creating. The status is Not Ready due to the message indicating that there is still “2 Active connection(s).”<o:p></o:p> Although you only have one Query Editor open, the second connection is for the T-SQL IntelliSense.<o:p></o:p> 3. In this example select the Drop Connections box. However, in a production environment, this could be very dangerous,<o:p></o:p> so you should not select it without some thought. You can then click OK to finish detaching the database.<o:p></o:p> That’s it. The database is detached, is no longer part of SQL Server, and is ready to be removed or even deleted. If you<o:p></o:p> check the Object Explorer in SQL Server Management Studio, you will see that the database is no longer listed.<o:p></o:p> Detaching a database, although seemingly a simple and innocuous operation, has the potential to be fraught with problems and<o:p></o:p> worries. As the example demonstrated, ensuring that there are no users attached to the database at the time of detaching<o:p></o:p> is not as easy as it first may seem. Setting up the database options to eliminate connections or to stop updates is only possible<o:p></o:p> once everyone has been removed from connections to the database. There is no easy way of removing connections<o:p></o:p> safely, as you never know what an application with a connection to the database is doing. You could remove a connection<o:p></o:p> CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 209<o:p></o:p> that is in the middle of processing. If you are going down the route of detaching the database, though, there is an obvious<o:p></o:p> reason to do this, such as moving servers and removing the database, so you would have a plan of action to do this. Users<o:p></o:p> would have been notified days or weeks in advance, and the database owner would have coordinated a date and time<o:p></o:p> when nobody should be connected. Also, the database owner would be around if there were any problems, and he or she<o:p></o:p> could make the decision to kill any connections left hanging around.<o:p></o:p> Figure 7-11. Detaching a database<o:p></o:p> Detaching the database is a process that removes entries within the SQL Server system tables to inform SQL Server that<o:p></o:p> this database is no longer within this instance of SQL Server and therefore cannot be used. It is as simple as that. If you<o:p></o:p> are removing the database completely, then you will need to delete the files from the directory they were created in.<o:p></o:p> It is possible to detach the database using a system stored procedure, although this does not let you kill the connections.<o:p></o:p> This has to be done via a T-SQL command.<o:p></o:p> We need to reattach the database before being able to demonstrate this, so let’s do that now. This would occur on our new<o:p></o:p> SQL Server instances after physically moving the files.<o:p></o:p> 210 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE<o:p></o:p> Try It Out: Attaching a Database<o:p></o:p> 1. Within Object Explorer, highlight the Databases node, right-click, and select Attach.<o:p></o:p> 2. This brings up the Attach Databases dialog box, shown in Figure 7-12. To add a database, click Add.<o:p></o:p> Figure 7-12. Options for attaching a database<o:p></o:p> 3. This brings up the Locate Database Files explorer, shown in Figure 7-13. You can use this like other Windows<o:p></o:p> Explorers to locate where your database MDF files are. Once you find the database you want to reattach, highlight<o:p></o:p> it and then click OK.<o:p></o:p> CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 211<o:p></o:p> Figure 7-13. Locating the database to attach<o:p></o:p> 4. This brings you back to the Attach Databases dialog box with the details filled in, as you see in Figure 7-14. Take<o:p></o:p> a moment to look over the information in this dialog box. Any problems will be detailed in the Messages column.<o:p></o:p> It is possible to attach more than one database, but it is best to do databases one at a time.<o:p></o:p> 5. This then leaves us to click OK to reattach the database. Moving to Object Explorer, you should see your database<o:p></o:p> at the bottom of the list, where it will remain until the explorer is refreshed.<o:p></o:p> Attaching a database involves informing SQL Server of the name and the location of the data files and the transaction log<o:p></o:p> files. This data can be placed anywhere on a computer, but it is recommended you place the data in a sensible location.<o:p></o:p> For example, the folders tempfiles or tobedeleted sport extreme names, but do demonstrate the unsuitability that should<o:p></o:p> be avoided.<o:p></o:p> When moving the data from one physical server to another, the data does not need to be in a subdirectory of Microsoft SQL<o:p></o:p> Server installation found under Program Files. In fact, in production environments, this is the last place you would locate<o:p></o:p> the data. You would generally want to keep these files away from any program files or the pagefile.sys file, because SQL<o:p></o:p> Server’s performance can be maximized when these files are separated. However, for the purpose of this book, placing the<o:p></o:p> data in the DATA directory under the instance of SQL Server is perfectly valid and acceptable.<o:p></o:p> Once the two data files have been copied, it is a simple process of using a couple of mouse clicks to attach these files into<o:p></o:p> the instance. What happens in the background, very basically, is that SQL Server takes the name of the database and the<o:p></o:p> location of the data files and places them into internal tables that are used to store information about databases. It then<o:p></o:p> scans the data files to retrieve information, such as the names of the tables, to populate the system tables where necessary.<o:p></o:p> 212 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE<o:p></o:p> Figure 7-14. Database located, preparing to attach<o:p></o:p> The main point to keep in mind is the database owner (see Chapters 1, 2, and 5 ). It is just as important to use a valid database<o:p></o:p> owner and not the sa login when attaching a database as it is when creating a database. The database, when it is<o:p></o:p> attached, will be given the owner of the login attaching the database.<o:p></o:p> Detaching and Attaching Using T-SQL<o:p></o:p> Detaching and attaching a database is an ideal way to move a database from one server to another<o:p></o:p> as part of an overall solution. It’s clean and simple and ideal if you are rolling out a “base” database<o:p></o:p> to many client sites, but it’s not the only way of doing it. Detaching a database is simply removing<o:p></o:p> it logically from a server, but keeping the physical files. This then allows these files to be moved to<o:p></o:p> anywhere, from another hard drive to a DVD, for further copying to a client computer if need be, and<o:p></o:p> then reattaching the database at the other end.<o:p></o:p> Detaching a database removes entries from the master and msdb databases. The physical backup<o:p></o:p> files will still be there, so if you do need to complete a restore after a detach and reattach, then you<o:p></o:p> can use the From Device option in the Restore Wizard to define the full location in the RESTORE T-SQL<o:p></o:p> command to get to those files.<o:p></o:p> CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 213<o:p></o:p> ■Note Detaching a database can only be done by a member of the db_owner role.<o:p></o:p> sp_detach_db [ @dbname= ] 'dbname'<o:p></o:p> [ , [ @skipchecks= ] 'skipchecks' ]<o:p></o:p> [ , [ @KeepFulltextIndexFile= ] 'KeepFulltextIndexFile' ]<o:p></o:p> The options are straightforward, with each being optional. If they are not supplied, then the<o:p></o:p> default value is mentioned within the following bulleted list:<o:p></o:p> • dbname: The name of the database to detach. If this option is missed, then no database will be<o:p></o:p> detached.<o:p></o:p> • skipchecks: NULL (the default) will update statistics. true will skip the updating of statistics.<o:p></o:p> • KeepFulltextIndexFile: true (the default) will keep all the full text index files that have been<o:p></o:p> generated with this database.<o:p></o:p> ■Note Full text index files are special files that hold information about data set up for full-text searching, which<o:p></o:p> is an area outside the scope of this book. Basically, full-text searching gives the ability to search on all text in a<o:p></o:p> column or multiple columns of data, and is also functionality used by search engines.<o:p></o:p> You might be expecting that you would use a stored procedure called sp_attach_db to reattach<o:p></o:p> the database. This command does exist, but it will be made obsolete in future versions of SQL Server.<o:p></o:p> The correct syntax is a “specialized” CREATE DATABASE command:<o:p></o:p> CREATE DATABASE database_name<o:p></o:p> ON <filespec> [ ,...n ]<o:p></o:p> FOR { ATTACH [ WITH <service_broker_option> ]<o:p></o:p> | ATTACH_REBUILD_LOG }<o:p></o:p> The syntax is easy to follow. The first option, ON, specifies the name of the primary database file<o:p></o:p> to attach, which has the mdf suffix. We will ignore the second option, <service_broker_option>, as<o:p></o:p> this is for a more advanced database.<o:p></o:p> The third option, ATTACH_REBUILD_LOG, is for situations where you wish to attach a database but<o:p></o:p> at least one transaction log file is missing. Specifying this option rebuilds the transaction log. No<o:p></o:p> database can be attached when SQL Server believes that there are missing files. If you do use this<o:p></o:p> option, then you will lose the full, differential, and transaction log backup chains that exist on SQL<o:p></o:p> Server, so complete a full backup after attaching to reestablish the backup baseline. This option<o:p></o:p> tends to be used when you deliberately wish to lose the transaction log file, such as a read-only<o:p></o:p> version of the database for reporting purposes.<o:p></o:p> ■Note If you receive any error messages, then reattach all files associated with the database, not just the main<o:p></o:p> primary file.<o:p></o:p> We can now detach and reattach ApressFinancial.<o:p></o:p> 214 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE<o:p></o:p> Try It Out: Detaching and Reattaching a Database<o:p></o:p> 1. The first test we will do is to try to detach ApressFinancial while there are still active connections so that we<o:p></o:p> can see what happens. Open up a Query Editor pane and point it to ApressFinancial database. Then open a<o:p></o:p> second pane and enter the sp_detach_db code as follows. Once you have done so, execute the code. Take note<o:p></o:p> that we are explicitly moving this connection to a “safe” system database, away from the database we wish<o:p></o:p> to detach.<o:p></o:p> USE master<o:p></o:p> GO<o:p></o:p> sp_detach_db 'ApressFinancial'<o:p></o:p> 2. The results you will see will be similar to the following:<o:p></o:p> Msg 3703, Level 16, State 2, Line 1<o:p></o:p> Cannot detach the database 'ApressFinancial' because it is currently in use<o:p></o:p> 3. Close the Query Editor pane opened earlier and any other Query Editor panes that have connections pointing to<o:p></o:p> ApressFinancial and then try rerunning the code again. This time you should see the following message:<o:p></o:p> Command(s) completed successfully.<o:p></o:p> 4. You can also achieve the same result within the code and without needing to close your panes. If you run the<o:p></o:p> following code and move back to a pane that had a connection, you will be presented with a reconnection<o:p></o:p> pane. Once connected, if your default database is not ApressFinancial, then you won’t be able to change to<o:p></o:p> that database until you move out of single-user mode or use the pane that had the following code within it:<o:p></o:p> ALTER DATABASE ApressFinancial<o:p></o:p> SET SINGLE_USER WITH ROLLBACK IMMEDIATE<o:p></o:p> 5. Change SINGLE_USER to MULTI_USER once you test the following code:<o:p></o:p> ALTER DATABASE ApressFinancial<o:p></o:p> SET MULTI_USER<o:p></o:p> 6. Now that the database has been detached, we need to reattach it, simulating a move to a new server. Enter the<o:p></o:p> following code in the same Query Editor pane. Replace the FILENAME parameters with the path to where your<o:p></o:p> database is located and ensure that the path is all on one line.<o:p></o:p> CREATE DATABASE ApressFinancial<o:p></o:p> ON (FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\<o:p></o:p> Data\ApressFinancial.MDF')<o:p></o:p> FOR ATTACH<o:p></o:p> 7. After executing the code, you should see the following message:<o:p></o:p> Command(s) completed successfully.<o:p></o:p> You have now successfully detached and reattached the database.<o:p></o:p> CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 215<o:p></o:p> Producing SQL Script for the Database<o:p></o:p> This section demonstrates a different method of backing up the structure of the database and the<o:p></o:p> tables and indexes contained within it by using T-SQL commands to complete this.<o:p></o:p> ■Note Only the structure will be generated as T-SQL commands; no data will be backed up—only the schema<o:p></o:p> that is needed to re-create the actual database can be scripted here.<o:p></o:p> The usefulness of this procedure is limited and is really only helpful for keeping structure backups<o:p></o:p> or producing an empty database, but it is useful to know rather than going through the process of<o:p></o:p> copying the database with all the data when the data is not required.<o:p></o:p> This method tends to be used to keep the structure within a source repository such as Visual<o:p></o:p> SourceSafe. It is also useful for setting up empty databases when moving from development to testing or<o:p></o:p> into production.<o:p></o:p> Try It Out: Producing the Database SQL<o:p></o:p> 1. Ensure that SQL Server Management Studio is running and that you have expanded the server so that you can<o:p></o:p> see the ApressFinancial database. Right-click, and select Tasks ➤ Generate Scripts. This brings up the<o:p></o:p> wizard shown in Figure 7-15 that allows the database to be scripted. Every attached database will be listed.<o:p></o:p> Select ApressFinancial and click Next.<o:p></o:p> ■Note You can select the check box at the bottom of the screen, which will script all the objects if you wish. This<o:p></o:p> will enable the Finish button so that you can go straight to the end.<o:p></o:p> Figure 7-15. Scripting—selecting the database<o:p></o:p> 216 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE<o:p></o:p> 2. On the second screen are a number of options about the scripting. Take a moment to look them over. Most of these<o:p></o:p> options should be clear to you from the setup options we have covered in setting up the database so far. A bulleted list<o:p></o:p> at the end of the example clarifies the options for you. Figure 7-16 shows the default settings. Click Next.<o:p></o:p> Figure 7-16. Options for the script<o:p></o:p> 3. As shown in Figure 7-17, you are presented with a list of objects that you could script. At the bottom of the dialog<o:p></o:p> is a Select All option. Press this, as we want to script everything. Once you have the options you wish to script,<o:p></o:p> click Next. You will then be taken through each object group one at a time. Within each screen there will be a list<o:p></o:p> of possible objects you wish to script. For example, Figure 7-18 demonstrates what you will see when you get to<o:p></o:p> the dialog for the stored procedures. Every stored procedure within the database will be listed. As you go through<o:p></o:p> each dialog, click Select All and click Next.<o:p></o:p> ■Note The Script Statistics option significantly increases the time taken to generate the script. Leave this option<o:p></o:p> off in most cases; it is really only useful when moving from a user test environment that is very similar to how the<o:p></o:p> system will work in production.<o:p></o:p> CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 217<o:p></o:p> Figure 7-17. Options selected for scripting<o:p></o:p> Figure 7-18. Scripting for every stored procedure<o:p></o:p> 4. After you have been presented with the dialogs for all the objects in the database of the areas selected in<o:p></o:p> Figure 7-16, where you should have selected every object, you are now presented with the screen shown<o:p></o:p> in Figure 7-19. This allows you specify how the script should be saved. There are three possibilities. Choose to<o:p></o:p> script to a new Query Editor and then select Next.<o:p></o:p> 218 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE<o:p></o:p> Figure 7-19. Where to store the script<o:p></o:p> 5. This brings you to a summary screen, shown in Figure 7-20, where you can expand what has been selected. You<o:p></o:p> may find that this screen is not of much use, as there are so few screens within this wizard. However, you can<o:p></o:p> use it for categorizing what objects are to be scripted. Take a moment to investigate this screen.<o:p></o:p> Figure 7-20. Script summary<o:p></o:p> 6. Click Finish. The wizard will start to generate the script. At the end, you will see a summary of how the script<o:p></o:p> production went. Any errors will be within the Message column on the right, as shown in Figure 7-21.<o:p></o:p> CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 219<o:p></o:p> Figure 7-21. Generating the script<o:p></o:p> The options available to you within the wizard are detailed here:<o:p></o:p> • Append to File: If you set this to true, then SQL Server will append the script to the file selected instead of overwriting<o:p></o:p> it.<o:p></o:p> • <span style="font-size: 8.5pt; font-family: "Verdana","sans-serif&