Note: This document is based on the original spoken Support WebCast transcript. It has been edited for clarity.

Heidi Moeller: Hello. Welcome to the Microsoft Support WebCast. I'd like to thank all of you for joining us today. Our topic is "SQL Server^™ 2000 Database Recovery, Backup, and Restore." Our presenter will be Reinaldo Kibel. I am Heidi Moeller and I will be your host for today's session. We will start this session with Reinaldo's presentation and follow that up with a question-and-answer period when the presentation is complete.

The Q&A portion of the Support WebCast is intended to encourage further discussions of the support WebCast topic. However, one-on-one products support issues are outside the scope of what we are able to address during the Support WebCast. If you need technical assistance, please submit an instance on the Web, or call Microsoft Product Support Services to speak with a Support Professional.

I'd like to now take a moment to introduce Reinaldo. Reinaldo Kibel is a SQL Server Support Professional. He has developed content and training for Microsoft^® SQL Server^™ 6.5 and SQL Server 7.0 for delivery to Microsoft Support Professionals. Reinaldo currently works on the server down team for SQL and develops content for the recent SQL Server 2000 release. Reinaldo received his Master Degree in Computer Science from the University of Texas in Dallas in 1997. He has designed and developed computer software applications and hardware since he started working on his Electronics Engineering degree back in 1984. Thank you so much for joining us today, Reinaldo. Let's go ahead and get started.

Reinaldo Kibel: So we are going to discuss SQL Server 2000 Recovery. Go to the slide 2. What are we going to cover today? One of the first things I was going to mention is restoring and how important it is. Then I am going to briefly browse through some of the Microsoft SQL Server 6.5 backup mechanism, and SQL Server 7.0 backup mechanism. Then the presentation is going to go into more detail on all the new SQL Server 2000 Recovery, Backup, and Restore enhancements and the differences between the Recovery models.

Microsoft SQL Server 2000 was released on August 9, 2000, and is the first product that uses the Microsoft Net platform, so we're very excited about it.

Going to slide 3. Being a Support Professional, one of the most common issues that we've seen here is, with databases that go down or servers that go down, they can't come back up. If they would had a good backup, that would solve the problem. So this time, instead of having a take home at the end, I'm going to have a take home in the beginning. If you don't want to watch anything else, this is what you want to take with you. Restore. It's very, very important. As I say in the bottom there, "A backup procedure can only be validated after a successful restore."

Please make sure that after you do a backup, periodically you test your restore. The restore has to be tested to make sure that it works. Get another server, get on the same server, do whatever you're going to do, but get another server, and then restore the backup. to that other server and make sure it does work. Then, check some data, run some DBCC, and do some consistency checks. Make sure it does work.

Another important thing about backups is, what databases do you backup on your server? Well, SQL Server has master database, temp DB, pubs, Northwind, and some others that are called the user databases. From all those that are listed, the most important is the master database, so make sure you also check the backup and restore of the master database. master database is like, I guess for the old DOS guys like me, like the autoexec.bat and config.sys; it has the configuration of your system, or in Windows^® terms, like Win.ini or System.ini. It has all the configuration of your server, so the master database is very important to be backed up and restored.

The second one I would also consider is, that our shift with a product, and not related to our user database is the MSDB, which contains all the SQL agents or scheduling, and all the tasks that are scheduled are in the MSDB. Again, test your backup, make sure it works, and after that you're fine. This is just like having insurance. If you have insurance for your car, when you actually need to use it, the insurance only covers part of your accident. You now have a totaled car; it's no good. So the same thing here. This is your insurance. Make sure you have good insurance. To know that, restore it and make sure it works.

Go to the next slide (slide 4). In all SQL servers, we always want to consider what are the disaster recovery plans? So we need to have some plan in place for those unfortunate things that can happen, the server goes down, the hard drive crashes, or the building burns down. Make sure you have a second plan, an emergency plan, a way to get the system back up. With all the .coms and systems that are live 24 hours a day and accessed all over the world, SQL Server plays a big part. This is our database.

So how does SQL Server help you with having a good disaster recovery plan? Some of the known older options that we had like in SQL Server 6.5, and previous versions, and SQL Server 7.0, is we can have the failover support of cluster. So if you have Windows NT^® 4.0, Cluster services or Windows 2000^® Cluster services, you can use clustering with SQL Server. So you have another machine that will be ready to run if the first one fails. SQL Server 6.5, yes cluster, SQL Server 7.0 is better, and SQL Server 2000, the cluster was actually built into the product. It's not an add-on like in previous versions. Cluster services out-of-the-box is there. The product was developed with that in mind, which was not the case for the previous versions. So it really works very well.

