Backup and Recovery Theory and Interview Questions Written by Zakir Hossain, CS Graduate (OSU) Chief of IT Operations, USAID CEO, Data Group

Fed Certifications: COR/AOR Oracle Certifications: OCP (Oracle Certified Professional), OCA (Oracle Certified Associate), Oracle RACAdmin, Oracle Backup/Recovery-Admin, Oracle Performance/Monitoring-Admin, Oracle App ServerAdmin Microsoft Certifications: MCDBA (Microsoft Certified Database Admin), MCITP: SQL Database Admin, MCITP: SharePoint Admin System Admin Certifications: MCITP: Windows Server 2012 Admin, RHCSA (Red Hat Certified System Administrator) Programming Certifications: SCJP (Sun Certified Java Programmer) Other Certifications: Security+, ITIL V3

Attention ============================================ You are not allowed neither sharing nor altering notes, questions, and answers with any institute, individual, and discontinued students or commercial/business use without written permission is 100% prohibited. If you need out documents, please contact directly with Zakir Hossain.

It is a Federal Copy Right Violation of Intelligence Product. Violators will be prosecuted with the fullest Extent of Federal Law.

Page 1 of 34

Backup and Recovery Theory and Interview Questions Exam Question: 1. 2. 3. 4. 5.

How do you deploy a database in test/UAT environment? How do you verify a backup is restorable? How long does it take to backup your database? What is compression and how much SQL Server can compress a backup? Difference between RECOVERY, NORECOVERY, AND STANDBY (Read only database, cannot modify any data in this database) when restoring a database? 6. What is VLDB?

Review Questions: 1. Which TCP/IP port does SQL Server run on? How can it be changed? 2. What are the main requirements to install SQL Server 2012? 3. What are the authentication modes in SQL Server? How can it be changed?

1. 2. 3. 4. 5. 6. 7. 8. 9.

What is SQL Server Agent? How do you refresh a database? How does the database recovery model impact database backups? How can I verify that backups are occurring on a daily basis? How do you know if your database backups are restorable? How do you migrate a database from SQL Server 2005 to 2008 R2? What are some common reasons why database restores fail? What are the permissions required to perform backup and Restore? How can you be notified if a native SQL Server database backup or restore fails via the native tools? 10. What are some common post restore processes? 11. How can full backups be issued without interrupting the LSN’s? 12. What are the important system databases need to backup? How do you backup system databases and restores system databases? 13. What are the backup and restore system tables? 14. How is a point in time recovery performed? Page 2 of 34

Backup and Recovery Theory and Interview Questions 15. What are your recommendations to design a backup and recovery solution? Simply what is Backup Check list? 16. What options/arguments can be specified in a BACKUP LOG statement to keep inactive log records from being truncated? 17. What are all of the backup options? 18. What are all of the Restore options? 19. How much time does it take for full backup and restore of 500 GB database? 20. What are the issues you faced in backup and restore process? 21. How to perform the tail log backup? 22. What is piecemeal Restore?

Page 3 of 34

Backup and Recovery Theory and Interview Questions

Introduction to backup: Consider that you have some important files in your laptop, some important contact information, and your favorite music collection in my laptop or in your desktop computer. You do not want to lose these even there is a corruption of your laptop or desktop computer. So, you want to be able to retrieve these even there is a failure, disk corruption or even there is a los or stolen of your laptop or desktop pc. Backup is making a copy of a database in a way that can be used to restore the database when needed and maintaining the backup copies in a safe place. So, the backup files can be used to restore the database on any server when needed. For our production databases, it is very common to keep 3 full recent backups and 3 differential/incremental backups and all related transaction log backups. On the other hand, it is a very common practice for development and test databases to maintain only 1 most recent full backup. So, every time when you take a new full backup overwrite the previous full backup. A Backup could be a complete or partial copy of a database or databases or database files (Data Files and Log Files) Example of Complete/Full backup: Full backup, Copy Only backup and backup of all data and log files Example of Partial/incomplete backups: Differential/Incremental backup, Transaction Log file backup, some of the File or File Group (Piece-Meal). Page 4 of 34

