Understanding Backup Restore in SQL Server 2000 Pankaj Agarwal SQL Server Support Engineer SQL Support PSS NC Microsoft

1. Understanding Backup Restore in SQL Server 2000Pankaj AgarwalSQL Server Support EngineerSQL Support PSS NCMicrosoft Corporation

2. Agenda • Importance of backup/restore • Database architecture overview • File/filegroups • Recovery models • Discuss transaction log (log sequence number) • Discuss various types of backups available in Microsoft® SQL Server™ 2000 • Tape backup • Media family and media sets • Restoring backups, partial database restore • Factors to consider when designing a backup strategy • Backup restore best practices • Case studies • Troubleshooting backup/restore errors • References

3. Importance of Backup Restore • Consider backup/restore planning to be the primary recovery mechanism • This method of recovery, in most situations, requires less time and provides the most reliable results • Unfortunately, in many cases, a regular backup strategy does not satisfy recovery needs, so even regular backups are unusable • A backup recovery plan should typically be considered from the restore perspective • If a restore has to be performed, the available time and resources would determine the time and effort spent on the backup strategy

4. Database Architecture Overview • SQL Server databases have two kinds of files: • Data files (.mdf/.ndf extension) • Transaction log files (.ldf extension) • A SQL Server database is always made up of at least one data file and one transaction log file • Database files hold the data added to the database • Transaction log files keep a record of all modifications made to the database

5. Database Architecture Overview (2) • There may be multiple files of each type in a single database • By default, a new database is created with one data file and one transaction log file • Data and transaction log files cannot be shared between databases • The first file for the database is called a primary data file (with .mdf extension) • All other data files are secondary and may be created with a .ndf extension • Data files are always grouped together into filegroups • Transaction log files cannot be members of a filegroup

6. Files/Filegroups • SQL Server database files are always grouped together in a filegroup • SQL Server creates a new database with a default filegroup called a Primary • Other filegroups may be added when you create the database or later on • A new SQL Server data file may be added to an existing filegroup when the new file is created • An existing file cannot be added to a new or existing filegroup • SQL Server uses a round-robin algorithm for space allocation when there are multiple files in a filegroup

7. Files/Filegroups (2) • Implementing filegroups in a SQL Server database presents an opportunity to use the filegroup backup/restore features • Using filegroups may improve performance • Implementing filegroups requires a complete physical design • Implementing filegroups for a production database may require downtime because when you move a table/index between filegroups, you must create a new table, move the data, and re-create the indexes

8. Recovery Models • SQL Server 2000 introduced a new concept called recovery models • This model simplifies the database settings related to transaction log and bulk load operations • Three possible recovery models: • Full • All update operations are logged, including bulk load operations • Take transaction log backups for maximum recoverability

9. Recovery Models (2) • Bulk Logged • Bulk load operations are logged partially • If a bulk load operation is carried out, perform a complete backup for complete recoverability • Simple • Transaction log is maintenance free • Bulk load operations are minimally logged • Perform complete and differential database backups • Transaction log backups are not permitted • For more information, see the WebCast: • SQL Server 2000 Database Recovery: Backup and Restore

10. Transaction Log • Each SQL Server database contains a transaction log • The transaction log can have several physical files • By default, these files have the extension .ldf • The transaction log records all modifications made to the database to protect against an unexpected shutdown • The transaction log is also used when implementing Log Shipping and Replication • The level of detail logged for modifications depends on the recovery model designated for a particular database • The transaction log is internally organized into several logical blocks called virtual log files • The transaction log does not contain any data, it is only a record of the changes that were made to the data

11. Understanding the Contents of the Transaction LogInstructions to Drive from Lawrence Street to Court Street • Start here • Take first right • Take first left • Take first right • Take third right • Take first left • Take first right

12. Understanding the Contents of the Transaction Log (2)Instructions to Drive from Lawrence Street to Court Street • Start here • Take first right • Take first left • Take first right • Take third right • Take first left • Take first right Instructions do not mean much without a starting point Data in the database : Starting point Transaction Log : Instructions