Another thing you also have on the previous version, and some users use, is replication. Note that replication is not really a disaster recovery option, but allows you to have your data, as it says, replicated to another/multiple server or other servers, they are called subscribers. You can replicate only part of your data, those columns, or this type of rows. So you can massage your data before replicating it. This is where replication comes into play.

Now the new things, for SQL Server. Log shipping or sometimes called a standby server or warm backup. Log shipping on SQL Server 2000 is actually built into the system, so we have support for that. Note though, that it is only on SQL Server 2000 Enterprise Edition. Note it is different than the other versions, SQL Server 2000 edition works on a Windows NT^® Server 4.0, Windows NT^® Enterprise Edition 4.0, or a Windows 2000^® Server, Windows 2000^® Advanced Server, and Windows 2000^® Datacenter.

In SQL Server 7.0 and the BackOffice^® Resource Kit for 5.0, we did have some of the routines. We had some help to implement log shipping on SQL Server 7.0, but again it wasn't really supported by the product. It was just help on implementation. In SQL Server 2000 Enterprise Edition, it's actually built into the system.

Another new thing in SQL Server 2000 is the "Snapshot" backups, and we will go more in depth throughout the presentation.

Going to the next slide (slide 5). What did we have on SQL Server 6.5 and 7.0? This is the only slide were I'm going to briefly discuss these. On SQL Server 6.5 or SQL Server^™ 6.0 and previous versions, stumble, a full database backup, we marked the database and we started loading those pages, copying the data pages from the data of your database into your backup device. On 7.0, we would copy all that, but at the end, we would still get all those changes that were done throughout this time that a backup took.

In SQL Server 6.5, let's say the backup takes an hour. Well, whatever changes were done throughout that hour, although your backup only ended in this hour, you will not get that on your database backup. You are only going to get what was there when you started your backup. On SQL Server 7.0, if it takes one hour, you will also get everything that was changed throughout the hour. We use the fuzzy backup.

You can get some more information on that in SQL Server 7.0 Books Online. Note that SQL Server 6.5 backup was not fast and SQL Server 7.0 backup was incredibly faster. If you haven't touched SQL Server 7.0, and you're from the SQL Server 6.5 time, test the backup on SQL Server 7.0. Obviously, SQL Server 2000 is even more optimized and we're going to talk about that throughout the presentation.

Another thing to note is that on SQL Server 6.5 we only had a full database backup that we could do, and the transaction log backup on those versions; we'll call that a dump, dump database, or dump log. On SQL Server 7.0 we introduced the differential backup and filegroup backups. On SQL Server 2000 we have some other new stuff. We also had virtual device interface support for ISVs on SQL Server 7.0. Again, on SQL Server 2000 we revamped that and it's a lot easier. We have a lot of other new features that we're going to discuss throughout the presentation.

Going to the next slide (slide 6). We're going to discuss the SQL Server 2000 recovery models. So all the enhancements we had in there, why do we cover that? This is one of Microsoft trademarks, making things easy and popular. SQL Server 2000 is another one of these. SQL Server 7.0 was a lot simpler and SQL Server 2000 is even simpler. The way we made that easier for backup and restore is through the recovery models. We introduced recovery models to SQL Server 2000. It simplifies the recovery planning and the backup and restore procedures, so you have a model that will make it easier for you to think about that and plan that.

When you choose a model, you're going to have to make sure you understand that there are some tradeoffs, obviously, between performance, data loss exposure, log space consumption (which is a transaction log) and simplicity.

On the next slide, we're going to really go deep into that and hopefully aid you in finding out which would be the best scenario for you. There's also some easy transitions between common operation scenarios that allows you to maintain data protection, maintain availability and minimize disruption of automated maintenance procedures.

Go on to the next slide (slide 7). What are the three recovery models that we have? We have Full, Bulk Logged, and Simple. Each one has some benefits, and as I said, we're going to talk about the work loss exposure and functional limitations of each one. Throughout this presentation, we're going to discuss, how does each one of them work? What are the caveats? What do you need to know? Where do you draw your line? What are you losing or gaining by choosing one or the other? We're also going to mention what are the available backup types for each one of the models and how to transition between them. You can have your server live, and transition between one recovery model to another. So again, we have the Full, Bulk Logged, and Simple.