Backup and Recovery Theory and Interview Questions

Restore: Is the process to restore a database using backup or backups of the respective database or databases. Keeping a copy of backups: Do not keep the backups not on the same disk of the same machine What are your recommendations to design a backup and recovery solution or DR (Disaster Recovery) Solution? Backup Location: Backup Media: Backup to Disk or Tape We do backup on local disks and then move the backups to the backup server. We used to copy the backups from the backup server to Tape. However, we do not use tape anymore. (DAT72) We also keep/maintain the latest copy in local machine. So we save time to copy the backup from the file/backup server to the server. Offsite Storage: Archive to Tape: We used to copy the old backups on TAPE. However, we do not use Tape backup anymore as it is not friendly and destroy easily How many copies of backup files to Keep and how long/retention period? What is your backup strategy? For our production databases, we maintain 3 copies of recent full backups, and other related backups of Differential and transaction log backup. Page 5 of 34

Backup and Recovery Theory and Interview Questions

For our development, test, and UAT (User Acceptance Test), which is also pre-production databases, we maintain only 1 backup copy of the databases. We scrub PIV (Personally identifiable value) before we restore the database in test or development environment. (What is scrub, why and when scrub data?) Modify actual data with fake data To protect personal information data Before or right after restoring the data Example, Tell the tools you have used to verify and setup the recovery models of a database (s)? 1. SQL Server Management Studio 2. Query Analyzer SELECT NAME AS DB_Name, user_access_desc, state_desc, recovery_model_desc FROM SYS.DATABASES

Page 6 of 34

Backup and Recovery Theory and Interview Questions

How long does it take to back up your database? We have many databases in different servers. The backup time will be different depending on server resources and type of data. However, I can give you some examples from current work environment, our backup time for 1 TB: 5-6 hours 500 GB: 1-2 Hours 100 GB: 45 minutes – 1:15 Minutes What is your backup strategy/plan? - Seek for business requirement, talk with supervisor and clients to find their requirements - Technical feasibility: Is it possible to do what clients are looking and even it is possible, is it a good plan to do technically - Cost for hard disk space (DB Size: 1 Formula: DB Size + (DB Size * 3) + 15% Free space to operate + 10-15 % Manufacturer loss (1 GB = 1024 MB | 1 GB = 1000 MB) 1 TB + 1 TB * 4 + .15 + .15 = 5.30 - Data Loss Acceptable: OK if we lose data for 1 hour/2 hour/1 day/1 week/Do not care/Cannot lose any data hour and - Considering to full backup only: Every hour / Every 2 hour = Bad solution: System will be too busy, more jobs will be running, higher possibility of failing jobs

Page 7 of 34

Backup and Recovery Theory and Interview Questions

- Strategy 4: Considering to full and differential backups only: Full backup: Full backup everyday + Differential: Every hour 1/week + Differential: Every hour - Strategy 5:Considering to full and differential and transaction log backups: Full: Weekly, Diff everyday + Transaction: every hour/30 minutes/15 minutes Additional benefit: No loss of data by using Tail log backup Strategy 6: Final Solution: Full backup Weekly, Diff everyday + Transaction: every hour More Examples on Backup Strategy: In our environment, we have Dev Environment: (Developers/Programmer/Software Engineers, Applications), Test Environment: - Connections – Users and Apps can connect - Applications functionality - Data Integrity (Data Quality) - Orphan Data: Data do not have related data. Example, part of the employee record has been deleted Prod environment: Our backup strategy is different for different environment Let me explain our strategy for Page 8 of 34

Backup and Recovery Theory and Interview Questions

Strategy One: For our Production Databases: We take 2 full backups (one on Tuesday at 7 PM, and second on Friday at 7 PM), Differential backup every 2 hours / or everyday at 11 PM, and we also take Transaction log backup every 15 minutes, every 30 minutest, and every hour Strategy Two: Production Environment:  2 Full/wk (Tuesday & Friday at 9 PM) >> Place a checkpoint DG_Training_F_BKUP (Naming Convention for DG_Training Database) When we setup a job, it is our company policy to use standard naming convention) For an example,  Differential Backup: at my company we are using differential Backup every 2 hours (Every day at 10 PM) >> We do not have any activity during the weekend, so….  Transaction Log Backup every 15 or 30 min >> We backup our database on the local disk (Local Server) and then another job runs to copy the backups from the local server to the Backup Server/Central Backup Server >> Our retention policy is to keep 3 copies ( >> Notification: It is our policy to setup the notification if there is any failure, DBA get notification (Database Server will automatically send an email – Notification - Troubleshooting)

Page 9 of 34

Backup and Recovery Theory and Interview Questions

For VLDB (Very Large Database): Any database over 1 Tera Byte DBs: we split backups to multiple disks to take the advantage of parallelism. So we can reduce the backup time. So, it might take somewhere from 2 to 2.5 hours instead of 6 hours. Also for VLDB databases to reduce the backup time, we only backup the tables that changes. We take this backup piece-meal backup method – This would consider as a different backup. Dev and Test Environment: Any database would consider as a development database that is under development or any kind modification or structural changes. Example, creating a brand new databse, or adding new column, removing a column, changing data type, length,creating new indexes, creating views etct We have same backup strategy for Dev and Test Environment or Databases We only take full backups for Dev and Test Environment Databases, and it is one per week, on Friday at 9 PM Retention Policy: We keep only 1 and we overwrite previous backup How long or how many copies of Backups: Prod: 3 backups Dev: 2 Backups Backup Strategy Continued: Examples:

Page 10 of 34

Backup and Recovery Theory and Interview Questions

1. 1 Full backup/week runs on every Friday at 7 PM, differential backup daily runs at 7:30 PM, and Transaction log backup every 2 hours 2. Full backup/Daily runs at 7 PM and we do not take any differential backup and Transaction log backup 3. Full backup/daily at 7 PM, and Transaction log backup every hour or every 2 hour 4. 2 Full backups – 1 on Tuesday at 7 Pm and 2 on Friday at 7 PM, and Transaction log backup every 30 minutes How do you verify and setup Database Recovery Model? Steps: 1. Right click on a Database > Properties > Options

2. So, the recovery model of this database is FULL How to backup a database? (Remember, this is a manual backup) Manually Backup/Restore when: When deploying a database from development to test environment/UAT/preproduction environment). Every two weeks there is a release (means new deployment from dev to uat/test) Page 11 of 34