13. Log Sequence Number • The log sequence number (LSN) is a unique number that is designated to entries in the transaction log • Each entry is called a log record • A simple transaction may contain several log records, so all log records for a transaction are linked to each other • The LSN is system generated and cannot be assigned or viewed for a certain transaction • The concept of LSN is important to understand because it is used in backup restore operations • Each transaction log backup has a starting LSN and an ending LSN • Consecutive transaction log backups should have the same LSN value for the LastLSN and FirstLSN

14. Different Types of Backups Available in SQL Server 2000 • SQL Server 2000 supports these types of backups for a database: • Complete backup • Differential backup • Transaction log backup • File/filegroup backup • File/filegroup differential • Transaction log and file/filegroup backups are not permitted if the database is in a Simple recovery model

15. Complete Database Backup • Backs up the whole database in a single image • Is self containing and does not depend on any other types of backup • May be restored to the same database or a new one on any server • Perform at regular intervals for all production databases

16. backup database db1 to disk = 'd:\db1_complete.bak' db1 backup database db1 to tape = '\\.\tape0' Complete Database Backup (2)

17. Differential Database Backup • Backs up all extents modified since the previous complete backup • Cumulative backup • Most recent backup contains changes from previous differential backups since the last complete database backup • Excellent alternative to performing a complete database backup for systems that are large and not modified very frequently • Enhanced in SQL Server 2000 – uses a bitmap to track modified extents • This speeds up the differential backups because the backup process hops directly to the modified extent indicated by the bitmap

18. Differential Backup (2) backup database db1 to device1 with differential Differential Backup Bitmap of modified extents

19. Transaction Log Backup • Backs up transactions since previous transaction log backup (or previous complete backup if no previous transaction log backups were performed) • Only permitted when the database is in the Full or Bulk Logged recovery model • Truncates the transactions that were backed up, unless the transactions are pending propagation to subscribers in transactional replication • May be used to recover the database to a point-in-time or to point-of-failure • All transaction log backups in the backup sequence should be available while performing recovery

20. update table1 set field1 = 1 where field2 = 2 Transaction Log Backup (2) Transaction Log db1 backup log db1 to device1

21. Filegroup Backup • Backs up individual files/filegroups in a database • Does not back up a portion of transaction log (like database complete backups and differential backups) • Requires transaction log backups to bring the recovered database back online • To recover from a filegroup failure using filegroup backups you must: • Perform a restore of the filegroup backup on the same database where the backup was performed • Apply all transaction log backups, including the tail of the transaction log • Q253817, “How to Back Up the Last Transaction Log When the Master and the Database Files Are Damaged”

22. Filegroup Backup (2) • Partial database restore functionality permits you to restore only the backed up file/filegroup to a new database • Speeds up the restore process by requiring only the file/filegroup that was backed up to be restored instead of the whole database

23. Primary Filegroup fg1 Filegroup fg2 Filegroup Filegroup Backup (3) backup database db1 filegroup = fg1 to device1

24. File/Filegroup Differential Backups • Combines the differential backups and the file/filegroup backups • A file/filegroup differential backup backs up all changes made to a particular file/filegroup since the last file/filegroup backup • Similar to any file/filegroup backup, it does not back up a portion of the transaction log, so it is required that all transaction log backups be applied if recovery is required for the particular file/filegroup

25. Tape Backup • Microsoft SQL Server uses Microsoft Tape Format to store the backups • SQL backups may coexist with any other backups performed using MTF (for example, Windows backups) • Provides the added benefit of restarting an incomplete or interrupted backup/restore operation by using the RESTART option with the BACKUP and RESTORE commands • Introduces the concept of media sets and media families

26. device1 device2 device3 device4 Continuation Media Initial Media Media Family Media Set and Media Family Media Set back up database db1 to device1, device2, device3, device4

27. Restoring Databases • Partial database restore functionality permits you to restore only the backed up file/filegroup to a new database • Actions: • Restore a complete database backup • Apply any differential and transaction log backups • Recover the database

28. Restoring Databases (2) • If recovery is required only for a specific file/filegroup, and backups exist for this file/filegroup, you may take the following actions: • Perform a transaction log backup of the tail • Q253817, “How to Back Up the Last Transaction Log When the Master and the Database Files Are Damaged” • Restore the file/filegroup backup • Apply any file/filegroup differential backups • Apply all transaction log backups since the latest file/filegroup backup • Apply the tail of the transaction log from step 1 • Recover the database