So going to the next slide (slide 8), we're going to talk about the benefits. Some of the key benefits of a Full recovery model are the minimal work loss exposure. As the name says full, it's full, it's complete. Hopefully you do not lose anything. This is the best and most complete, most covering recovery model. It supports recovery to arbitrary points in time. If one can remember, again on the previous versions, we had recovery to a point in time. SQL Server 6.5 is not to good, SQL Server 7.0 is much better, and SQL Server 2000, again, we enhanced it even more. We are now going to discuss some other recovery to some arbitrary points or marks to the log that we can make on SQL Server 2000.

On a Bulk_Logged recovery model one of the benefits would be the high performance bulk operation, BCP and bulk INSERT, and creation of indexes. That's when you may want to consider, if you have a lot of this, having bulk_logged. It's a little bit different than that option that we had on the databases, the SPDB option for select into bulk copy, where we log some of these transactions. It's a lot faster than a full, but we still log minimal log space on bulk operation.

Then the Simple is, as the name says, simple. There is no transactional log, so less log space is required and it is the simplest one to deal with a backup and restore strategy.

Going to the next slide (slide 9). How about Work Loss Exposure? Where do we stand? What does each one of them give us? On a Full recovery model, normally we don't lose anything. Now if the transactional log is damaged, we have to redo all the changes since the most recent transaction log backup. On a Bulk-Logged, we have to redo changes since the last transaction log backup if that transaction log is damaged, or if the damaged files were subject to bulk operations that committed after the last transaction log backup. So from here, if you do any bulk logged operations, back up your transaction log immediately. You want to have that backup, just in case it ever goes down and you need a new transaction log backup. Otherwise, no work is lost in a Bulk_Logged mode.

Then the Simple one, we just have to redo all the changes since the last full database backup or differential database backup.

Again, going to the next slide (slide 10). I'm going to depict a little scenario here on media failure. What I wanted to point out is, a lot of times we get this question, "Should we back up the tail of the transaction log?" Well, what does this really apply? The idea is that SQL Server has basically two devices or files, which are the data (the .mdf and the .ndf) and the log files, which are usually .ldf files. If these two are in different physical hard drives, physical controllers, or physical devices, and your data drive fails, so the one that contains the .mdf or the .ndf fails, you still have access to the .ldf. What can you do before you restore all your databases to new hardware or you put a new drive?

Well, the idea is that you can still get the transaction log backup. What you want to do with that is make a back up the current active portion of the transaction log by running that command, backup log to database name device name with no truncate. This only works if you are in the Full or Bulk_Logged recovery model. . Then, all we need to do is put the new hard drive in there and then start restoring. Restore the most recent full database backup that you have, and then restore any differential backup that you may have done after that, if any exists. Then apply all the transaction logs that you have, including this last one that we just made in step 1, where we did the backup the current active portion of the log.

The idea is you always want to keep a transaction log on a very fault tolerant device. Even maybe a little bit more important than your data part, if your backup and restore is done well, then the transaction log is the most important thing. Remember, any time we make any changes to the database, the first thing that it does is writes the transaction log, then it writes to the data later, whenever it has time, and whenever it loads the data from memory to the data part of your database. But the transaction log is immediately updated. The client, for example, doesn't get a response back from the server until the transaction log is updated.

Let's go to the next slide (slide 11). Some of the limitations, point-in-time recovery, full only allows recovery jobs point-in-time, and then under other models you must apply the most recent backup and then it will do all the work. Now there are other things that affect it. As I mentioned before, you have the log marks. This is in SQL Server 2000, the marks are usually unusable under the Simple recovery model because we don't make any backup of the transaction logs, therefore no marks are on the log.

Under the Bulk_Logged model, if a bulk log operation occurs in the log backup interval, and log marks are currently not placed if they're known to be unusable. So in a Simple recovery model, if you try to do a begin tran, use the mark statement WITH MARK, and put a label to your mark, it's not going to work. It will only work on the other recovery models with that caveat.

Now how do we log them? Go to the next slide (slide 12). On a Full recovery model, all operations are fully logged, so all operations are completely logged to the transaction log. On a Bulk_Logged mode you have minimal logging for some operations. For example, as name says, are for all the Bulk_Logged operations, like CREATE INDEX, bulk load, SELECT INTO writetext, and update text. On a Simple recovery model, we truncate the transaction log on checkpoint. We have the little flag, similar to SQL Server 6.5 and 7.0, we have the flag for truncate log on checkpoint. That's on for a Simple recovery model.

Going to the next slide (slide 13). Log Backups in Bulk_Logged Mode. This one is that from a Full recovery model you backup everything, a Simple recovery model you don't backup any transaction log. How do we do it with a Bulk_Logged Mode? The log backup contains extents modified by the bulk logged operations since log backup. It avoids breaking the log 1 chain, it eliminates the need for special full backup, and it has minimal impact on automated backups.

