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. 2

3. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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

13. 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. 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. 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. 16 Complete Database Backup (2)

17. 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. 18 Differential Backup (2)

19. 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. 20 Transaction Log Backup (2)

21. 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. 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. 23 Filegroup Backup (3)

24. 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. 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. 26 Media Set and Media Family

27. 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. 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. 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. 30 Partial Database Restore (2)

31. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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

42. 42