29. Partial Database Restore • Restores a file/filegroup on its own into a new database • Useful for situations where only a portion of the database is required during recovery • Use the PARTIAL option with the RESTORE Transact-SQL command • Restores all tables in the primary filegroup • See Q281122, “Restore File and Filegroup Backups in SQL Server”

30. restore database db2 filegroup = fg1 from device1 with partial Primary Filegroup Primary Filegroup fg1 Filegroup fg2 Filegroup Partial Database Restore (2) fg1 Filegroup fg2 Filegroup fg2 marked offline

31. Factors to Consider When Creating a Backup Strategy • How much information can you afford to lose? How critical is the information in the database? • What is the availability of resources governing the backup/restore strategy? • For example: finance, time, administration • What is an acceptable time period to perform maintenance when a disaster happens? • Is this a 24x7 database? • How large is the database? What type of data is stored in this database? • For example: OLTP or warehouse?

32. Backup Restore Best Practices • Perform regular restores of backups • Currently, the only way to verify the restorability of a backup is to actually restore it. • If possible, use a redundant computer. This computer may be used as a standby computer if the production server has unexpected hardware failure. • Schedule time for a simulated disaster • This will give maintenance team members a fair idea of how well the current disaster recovery strategy holds up.

33. Backup Restore Best Practices (2) • Document the recovery procedures • These procedures should be easily accessible by anyone involved in maintaining the server. • Periodically check the Windows NT® event logs for any hardware problems • These may be reported by SQL Server, but it might be too late.

34. Backup Strategy Case Study 1 • Consider a 24x7 .com database (about 5 GB) on SQL Server 2000 • Perform a complete database backup on the first Sunday of every month between 1:00 A.M. and 4:00 A.M. • Perform a differential backup every night at 1:00 A.M. • Perform transaction log backups every 30 minutes around the clock

35. Backup Strategy Case Study 1 (2) • Recovery path will involve loading the complete backup, the latest differential backup, and all transaction log backups following the differential backup • Provides a means to perform point-in-time restore and point-of-failure restore • Point-of-failure restore is performed if the tail of the transaction log is backed up before restoring the complete backup

36. Backup Strategy Case Study 2 • Consider a data warehousing database (about 100 GB) created with three filegroups and five files per filegroup • Database is loaded with data every month on the first Sunday of the month (data is used primarily to create analysis services cubes) • Perform complete database backup after the data is loaded on the first Sunday of the month • Alternatively, differential backups could be performed every Sunday, with a complete backup being performed once every month

37. Backup Strategy Case Study 2 (2) • If this database was modified regularly, then filegroup backups (in conjunction with transaction log backups) could be used • Filegroup1 could be backed up on Monday and Thursday; Filegroup2 on Tuesday and Friday; and Filegroup3 on Wednesday and Saturday • Back up transaction logs as frequently as you want; however, these would be mandatory after each filegroup backup

38. Troubleshooting Backup Restore • Check SQL Server errorlogs, Windows NT System and Application event logs, and scheduled job output to make sure that regular backups completed successfully • If backups are unsuccessful, refer to any I/O errors in the SQL errorlogs for more information • Also, look at the Windows NT System and Application logs to see if there are any errors • Test the integrity of the backups by performing a restore to a different server

39. Troubleshooting Backups • Check the basics: • Drive space • Disk subsystem errors • Tape drive errors • Regularly restore the backups and notice any errors raised while restoring • If network backups are being performed, look for network errors in the Windows System and Application eventlogs • Regulary look at SQL Server errorlogs and Windows NT eventlogs for errors that may indicate backup failures

40. Troubleshooting Restore • The most common problem not being able to restore the backup file • The only way to verify this is to regularly restore backups using the same medium that you would use in disaster recovery • If the message from SQL Enterprise Manager is not descriptive, perform the restore operation from the SQL Query Analyzer window to get more information

41. References • KB articles • Q307775, “INF: Disaster Recovery Articles for Microsoft SQL Server” • Q221465, “INF: Using the WITH MOVE Option with the RESTORE Statement” • Q231347, “INF: SQL Server Databases Not Supported on Compressed Volumes” • Q253817, “INF: How to Backup Last Transaction Log When Files are Damaged” • Q281122, “INF: Restore File and Filegroup Backups in SQL Server” • SQL Server Books Online • Inside SQL Server 2000