Looking at the diagram on slide 13, for bulk_logged mode we do the following: we start the transaction log backup, if a bulk_logged operation occurred, the first thing we put into the log backup which is the green and orange portion to the right). The first thing you put in there is the data pages that were changed. So we copy all those data extents that were changed because of a bulk_logged operation; then we copy all the transaction logs into this log backup. In that way, we actually didn't log that bulk log but we're marking what data was changed because of the bulk_logged operation. So when you do a transaction log backup, that transaction log backup is a little bit larger than a normal one, which wouldn't contain the bulk log, because we're loaded the data in there too.

Going to the next slide (slide 14). What are the available backup types for each model? In the left column we can see three different models, the Full, Bulk_Logged, and Simple. And on the right, what are the backup types we can choose?

I'm going to start with a simple one, which is easy. You can see that a transaction log backup is not allowed. File and file differential are not allowed. File differential is something new to SQL Server 2000. We had Database Differential before on SQL Server 7.0, but File Differential is new to SQL Server 2000. As you can see, on a Full database backup, a database backup is required for file backups, it is also required for Bulk_Logged and Simple. Where differential database backups are also in all of the three models, the transaction logs are required, both in Full and Bulk_Logged. File or File Differential is optional in both Full and Bulk_Logged recovery models.

Going to the next slide (slide 15). An interesting thing you can do with the recovery models, is that you may want to think about how you are going to choose one or the other, and you can change between them. So if you are, for example, on a Full recovery model and you want to change to the Bulk_Logged, there's no change. Nothing special needs to be done. The question is when you go back. The idea of having those recovery models, one of the ideas of implementing Full and Bulk_Logged is that most of the time if you want to be 100 percent safe, keep your database on Full recovery model. At some point in time, you may want to have the Bulk_Logged on, if you know you are going to do some bulk logged operations. Turn it to bulk logged, so it does minimal logging to the transaction log, and you can still use that faster speed of a bulk logged operation.

As you can see, if you change from a Full to a Simple, you have the last log backup prior to the change, keep this in mind. The other way around, if you change from Simple to Full, you have to do a full database backup after every change, or a simple to a bulk logged you have to do a full database backup. Why is that? Because at this point, the Simple doesn't use transactional backups, so we need to have now another clean database backup, so we can start to restore transaction logs if we need to. Then it's a full database backup; it's a new backup from your database.

So changing back-and-forth between Full and Bulk_Logged, transactions do not break the automated backup procedures. Going to and from Simple requires more care, but is uncommon. We haven't really thought that somebody's going to change a lot between Simple and Full. So if you choose Simple with no transaction log backups, you probably are not going to change to Full.

Going to the next slide (slide 16). How do you set up the recovery model? New to SQL Server 2000, the way you set that up is by using the ALTER DATABASE. So you use: ALTER DATABASE DatabaseName, and then use the keyword <recovery_option>, and set one of those recoveries as being Full, Bulk_Logged, or Simple. For example, to select a Full recovery model you do the following: ALTER DATABASE DatabaseName SET RECOVERY FULL. To select a Bulk_Logged recovery model you do the following: ALTER DATABASE DatabaseName SET RECOVERY BULK_LOGGED.

Another thing we want to see on the next slide (slide 17), is the legacy options that we had before, select into bulk copy, and truncate log on checkpoint. If we have truncate log on checkpoint being true, it's obviously a Simple recovery model. You have truncated in a transactional log, so there's no way to do a Full or a Bulk_Logged recovery model.

If you have it false, then it depends on the select into bulk copy. As one can think, if select into bulk copy is true, you are in the bulk log mode. If select into bulk copy is false, you are in the full mode. Think about this, we're just showing those options, because they still obviously exist, but if you want to change or set your recovery model, make sure you use what I did in the previous slide, which is the ALTER DATABASE and not the SPDB option.

As installed, the model database uses the full recovery model, which means anytime you create a new database, that new database is going to come with full recovery model. There are some caveats with that statement. You may want to bring up some sort of Books Online, and take a peek at that with the desktop development edition of SQL Server. This was made for development, we don't usually consider that you're going to have a Full recovery model. So the recovery model is set for Simple recovery model.