Backup and Recovery Theory and Interview Questions

Right click on a database > Tasks > Back up

How does Recovery Model impact database backups? Database recovery model determines the retention period of transaction log entries. So, every minute, five minutes, hourly, etc. in order to keep the transaction log small and/or maintain a log shipping solution. Recovery Models: 1. Every Database has its own Recovery Model 2. All transactions are written to Log file and then from the Transaction Log File are written to Data File What are the different types of Recovery Models? 1. Simple Page 12 of 34

Backup and Recovery Theory and Interview Questions

2. Full 3. Bulk-logged Simple Recovery Model: - Transaction log Backup is not available - It still writes transactions to Data Cache, and Transaction Log file and finally it writes transactions to Data file from transaction log file. Transactions in the Transaction Log File are cleared/deleted after transactions are written into Data File - As soon as it completes writing the Transaction Log, it TRUNCATES the Transaction Log File. That is why Transaction log backup is not available in Simple Recovery Model - Performance will be better compare to Full and Bulk-Logged Recovery Model - When to Use: o Recommended and best practice to use in Test and Dev environment. As a matter of fact, we use this model for our test and dev environment - We also use for our production Databases for our READ-ONLY Databases. It means data does not change in database. Example No INSERT, UPDATE, DELETE of records - Transaction Log Backup: Cannot take transaction Log Backup (Disadvantage) - Transaction Log file will never fill up (Advantage) - Can take both Full and Differential Backups (Advantage) since it not always needed to take Transaction Log backup Page 13 of 34

Backup and Recovery Theory and Interview Questions