Going to the next slide (slide 18), Backup and Restore New Features. We have implementation changes to the differential backup. One of the things we had on SQL Server 7.0, which was new to SQL Server 7.0, was the differential backup. It worked well, but if you made any changes to your files or filegroups in your database, you would have to do a new full database backup, so the differential backup would not back up that change. So, if you had this huge database, you rely on full database backup, and then your incremental, your differential database backups, but you add a new file, on SQL Server 7.0 you would have to make another full database backup to start over. This is corrected on SQL Server 2000. The differential backup will have the information. Therefore, if you add a new file, add a new filegroup, or modify the information, the differential backup will have it in SQL Server 2000.

Also new in SQL Server 2000 is the Recover to mark. We have, as I mentioned before, fast failback from a standby to a primary, so if you have log shipping and you want to fail back between the standby server to the primary and vice versa, it is a lot faster and well implemented.

"Snapshot" backup and recovery, we have improved the recovery from isolated applications or operator error, and have improved security for backups. You are going to see that you can put some passwords into your backup. Just make sure that one understands that it is secure, and if we lose the password, it's lost. There's no back door, no way to intervene. You can't call anybody. Because there's no way to get in. The backup password is there, and someone needs to know that, so write it down, pass it along to other workers in your organization to make sure that somebody knows what that password is. There are some other enhancements we're going to talk about too.

Go to the next slide (slide 19). On differential backups, as I mentioned, it's a lot faster because it's bitmap driven. On SQL Server 7.0, we had the differential backup, but you had to schedule all the pages and see which ones were actually changed. Here we actually have a bitmap we keep, like a map of all the pages, and we can mark on that bitmap what pages were changed. So when we have to do a differential backup, it goes through the bitmap and immediately gets the pages that were changed. So the differential backups in SQL Server 2000 are a lot faster than in SQL Server 7.0. Also, you have the new differential file and filegroup backup for the very large databases (VLDB).

Go to the next slide (slide 20). Log markers for recovery to named event. We can use those log markers, as I mentioned before. You can use the statement similar to the following point: BEGIN TRANSACTION TransactionLabel WITH MARK. If that's in there, and obviously if you are on a Full recovery model, you can recover back to that mark. It is similar to point-in-time recovery and can include or exclude the names of transactions. So, you can do a recovery before the mark or recovery at the mark.

Go to the next slide (slide 21). The names can be reused, so the labels you used for the mark can be reused. What we want to do when we restore is bind it together with the time, so you can identify one of the marks. For example: RESTORE LOG…WITH STOPBEFOREMARK = ‘TransactionLabel' AFTER <the time>. Marks are recorded in MSDB, so if you load this transaction onto a new server, your MSDB is not there, you don't have the marks. There is no way to use the marks in there. Restore history tracks, restore to mark.

Go the next slide (slide 22), some recovery challenges. Prior versions of SQL Server handle crash recovery and media failure, if the transaction log is not damaged, and do not allow point-in-time recovery of a related database. SQL Server 2000 does. Interestingly, as I mentioned before on that example in the beginning, that we have this hard drive crash of your data if you still want to backup the tail of your transaction log. In SQL Server 7.0 we could do that, but we could only do that if we still had access to the .mdf file. In SQL Server 2000 you don't need access to the .mdf file. You can still do it even if you don't have the .mdf file there.

Go to the next slide (slide 23). Backup strategy for related databases. We want to consider the Full recovery model. Synchronized backups are unnecessary, use the recovery to mark, back up related databases in rotation. Variations, backup infrequently modified databases less often, and overlap as backups hardware allows. Manage backups to achieve recovery goals. Allow for roll forward time, and worst case, simultaneous restore and recovery of all related databases.

Go to the next slide (slide 24). We're going to talk a little bit about log shipping, which is fully supported, as I mentioned before, in SQL Server 2000. It's new, so it's implemented as built-in and is, again, only supported in SQL Server 2000 Enterprise Edition. That is why the icon is there. Database restore is not required, if the data and log files are not damaged. For example: BACKUP LOG … TO … WITH {NORECOVERY | STANDBY}. Remember, you can switch between the standby and the primary server, that's what log shipping is. So you have another server that contains all your database. To begin with, we have a full database backup loaded there and then as time goes by, we have transaction logs load into that standby server.

Log backups taken on secondary can be applied back on primary. This new to SQL Server 2000. It's very useful for planned failover. As I mentioned before, you want to make sure you can restore. Well in the case you are using log shipping, you also want to make sure that the failover works. You want to test the failover between the primary to the standby. It enables high availability to the reporting server to switch between primary and secondary. For example, the standby server, if you BACKUP LOG … TO … WITH STANDBY, that's usually used when you want to have the reporting server.

Go to the next slide (slide 25), Snapshot Backup and Restore. Functionally equivalent to full database, or file and filegroup backups. You can do a roll forward using conventional differential and log backups, and it can be used to initialize standby database. Again, the history of the snapshot backups is maintained in the database. It's supported by third-party virtual device interface applications with storage system support, for example, the split mirror or copy-on-write.

The snapshot backup is not an out-of-the-box support; it's not something that Microsoft gives out there. It's a third party, so it's another vendor that writes some applications, and create and develop the hardware and software to work with it. Microsoft gives support to them and they work with the Microsoft SQL development group.

For example, on the next slide (slide 26), we have a standby server using EMC^2® Storage that uses the split-mirror. As you can see there, you have a disaster recovery site, which is physically located in a different area, in a different region of the country. Split-mirror, during database backups, can be used in mount volumes during database restore to standby, in conventional log backups to disk are transferred the same way. So the idea is that you have your hard drive or all your data and logs of your database in a mirrored hard drive and it can be split at any given time and transferred to that (mirrored drive) or server. Again, this is third-party support, so in this case, you would need specific hardware and software from the third party, additionally in/to SQL Server 2000.

Going to the next slide (slide 27), the partial database restore. This restores subsets of the filegroups, and is intended for recovery from operator or application error. For example, it happens a lot, they have calls like this, "Oops I dropped a table. I didn't mean to." Well this is where we can use the partial database restore. You can go in and merge or replace the damaged data with the table that was dropped incorrectly.

Going to the next slide (slide 28), Log and File Backup. We have the flexibility so that log and file backups do not block each other. Before, there was a lot of contingency, if you were doing a log backup, you didn't want to do the other. Well, in SQL Server 2000, files are independent of each other, so you can actually do a log backup at the same time you are doing a file backup. In the example I have at the bottom of the slide, I have log backups running all the time, let's say, for example, every 15 minutes, I make a transaction log backup. Then throughout the time, at some points, I do a file level backup.

Note that you must do concurrent file backups as a single operation. If you want to do two files at the same time, you must be on one backup operation. It cannot be two separate ones.

Go to the next slide (slide 29), Backup Security. We have a password to your backup media and backup sets and nothing can be done if you forget it. Please understand that. If you put in a password, I know you don't want to it, but write it down somewhere, tell somebody else. If that person is going to leave for vacation, make sure somebody else knows that password. If that person is going to leave the company, make sure the password is given to somebody else. Otherwise there's no way out.

The backup security prevents any unauthorized restore using our tools. So note that it's not really encrypted in there, so the data can be interpreted by another program. This is not supported by Microsoft; we do not recommend you doing that. I'm just mentioning that the password is not encrypted. It's just a password; our tools will not restore unless you know the password. This prevents any unauthorized append to media by our tools and other MTF-compliant applications, and does not protect against media overwrite.

Go to the next slide (slide 30), Other Backup and Restore Improvements. We have on-line back ups of large data files during heavy updates. So that in SQL Server 2000 we have even less transaction throughput drop than we had on SQL Server 7.0. It's a lot higher average backup throughput. The log can be backed up, despite a damaged primary file, as I mentioned before. Media recovery of add and drop files is going to be there, and we have a maximum of 64 backup devices.

Go to the next slide (slide 31), Database Settings, Improved usability. All database options are modified by using the ALTER DATABASE. As I mentioned before, the sp_dboption (bold in SQL Server Book Online) is still there, but don't use it; use ALTER DATABASE inside the recovery model. This is a lot better and the recommended way to do this in SQL Server 2000.

Database options are available from built-in functions, so you have those two functions there, fn_DatabasePropertyEx() and fn_DatabaseProperties(), that will allow you to set and check the data recovery models.

As I said, the SPDB option and database properties are frozen at SQL Server 7.0, they have no functionality. Use the RecoveryModel property. The RecoveryModel property specifies the recovery model for a database and uses the syntax: object.RecoveryModel [= value].

Go to the next slide (slide 32), Putting It All Together. What scenario could we have? For an example, on the top of the slide we have Time, so if we begin from the left to right as time goes on. We could do a full recovery if we setup a Full recovery model. We could have Bulk Load done in there, you could have log backups done at the same time, you could have File Backups done at the same time, as long as again, those two file backups are from one command. You could have Log Backups done, and you could change to Bulk_Logged recovery model and do bulk load operations, Create Index. Make sure that you remember that after the bulk log load is done, back up your transaction log. Then again, switch back to Full recovery model and do online checks, create indexes, and keep your tradition log backups done all the time.

I guess we end our presentation here.

Heidi: Excellent. Thank you so much, Reinaldo. It is time to move on to the Q&A portion of this Support WebCast. A couple of notes for you, before we do so.