- Committed (Saved) transactions are removed from the Transaction log file when the check point process occurs Full Recovery Model: - All types of backups are available - Performance could be an issue compare to Simple Recovery Model - When to Use: o Data is Critical o For OLTP (Online Transaction Processing) Database. Example of OLTP DB: where regular transactions take place. Example of OLTP databases are Data Group Student Management System, Data Group Doc Management System, Ebay, Paypal, Amazon etc. Keep in mind that if there are regular INSERT, UPDATE, DELETE type of commands take place against any database that database is considered as OLTP database - Cannot afford to lose any data - Can take full, differential, and transaction log backup - Committed Transactions are still in Transaction Log file even after commit - Committed transactions are only removed when the transaction log backup process occurs - Transactions in the Transaction Log File are maintained after transactions are written into Data File - Transaction Log Backup: Can take transaction Log Backup - Transaction Log file will fill up (Disadvantage) Page 14 of 34

Backup and Recovery Theory and Interview Questions

- Can take transaction Log Backup (Advantage) - Bulk Operation: Bulk Insert (Example) - INSERTING 10,000 RECORDS – Transaction Log File will keep complete transactions of bulk operation (Insert 1 into ccc - For many operations like bulk, insert, update, delete Full recovery will make a Database Slower Bulk Logged Recovery Model: - Very much same as Full Recovery Model - Except: Bulk Insert, SELECT INTO, CREATE INDEX (create index IDX_ID on Customer (ID)) will not saved in TL File - Can take full, differential, and transaction log backup - Committed Transactions are still in Transaction Log file even after commit - Committed transactions are only removed when the transaction log backup occurs - Transactions in the Transaction Log File are maintained/Remain after transactions are written into Data File - Transaction Log Backup: Can take transaction Log Backup - Transaction Log file will fill up (Disadvantage) - Can take transaction Log Backup (Advantage) - For Bulk Operations, Transaction Log file will maintain the minimum information of Bulk Operation (“Bulk Operations Performed”) - Re-load all the bulk operations - Performance is faster - If no bulk operations occurred, there is no difference between Full and Bulk-Logged Recovery Model Page 15 of 34

Backup and Recovery Theory and Interview Questions

- Bulk Operation: o If there is any bulk operation like Bulk inert, those transactions will not be saved in Transaction Log file. Transaction log backup will contain all transactions except bulk operation related trunsactions What are the different types of backup? Tell me the different types of backup you are using 1. 2. 3. 4.

Full backup Differential Backup Transaction Log Backup: is not available: Simple, Special Backups: a. Copy Only Backup b. Piece-meal Backup i. File and file Group c. Tail Log Backup

5. Why transaction log file grows too much? For 1 of your database, transaction log file is growing too fast. Why? There are two reasons: 1. Databases are using either Recovery Model Full or Bulk 2. No transaction log backups are taken What is the solution if Log file is growing faster than usual? 1. Take transaction log backup and Truncate the Transaction Log file 2. Change Recovery Model to Simple 3. Resize the Log File to desired size Page 16 of 34

Backup and Recovery Theory and Interview Questions

4. Now change the Recovery Model to Full What is the default time to write transactions from Transaction Log File to Data File? 15 minutes For accidental delete of records how do you recover your database? >> Hint: You need to create new DB (Restore from Backups > EXP/IMP) What are the Extensions different backups? Full and Diff: .bak Transaction Log Backup: .trn How can I verify that backups are occurring on a daily basis? 1. Review SQL Server error log for backup related entries. 2. I can also use DMV call msdb.dbo.backupset select * from msdb.dbo.backupset 3. Review file system where the backups are kept and validate those exist or not How do you know if your database backups are restorable? How do you verify backup? 1. We can issue the RESTORE VERIFYONLY command to validate the backup RESTORE VERIFYONLY FROM disk = 'C:\SQL_DBBKUP\DB_BKUPBulk.bak'

Page 17 of 34

Backup and Recovery Theory and Interview Questions

1 (Good) 0=Bad backup 2. Randomly restore the databases using the backups in Test Environment 3. Automate the restore verify only process as part of backups. As part of job setup of backup jobs, use backup verify command 4. Using CHECKSUM. Example: >> BACKUP DATABASE DG_Training TO DISK = “L\backup\DG_Training\DG_Training.bak” WITH CHECKSUM (Verifies block using the header information) What are some common reasons why database restores fail? 1. 2. 3. 4.

Might have a wrong or corrupted backup Syntax error such as with the WITH MOVE command Unable to gain exclusive use of the database LSN's are out of sequence so the backups cannot be restored

What are the different types of database corruption? 1. Physical Corruption: Disk Damaged, 2. Logical Corruption: Compromise Data Integrity, How can you be notified if a database backup fails? Setup SQL Server Alerts to notify DBAs on a failure condition According to best practice, what is the backup retention policy?

Page 18 of 34

Backup and Recovery Theory and Interview Questions

Do not store the backups and online databases on the same server and definitely not on the same disks. If a disk failure occurs, you would lose both backups and online database with a single failure Ensure the backup policy meets all the business regulatory requirements of the organization Keep at least 1 and the latest backup on local server and others including the latest backup on the network/backup server as it would need to restore What are some common restore processes? What are some common post restore processes? 1. Sync the logins and users 2. Validate the data is accurate 3. Notify the team and user community If needed, cleanse data to remove sensitive data. For an example, SSN, credit card information, and other Personal Identifiable Information (PII) Setup the database properties. For an example, recovery model, readonly, read-write Explain how you could automate the backup process Backups can be automated by using a cursor to loop through each of the databases and backup each database Setup a job as part of maintenance plan What are the system database and system tables that keep backup and restore related information? What do each of the tables do? Page 19 of 34

Backup and Recovery Theory and Interview Questions

Database Name: MSDB Tables: Backupfile - contains one row for each data file or log file backed up backupmediafamily - contains one row for each media family backupmediaset - contains one row for each backup media set backupset - contains one row for each backup set restorefile - contains one row for each restored file restorefilegroup - contains one row for each restored filegroup restorehistory - contains one row for each restore operation Difference between Full and Bulk-Logged Recovery/Why should you select Full Recovery Model over Bulk-Logged Recovery Model? What is relationship of Database Recovery Model with Database Backup? Recovery Models: Simple, Full and Bulk-Logged Simple: Types of backup can be taken: Full and Differential Used for Env: Dev and Test - Does not provide option to take transaction log backup - Does not generate any transaction log meaning no entries in transaction log file Full:

Page 20 of 34

Backup and Recovery Theory and Interview Questions

- Keeps historical information of every transactions in Transaction Log File - Provides option to all types of backup Bulk-Logged: - Very much same as Full Recovery Model with the exception of (Bulk-Logged Recovery Model does not keep historical information for the following operations:) o Bulk Operation: Importing Data by using BCP method (Import data from Flat file) o Create index: Could become a big issue since it might decrease the perf CREATE INDEX idx_name on Table_Name(Colum Name) Example: CREATE INDEX IDX_Course on Course (Course_Number) >> If full recovery model is being used only “CREATE INDEX IDX_Course on Course (Course_Number)” will be written to Transactin Log File. So we are not losing/using that much of space. o Select into (Example: Archive Records - Ds Tools you have used to backup and Restore? 1. Native Tool: Management Studio and Query Analyzer Page 21 of 34

Backup and Recovery Theory and Interview Questions

2. Third Party Tool: Idera, Toad for SQL Server, RedGate, DBArtisan What are the different Recovery States/Modes to Restore a Database? 1. WITHRECOVERY: After restoring a database, database will be placed in normal operational mode means database is ready to use for normal operation 2. WITH NORECOVERY: After restoring a database, database will be placed in Recovery mode. It means database is ready to use for normal operation 3. WITH STANDBY

Page 22 of 34

Backup and Recovery Theory and Interview Questions

Page 23 of 34

Backup and Recovery Theory and Interview Questions

Tell me some of the maintenance plan you have in your environment?

Backup methods 1. Automatic – Daily/Corporate 2. Manual: Use only for Copy only Backup How do you setup jobs to take backup of a database? >> T-SQL/Maintenance Plan What is the best backup? - A backup that protects data Page 24 of 34

Backup and Recovery Theory and Interview Questions

- A backup that protects the database system - A backup that protects business - So, we (DBA) need to talk with business people finding their expectations - DBA need to find out is realistic or not and doable - DBA should give some recommendations - Result of this (Expectations + Recommendations): Backup strategy - If possible, best backup: Copying all data and log files from the physical location. However, it is not possible while database is running What do you need to backup? Backup all Objects that make up a database Special Note: Files are associated with a group called file group and log files are not associated with any file group A file can contains tables, views, functions, stored procedures, indexes Example, 1. Data file and log file / Data file or log file OR 2. File Group (Employees, Employees_Overseas, US_Missions, and Log File 3. Can backup 1 file group or all groups Database needs to be offline

Page 25 of 34

Backup and Recovery Theory and Interview Questions

Steps: Right click on the database (US_Missions) > Tasks > Take database offline Now copy and paste those files to the backup location What are the System Databases? Master: Contains system-wide configurations, Any database created, file, file group, index, and everything we within our database and databse server is stored in master database. Backup is needed Model: Template to create user database. Backup requires: If modfied MSDB: Jobs are stored in MSDB database. Backup is needed TEMPDB: To process temporary tasks. This database gets deleted every time we shutdown database and recreates every time we start the database server . No backup needed Difference between Full and Differential Backup: Full Backup: Copy of the exact of the database Differential Backup: Copy of changes since last full backup What are the database you backup? System Database: Master, MSDB, and Model (optional) And user databases (Database we create) What is Backup Compression? Do you use it and why? -

It compresses in size Saves space Reduces size without losing any content of the database Reduces backup time Page 26 of 34

Backup and Recovery Theory and Interview Questions

How do you restore a database as Standby Database and why? During the restoration of a database, use the option WITH STANDBY What are the different Database Restoration Modes? With Recovery: - Is in Recovered state - After the restoration place the database in normal operation mode – Ready to use With No-Recovery: - Is not in Recovered state - After the restoration place the database in RECOVERY mode – More files to recover from With Standby: - Read_Only state - After the restoration place the database in normal operation mode – Ready to use – BUT with READ_ONLY OPTION How do you REFRESH a database?? During the restore, use the restore option: WITH REPLACE How to bring a Database online if it is offline? Steps: Right click on the database (US_Missions) > Tasks > Bring Online Difference between Primary File Group and other file groups >> Primary File Group: Default file group created by the system. All tables and data will be stored in that file group if we do not specify other Page 27 of 34

Backup and Recovery Theory and Interview Questions

file group >> Secondary /Other File Group: These are not default file group to hold/contain any tables or data. We must need to manually specify the secondary file group as storage Difference between files and file group >> File: Container of table. Table is the container of data. It is the physical file where we store tables and data within the table >> File Group: Container of File or files (Folder/Direcory) Difference between offline and online Database: >> Offline: users cannot access the database >> Online: Users can access the database and perform activities need to do Difference between mdf, ndf, and ldf .mdf: extension of first data file .ndf: extension of all other data file .ldf: extension of first log files How do you refresh a database? What is Point-In-Time Recovery?

What are the different ways to move data/databases to different servers/Databases? What is unstructured data? Page 28 of 34

Backup and Recovery Theory and Interview Questions

Data cannot be broken into pieces as part data modeling point of view Examples: picture (DMV driving license, immigration or any places/databases that require to mange picture/images/graphics), Audio, Video, streaming data How do manage unstructured data? New feature: Require to activate to manage streaming related data What are the files make a database: Mdf, ndf, ldf What will be backed-up by different types of backup? Full backup will include: mdf, ndf (Committed/Saved transactions, ldf (all current uncommitted + committed transactions), unstructured, Differential backup: mdf, ndf When do you need/consider to take Copy only backup? - A backup job is scheduled - A backup job is scheduled and need to Deploy/Restore the database using a recent backup - A backup job is scheduled and need to test data in test environment - Otherwise, it will interfere with job scheduler meaning job skip the schedule date/time What are the reasons and how do you manage transaction log file? Reasons:

Page 29 of 34

Backup and Recovery Theory and Interview Questions

- Log file (transaction log) may grow too fast if not taking backup and truncating it - Lot of transactions (inserts, delete, updates) - Not taking transaction log backup. So, grow and grow since it contains transactions and will hold those transactions in that file. So, if we take transaction log file, it will Truncate those transaction what was included by that transaction log backup How do you set-up Transaction Log file size? Depends on 3 factors: 1. Frequency of transaction meaning (inserts, delete, updates) 2. Frequency of writing data from Log to data file. Default time: 15 minutes 3. Size of log file (1 MB). Fills up before 15 minutes: It will write transactions from log file (ldf) to data file (mdf/ndf) Difference between Delete and Truncate Delete: Delete data, but will not release the space for future use. In this case, database will still think the space is still in use Truncate: Delete data and will release the space for future use What are the systems database need to backup? Master: MSDB: Model (If using as a custom template) Page 30 of 34

Backup and Recovery Theory and Interview Questions

What are the steps to restore a database? If Performing only Full BKUP 3. Restore using the last Full backup with RECOVERY (Apply transaction Require to bring a DB online) If Performing Full and Differential BKUP 4. Restore using the last Full backup with NORECOVERY 5. Restore the last Differential Backup with RECOVERY If Performing Full, Differential and Transaction Log BKUP 6. Restore using the last Full backup with NORECOVERY 7. Restore the last Differential Backup with NORECOVERY Restore all Transaction Log backup in order from the last differential backup with NORECOVERY until the last one with RECOVERY What type of Recovery Model do you use and why? If a DB is/ 8. Test DB or Development DB, Simple Recovery Model – Recommended 9. Databases uses by Developer, Software Engineer/programmers/testers – Simple Recovery Model 10. For most cases, Dev and Test Environment Db – Simple Recovery Model is recommended (Full Backup) 11. Full Recovery Model Page 31 of 34

Backup and Recovery Theory and Interview Questions

o Mission Critical Databases – Cannot afford to lose data and down time o Production Databases – Recommended Practice (Best Practice) o Bulk-Logged Recovery is not a Recommended Practice. Recommend Developers to perform bulk operations after business hour How do you deploy a database? Backups Needed Environment point of view:

Transaction Architecture Related to Recovery: - Transaction: It is a logical unit of work that should either be true or false Example of transactions: CREATE TABLE TAB1 ( id INT, Name varchar (50) ) Page 32 of 34

Backup and Recovery Theory and Interview Questions

- SQL Server determines how Transactions will be written to Data Cache - Data Cache (Transaction Log Page (s) + Data Page (s)). A page is the smallest unit of data storage in SQL Server. 1 Page = 8 bytes, Extent = 8 Pages - Before Commit: 1st SQL Server writes data into Data Cache/Memory/Buffer/Buffer Cache (Transaction Log Page and then write to Data Page - Uses to Recovery Data: UNDO - After Commit: If commits, Data from Data Cache will be written to TL - Uses to Recovery Data: Need to backup TL and can be used - CHECK-POINT: Default time for check-point: 15 minutess - After check-point, data Transaction (s) will be written to Data File

Sample T-SQL Code Full backup with Schedule: EXECUTE master.dbo.xp_create_subdir N'L:\Backups\BKUP_Demo' GO BACKUP DATABASE [BKUP_Demo] TO DISK = N'L:\Backups\BKUP_Demo\BKUP_Demo_backup_2013_02_26_22110 4_3288649.bak' WITH NOFORMAT, NOINIT, NAME = N'BKUP_Demo_backup_2013_02_26_221104_3278648', SKIP, REWIND, NOUNLOAD, STATS = 10 GO declare @backupSetId as int select @backupSetId = position from msdb..backupset where database_name=N'BKUP_Demo' and backup_set_id=(select Page 33 of 34

Backup and Recovery Theory and Interview Questions

max(backup_set_id) from msdb..backupset where database_name=N'BKUP_Demo' ) if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''BKUP_Demo'' not found.', 16, 1) end RESTORE VERIFYONLY FROM DISK = N'L:\Backups\BKUP_Demo\BKUP_Demo_backup_2013_02_26_22110 4_3288649.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

Page 34 of 34