If some of the details in the PowerPoint slides were a little bit difficult for you to view within your browser or you would simply like to have a hard copy of the slide, be sure to download that file from the Web site (support.microsoft.com/webcasts). You go down to the bottom of that page, you'll see link to all the past sessions and then this one will be under the Technology of Databases.

The Q&A portion of this Support WebCast is intended to encourage further discussion of the Support WebCast topic. If you do need technical assistance, please submit an incident on the Web or phone-in to Microsoft Product Support Services and speak with a Support Professional

I do have one more message. Jack Lee, is another Support Professional who works with Reinaldo and he is joining Reinaldo to assist in the Q&A session, so I do want to thank him for joining us as well.

The first question. Does log shipping work on a database where writetext or SELECT INTO operations have been performed?

Reinaldo: Yes. It does work, as long as you are in a full database model.

Heidi: Next question. Would you please discuss when and how to use single-user mode when doing a restore?

Reinaldo: If we're talking about putting the server in single-user mode, the only thing I can think of is that, if you're trying to restore some of the system databases, like master database, and you need to put a database in single-user mode. Otherwise you do not need to put the database in single-user mode in any of the recovery models.

Heidi:

The next question is: In SQL Server 7.0, we always have a problem that our physical log file will not shrink. Do you know if this is corrected in SQL Server 2000?

Reinaldo: Yes. There's a little issue with SQL Server 7.0, a new feature for SQL Server 7.0 that we didn't have in SQL Server 6.5, that it automatically expands and shrinks itself in SQL Server 7.0. We did have an issue that if your transaction log was not completely full all the way to that last VLM, you would not truncate that. So you have to fill it up with operations and then it would truncate. On SQL Server 2000, yes, it is fixed, and the database will actually try to shrink your transaction log as much as it can, and will fill it up with transactions, so it can shrink your transaction log.

Heidi: Terrific. Before we move on to the next question, we are very interested in your feedback regarding the program overall, if you have any comments about the topic you listened to today, or suggestions for topics you'd like to see in the future, use the alias feedback@microsoft.com. If you do use that alias, be sure to include "Support WebCasts" in the subject line.

Next question: Before I submit a big batch job, I want to back up my database. Is full database or disk database backup recommended?

Reinaldo: I guess, if you are going to load, a batch job into your database, load a lot of data at one point, we're probably talking about Bulk_Logged. If that's the case, the only time that I mentioned throughout the presentation that you need to do just a transaction log backup, is after the load, if you are in Bulk_Logged recovery model. If you are in Full recovery model, you will be logged in everything, so you don't really need to do another transaction log backup just because you made a load and you're thinking of switching that over to the full-end recovery in bulk logged mode.

Heidi: Excellent. Next in line: Is there a quick way to back up and restore all the DTS packages at once?

Reinaldo: It's not really related to the topic, DTS. It really depends on where you're storing your DTS package. If the package is stored as a file, if you store it on MSDB, backing up to MSDB would take care of it. That's all I can answer at this point.

Heidi: Next question: Can you run SQL Server 2000 effectively on Windows NT Enterprise or does it need Window 2000 Advanced Server?

Reinaldo: This is not really related to this presentation, but you can bring up Books Online or even on the Web, go to www.microsoft.com/sql, and you're going to be able to check all the different platforms that SQL Server 2000 is supported on. But quickly to answer your question, yes, it would work on any one of those two. The only thing that I mentioned throughout the presentation was that SQL Server 2000 Enterprise Edition works on Windows 2000 Server, Advanced Server, and Datacenter Server.

Heidi: Excellent. Does a database need to be in Bulk_Logged or simple recovery mode to perform select into?

Reinaldo: I think the answer for that will be that you don't really need to be on any recovery model, the part of the recovery models are just for backup purposes. So if you want to take advantage of the speed of a bulk logged operation, you want to consider what recovery model you are going to use. A Bulk_Logged recovery model is going to be faster than a Full recovery model. Obviously, on a Simple recovery model, it will be even faster, because you're not even logging anything of your bulk log or select into.

Heidi: Next in line: I'm doing a full database backup. At the time a user starts a transaction, my backup completes and after that, the user commits the transaction. If I restore my backup, will the transaction be lost?

Reinaldo: Yes. Because if the transaction is not committed on the end of the database backup, we don't have that transaction in there. We have the transaction as not being committed. So it will be in the log, so what you want to do to capture that transaction will be to backup the transaction log after that, but the transaction is not committed and will not be on your backup.

Another interesting thing I want to point out on the SQL Server 2000 backup is that we go through the backup of the transaction log. As we finish, remember that in the one-hour scenario I gave on SQL Server 6.5 and SQL Server 7.0, as we finish the transactional backup, we go back and back up all the data pages that were modified from the time we started the backup. So the backup is a lot faster on SQL Server 2000. It will also capture those changes that were done while the transaction log backup was being done, but obviously only until the end. When it ends that backup, that's it, we don't get any more information.

Heidi: The next question is in regards to RAID configurations. Can you recommend RAID configurations for backup file devices?

Reinaldo: That's a little bit out of the scope. I don't know if I can even help a lot. Obviously there's all those different RAID configurations that it would mark and choose. It's a little bit out of the scope of this presentation. It would take a lot of time for me to really describe all the different RAID options and it would be out of the scope.

Heidi: Okay, well if it's out of the scope of our topic today, we will go ahead and move on.

The next question is, with regard to VLDB, very large database backup and restore. On a VLDB, perhaps 4 or 5 terabytes, is there a benefit to limiting the .mdf to the system catalog and placing all user objects in the .ndf files or does this not have an impact on your ability to back up or recover?

Reinaldo: I think I mentioned this before. SQL Server 2000 does not rely on the .mdf to do the backup and transaction log. So separating the system tables in .mdf and all that is in .mdf. There are obviously a lot of benefits if you want to do the filegroup-level backups. On a huge database, you definitely want to use the filegroups and files. Again, new to SQL Server 2000, you can also do a file backup. But aside from being able to do filegroup backups, you can use the feature of a file backup.

Heidi: Next question: Are there commands to turn logging off and then back on in SQL Server 7.0 and SQL Server 2000?

Reinaldo: To turn transaction logging on and off, you can't do that. There's no way to do that. That's just how SQL Server works This is the way SQL Server guarantees the data integrity. As I mentioned before, the transaction log is written to as an update or when an INSERT statement is done. Even before the client can get a reply back that that transaction is completed, the first thing SQL Server does is write to this transaction log. So we don't have any other way of keeping track of the data changes, so no you can't turn off transaction log.

Heidi: Terrific. The next question is: If we store DTS in the database, what do we need to back up? These are part of the database SO they are pertinent to this presentation.

Reinaldo: If the DTS package is stored in the database, it is stored in the MSDB database, so a backup of the MSDB will back-up your DTS package. If you would like more information on that, Heidi, this would be one of the questions I would do a little research and add to the presentation later on.

Heidi: The next question is: Is there still the SQL Maintenance utility with SQL Server 2000? If there is, is there any new functionality included with it?

Reinaldo: SQL Maintenance is still available in SQL Server 2000, as it was in SQL Server 7.0. I can't think off the top of my head of any new functionality there. You may want to consult the SQL Server Books Online for more detailed information on how to use that command and all the settings you can choose and set it up.

The neat thing about Microsoft SQL Server 2000 is that everything is so easy. In SQL Server 2000 you just run the wizard and it will build the command for you.

Heidi: Excellent. Next question: Can you have a transaction log backup and full backup executing at the same time? Can you start another database backup before the first one finishes?

Reinaldo: If you want to do a transactional backup at the same time you're doing a full database backup, you can't. The only thing you can do is the file backup Can you start another database off another database, sure. As long as it is not the same database, you can always start as many database backups as you want.

Heidi: The next question I was in the process of running DTS to append 5 million records to a table in my database. When DTS reaches a little more than 3 million records, it fails with an error specifying that the primary key was null and a record number was given. Does DTS do a complete rollback when an error occurs, or are records inserted left in the tables? I'm wondering whether I can start the process all over or whether I have to search the input file and remove records already loaded?

Reinaldo: Heidi, I don't think this is really related to this presentation. It's more a DTS question. I would suggest the customer who is asking this question to probably do a little research on, again, SQL Server Books Online and on our Knowledge Base articles related specifically to DTS and if it does rollback or not on transaction.

Heidi: Good suggestion. Once again, if you haven't sent us any comments and you have some, please e-mail us at feedback@microsoft.com at any time and include "Support WebCasts" in the subject line.

It appears that we have not received any additional requests or questions, so we're going to go ahead and wrap up today's session.

I want to thank all of you for joining us today. I do hope you found the content beneficial. Once again, the archives are easily accessible from support.microsoft.com/webCasts. So if you would like to access the PowerPoint slides, on-demand streaming media, or transcripts, that's going to be an excellent location. We also have a list of all upcoming sessions from that location. Thanks again for joining us today and have a great day.

Goodbye.

Reinaldo: Thanks. Bye.