What is RPO/RTO?

Time is money!

     Your boss keeps talking about RPO (Recovery Point Objective)  and RTO (Recovery Time Objective).  Do you just nod your head like you know what he/she is talking about?  Maybe that scenario just happened and you are searching the internet for what these terms mean.  If so, welcome!  No one likes to think about disasters, but they happen all too often.  Planning for the worst and hoping for the best will keep your data safe and your job even safer. Let’s take some time and explore what RPO and RTO mean, why these things are important, and what you need to do next to be a hero DBA!

RTO (Recovery Time Objective)

     Recovery Time Objective is the amount of time in which your company expects you to have the database fully restored after a disaster.  That is, how much downtime is acceptable for disaster recovery or planned outages.  Each company is different, and most reference RTO in terms of nines. 

     The nines measure for a company that measures 365 days a year, 24 hours a day as follows:

5 9’s – 99.999% (this translates to about 5 minutes of acceptable downtime per year)
4 9’s – 99.99% (this translates at about 52.5 minutes per year and is much easier to achieve)
3 9’s – 99.9%  (this translates at about 8.75 hours per year)
2 9’s – 99% (translates to about 3.5 days a year)

     To decide what RTO is best for your company, you need to take into consideration your data needs.  Not all companies run on a 365/24 schedule.  Some companies only measure downtime between 8am-6pm Monday through Friday, or only on the weekends.   This will drastically change the translation of the 9’s.  Another thing to think about is whether the measured downtown includes time for maintenance or patching, times when the database must be offline.  If maintenance time is eliminated from consideration, meeting the higher 9’s is much easier.

     If your company insists on an RTO of 5 Nines and does not take into consideration maintenance or patching, then you must speak with the persons in charge to discuss the RPO.  It is possible to adhere to the strict 5 minutes of downtime, but the point at which you are able to recover, will definitely be restricted.

RPO (Recovery Point Objective)

     Recovery Point Objective is the level of data or work that is acceptable to lose in the event of a disaster.   Ideally, companies will want ZERO data or work loss.  While that IS achievable, it will all depend on  valid backups and the extent of damage the database suffered at the point of disaster.  

     An RPO of 15 minutes means that the data and work must be recoverable to a point within 15 minutes of the disaster, meaning that it is expected that only 15 minutes of work or data may be lost.  Stop right here and think about your backup  plans and recovery modelsRestoring a database that is in simple recovery model should not take as long as a restoring one in full recovery model. It is important to remember (from previous blog posts), the recovery model dictates how much data you can recover. It is also important to remember, the ability to recover ANY data at all is fully dependent on having valid backups.

Run Book

     Another term you might hear is “Run Book”.  A Run Book is a physical or digital collection of information that is needed to restart the database in case of disaster.  There are many items that should be included in the runbook.  Some of the essential items one should consider having in the runbook are:

  • Server level info, configuration, purpose, etc.
  • List of all databases and applications using them
  • List of agent jobs and proper response to a failure
  • Disaster Recovery process with all contacts, RPO/RTO, etc. required to bring it back (based on level of issue)
  • Security
  • Backup schedules

     When considering a run book, think about what someone would need if they were new to the company and the only person available to restart the database.  What information would that person need?  Making sure your run book is up to date on a regular basis is certainly a great idea!

Preparing for disaster

     Keep in mind that if you prepare for the worst, you will be less likely to be caught off-guard with a manager breathing down your neck asking “WHEN WILL WE BE BACK UP AND RUNNING?!?!”  Do you have any idea how long it will take to restore your database?  If your answer is “no,” I would suggest doing a restore to see how long this takes.  Further, I would suggest making it a habit to perform drills so that you and your team know what to do in the event of a disaster, and exactly how long it takes to get your company back up and running. Having a solid backup schedulevalidating those backups, and keeping your company’s expectations in mind, you will be ready to handle any data disaster that may be thrown your way.  

*Originally posted at Procure SQL:
https://www.procuresql.com/blog/2018/08/31/what-is-rpo-rto/

 

Tail Log Backup

In my SQL Server Recovery Models blog, I touched a bit on my experience with recovery using Tail Log Backup.  In this post we will take an in-depth look at Tail Log Backup; what they are, why they should be in your toolbelt, and lastly line out the steps to successfully take a Tail Log Backup.

What is a Tail Log Backup?

Simply put, a Tail Log Backup contains log records that were not yet backed up at the time of failure.  So if Transaction Log Backups occur every 15 minutes, and you suffered loss at the 11 minute mark, the Tail Log Backup includes all data changes during the time span between the last successful Transaction Log Backup and minute 11.  Recovery using the Tail Log backups can be performed in either Full Recovery or Bulk Logged Recovery, but cannot be used in Simple Recovery Model. 

Why are these important?

Is it possible to recover with no data loss?  YES*!  This is where our new friend comes into action!   Taking a Tail Log backup is done to prevent data loss and helps recover right up to the point of disaster. (This is also referred to as Point In Time Restore.)

Keep in mind: in order to recover your database to its most recent point, you must have a valid Full Backup and valid Transaction Log Backup sequence!

After a disaster, if you can take a Tail Log Backup, have all the preceding Transaction Log Backups, have a valid Full Backup, and you are in Full Recovery mode, it is possible to recover with NO DATA LOSS!  For this to be possible in Bulk-Logged Recovery mode, no minimally logged operations must have occurred.

In what case would you ever need a Tail Log Backup?

Any time you have a damaged database and are needing to restore, it is best to check to see if you need a Tail Log Backup.  The question you need to ask is “Do I have Transaction Log Backups?”   If the answer is yes, your recovery will be much faster!  Another question to ask is “Is the Server still available?”

Server Still Available

If the database is damaged but the server is still available, it is pretty easy to take a Tail Log Backup.  When the data files are damaged or missing, you will get an error if you try to take a normal log backup.  But if you use NO TRUNCATE, you will be able to take a Log Backup.

BACKUP LOG [TestDB] TO DISK = 'G:\DBA\Backups\TestDB_Log_Tail.bck' ;
WITH INIT,
NO_TRUNCATE;

*Note:  In order to successfully take a Tail Log Backup, you must use NO TRUNCATE. That will allow the log backup even if the database files are damaged or not there.  Using INIT will overwrite any existing set and you will still end up with only one backup in case the command is run twice.

Server Not Available

Let’s say the server has crashed and cannot be brought back online.  If you are lucky enough to still have access to all the Full Backups and Log Backup files, you can attach them to another server and automatically recover.

If the database is damaged and the server is not available, taking a Tail Log Backup becomes a little more difficult. Rest assured, there is still an option to try.

You will need to create a dummy database with the same name as the one that is damaged.

  1. Next, set the database offline.
  2. Delete the files from the dummy database.
  3. Drop in the log file from the real database.

--Create a dummy database with the same name
CREATE DATABASE [TestDB];
GO
--Set the database offline
ALTER DATABASE [TestDB] SET OFFLINE;
GO
--Insert Log file from original database--Take Tail Log Backup
BACKUP LOG [TestDB] TO DISK = 'G:\DBA\Backups\TestDB_Log_Tail.bck' ;
WITH INIT,
NO_TRUNCATE;
GO

Now you are ready to take a Tail Log Backup as detailed above.  This will allow you to recover to the point of failure!  In my next post, we will do a deep dive into Recovery Using Tail Log Backups.

Thank you for reading!

 

*Originally posted at Procure SQL:
https://www.procuresql.com/blog/2018/09/19/tail-log-backups/

 

Deep Dive into Bulk Logged Recovery Model

    Full disclosure time: Bulk Logged Recovery Model is  quite confusing to me.  And as it seems, to many others.  I wrote a bit about it in SQL Server Recovery Models and decided that it was so complex, I really wanted to learn more and to explore what works and what doesn’t.  Let’s take a deep dive into bulk logged recovery!

Deep Dive into Bulk Logged Recovery

Why would you choose Bulk Logged Recovery?

    Switching from full recovery to bulk logged recovery does have its perks when you have a very large amount of data to insert.  Most notably in a data warehouse setting, switching to bulk logged recovery to perform bulk inserts make perfect sense as you are dealing with very large amounts of data being updated at one time.  Also, when doing an index rebuild switching to bulk logged recovery can improve performance while performing operations on large amounts of data at once.

Are there better ways to insert a large amount of data at once?

     Bulk Logged Recovery uses minimal logging for bulk-logged operations, this reduces log space usage. I must add a caveat here; it makes it faster and reduces the usage in the file, but it results in a very large log backup. 

“Under the bulk-logged recovery model, if a log backup covers any bulk operations, the log backup contains both log records and the data pages that were changed by bulk operations. This is necessary to capture the results of the bulk-logged operations. The incorporated data extents can make a log backup very large.” Reference Myths & Truths

     However, there is a risk of data loss for the bulk operations because these operations prevent capturing changes on a transactional basis.  A point in time recovery (PIT) while using bulk logged recovery is not possible because the minimally logged operations cannot be restored.  This can be an issue. So, if you have a bulk operation that needs to be handled, but you want to ensure point in time restore of each transaction in that operation, what is an alternative solution?  It is important to note that you can indeed recover a transaction log containing bulk logged operations but not to a particular point in time.  Instead you can take a transaction log backup as soon as the bulk operation is finished and regain PIT recovery.

     You can still perform bulk operations in full recovery model, it just means that they will be fully logged and that you will not see the performance gains from minimal logging.  It is the price you have to pay, you sacrifice performance for PIT restore abilities of the transactions within the bulk operation.  Ultimately your decision will have to be based on weighing what your company demands for I/O and RPO/RTO (Recovery Point Objective/Recovery Time Objective).  Do you know what your company’s RPO/RTO plans entail?  Now is a good time to find out!

     Feeling uneasy?  Wondering if there are there other ways to process bulk operations?

     There are different methods one can utilize for optimizing bulk operations.  These methods include using minimal logging, batching, disabling triggers and constraints, and many others that can be found here

How best to ensure limited data loss using Bulk Logged Recovery.

    So you decide you are going to use bulk logged recovery and you want to make sure that you are set up for success, there are a few things to keep in mind.  It is recommended that you perform bulk inserts using bulk logged recovery when there is the least amount of activity on your database.  Also take into consideration how difficult or easy it will be to recreate data if there is a failure during the bulk insert.  There is no PIT restore of the bulk operation using bulk logged recovery.  If the bulk operation is interrupted at any point, the entire operation must be performed again in its entirety.

Still want to proceed?

Wait!   

First, before you switch from full recovery, take an extra log backup.  If all things go badly, at least you will be able to get your database back to the point before you switch recovery models.  This is highly recommended!  If not, this is what we call an RGE (resume generating event).

     Let’s walk through the process of getting ready and switching recovery models.  Our first step in this exercise is to create a table.  We then go on to taking a log backup, inserting data manually, taking a log backup, and then on to switching to bulk logged recovery.

This is a great visual of what we will be doing.

 


--Step 1--

Use BLRDB
GO

DROP TABLE dbo.BulkDataTest;

CREATE TABLE dbo.BulkDataTest
(Price money NULL,
ProductID int PRIMARY KEY NOT NULL,
ProductName varchar (25) NOT NULL,
ProductDescription text NULL)
GO

BACKUP DATABASE LOG [BLRDB]
TO DISK ='C:\DBA\Backups\BLRDB_Log_02232018_0800.bak'

-- Step 2--
Insert into dbo.BulkDataTest
(Price, ProductID, ProductName, ProductDescription)
VALUES ('456', '456123', 'HeroBike', 'Red Bike with Hero Cape Handles');

-- Step 3 --

BACKUP LOG [BLRDB]
TO DISK ='C:\DBA\Backups\BLRDB_Log_02232018_0815.bak'

-- Step 4 --
-- Switch to Bulk Logged Recovery Model --
USE [master]
GO
ALTER DATABASE [BLRDB] SET RECOVERY BULK_LOGGED WITH NO_WAIT
GO
-- verify Recovery mode  This script will ensure that you are in the desired recovery model--
select name, recovery_model_desc from sys.databases

Our next steps will be to insert our bulk data, insert manual data, take log backups, switch back to Full Recovery, and take an additional log backup.

-- Step 5 --
-- Commit both transactions below at the same time--
USE [BLRDB]
GO

BULK INSERT BulkDataTest
FROM 'C:\DBA\TestDocs\demo_bulk_insert_26.csv'
With (FIELDTERMINATOR = ',' ,
ROWTERMINATOR = '\n' ,
ROWS_PER_BATCH = 100000,
TABLOCK
);
GO

INSERT INTO BulkDataTest
(Price, ProductID, ProductName, ProductDescription)
VALUES ('1099', '1111111', 'HoverCraft', 'BippityBoppityBoop');

Select *
From dbo.BulkDataTest

-- Step 6 --
--take log backup--

BACKUP LOG [BLRDB]
TO DISK ='C:\DBA\Backups\BLRDB_Log_02232018_0900.bak'

-- Step 7 --
--Insert more rows manually--

INSERT INTO dbo.BulkDataTest
(Price, ProductID, ProductName, ProductDescription)
VALUES ('56', '111117', 'TheCheap', 'One of the cheapest bikes ever made'),
('58' , '111118', 'NewerModel', 'This one is for beginners'),
('591' , '111119', 'ABetterOne', 'Okay this one is good') ;

-- Step 8 --
-- Switch back to Full Recovery Mode--
USE [master]
GO
ALTER DATABASE [BLRDB] SET RECOVERY FULL WITH NO_WAIT
GO

Use BLRDB
GO
-- Step 9 --
--Insert more rows manually--
INSERT INTO dbo.BulkDataTest
(Price, ProductID, ProductName, ProductDescription)
VALUES ('36', '111120', 'BoyBike', 'This is a bike for tall 8yo'),
('136', '111121', 'ManBike', 'This is a bike for tall men'),
('236', '111122', 'ShortBike', 'This is a bike for under 5foot');

-- Step 10 --
--Take Log Backup--
BACKUP LOG [BLRDB]
TO DISK ='C:\DBA\Backups\BLRDB_Log_02232018_0930.bak'

Ready for a challenge?

Now let’s simulate a dropped database and walk through restore! The following steps will walk you through to be able to answer the questions below.

Question 1: Restore backup to step 6.  What is missing?  Do you have the single row inserts?  Is the data from the bulk insert there?


--Drop Database--
USE Master
GO

DROP DATABASE [BLRDB]

--Restore Full Backup --

RESTORE DATABASE BLRDB
FROM DISK ='C:\DBA\Backups\BLRDB_Full_02232018.bak'
WITH NORECOVERY;

RESTORE DATABASE BLRDB
FROM DISK ='C:\DBA\Backups\BLRDB_Log_02232018_0800.bak'
WITH NORECOVERY;

RESTORE DATABASE BLRDB
FROM DISK ='C:\DBA\Backups\BLRDB_Log_02232018_0815.bak'
WITH NORECOVERY;

RESTORE DATABASE BLRDB
FROM DISK ='C:\DBA\Backups\BLRDB_Log_02232018_0900.bak'
WITH NORECOVERY;

RESTORE DATABASE BLRDB
WITH RECOVERY;

use [BLRDB]
go

SELECT *
FROM dbo.BulkDataTest

--For Qustion 1, Restore Step 6 --

USE Master
GO

RESTORE DATABASE BLRDB
FROM DISK ='C:\DBA\Backups\BLRDB_Full_02232018.bak'
WITH NORECOVERY;
GO

RESTORE DATABASE BLRDB
FROM DISK ='C:\DBA\Backups\BLRDB_Log_02232018_0800.bak'
WITH NORECOVERY;
GO

RESTORE DATABASE BLRDB
FROM DISK ='C:\DBA\Backups\BLRDB_Log_02232018_0930.bak'
WITH NORECOVERY;
GO

RESTORE DATABASE BLRDB WITH RECOVERY;
GO

use [BLRDB]
go

SELECT *
FROM dbo.BulkDataTest

Question 2: Restore to backup at step 10.  What is missing?  Do you have everything?


-- For Question 2, Restore Step 10 --
-- Drop DB--
USE Master
GO

DROP DATABASE [BLRDB]
GO

USE Master
GO

RESTORE DATABASE BLRDB
FROM DISK ='C:\DBA\Backups\BLRDB_Full_02232018.bak'
WITH NORECOVERY;
GO

RESTORE DATABASE BLRDB
FROM DISK ='C:\DBA\Backups\BLRDB_Log_02232018_0800.bak'
WITH NORECOVERY;
GO

Restore Database BLRDB
FROM DISK ='C:\DBA\Backups\BLRDB_Log_02232018_0815.bak'
WITH NORECOVERY;
GO

Restore Database BLRDB
FROM DISK ='C:\DBA\Backups\BLRDB_Log_02232018_0900.bak'
WITH NORECOVERY;
GO

Restore Database BLRDB
FROM DISK = 'C:\DBA\Backups\BLRDB_Log_02232018_0930.bak'
WITH RECOVERY;

USE BLRDB
GO

SELECT *
FROM dbo.BulkDataTest

Swimming back up.  Swimming Back Up

     We have taken the plunge and now it is time to swim back to the surface and discuss what we have found.  Working through this exercise and answering these questions should show you how bulk logged recovery model works, why it is important to switch back to full recovery, and most importantly, why full and log backups are a must!

     What are your answers?  Your answers should show that if you are only able to restore to step 6 (the point at which you took a log backup but had not yet switched back to full recovery and taken another log backup) all data operations that were performed since your bulk operation have to be recreated!  The second answer should show you that it is imperative to take a log backup before switching to bulk recovery, take a log backup after your bulk insert, and take another log backup after reverting back to full Recovery.  If you follow those steps, your answer shows that all of your data operations were in fact logged and can be restored up to the last log backup without data loss.

*Originally posted on Procure SQL at  https://www.procuresql.com/blog/2018/07/18/deep-dive-into-bulk-logged-recovery-model/

 

Paint Your Success

I like to listen to people.  Really listen.  Listen to what they are saying and what they are NOT saying.  Sometimes the answers are clear and other times the answers are hidden.  One conversation in particular struck me.  It was a conversation between two people and one was complaining that he was not being heard by his team.

This made my mind start thinking about ways in which we are either not heard, or are dismissed.  Perhaps this person was having that issue because he lacked clear communication of his ideas, or was not seen as a strong member of the team.  Verbal, written, and non-verbal communication are key to gaining ground in the areas you desire.

Communication Styles

Non-verbal communication is the ice breaker.  Think about the way you appear walking into a meeting.  Is your head held high?  Do you smile? Do you look people in the eyes when you speak to them?  The very first instance someone sees you is your first opportunity to make the impression you desire.  This first impression will set the tone for the discussion. Walk in confident and friendly.  Your audience will take note that you are there to do business and are invested in communicating with them.  They will feel welcomed and at ease.

Verbal communication skills truly are an art form.  Being able to craft sounds and thoughts into words and paragraphs comes easy to some. For others, this is a craft that seems daunting and elusive.  Being an extrovert helps me excel in my communication skills.  I am largely unafraid to speak with new people.  I thrive in meeting new people and sharing ideas.  However, for those who are more introverted, verbal communication is very uncomfortable and often crippling.  If you are one who has trouble with verbal communication, consider honing your written communication skills while you work on gaining comfortable levels with your verbal communication skills.

Written communication can emphasize your point, make an idea memorable, or totally destroy a project.  Of course we want to give our projects as much growth potential as possible, so being able to formulate a well written idea is just as important as verbal communication.  Written communication is tricky because the reader is not afforded the opportunity to see the facial expressions of the writer.  So often times, jokes come off as harsh criticisms.  The tone of a person’s writing is just as important as the words the writer uses to convey a message.

Painting

Bring Forth Your Inner Picasso

Think of creating a painting.  Before creating your masterpiece, you must start with a properly prepared canvas.  Stretching muslin onto a wooden frame is the first step to creating a painting.  The second step is to temper the muslin with a wash of water-thinned paint.  This will allow the muslin to harden and provide a strong base for the paint to stick.  The next step is to allow your creativity to flow through colors, shapes and movement.  Before you know it, you have created a lovely piece of artwork that is truly showing of who you are.

Being Picasso

That confused look on your face is one that I see often.  It is okay, I speak in odd metaphors at times.  I paint life with an interesting brush.  Let’s explore how you can become Picasso of the Morning Stand-up and get your ideas heard!

  • First, start with the way you enter the room for the meeting.   This is your non-verbal communication that sets the stage for the meeting or conference.  Head up, smile, look people in the eyes.  Assert your position in the room and welcome others into your space. (Stretch your muslin onto the frame and temper the canvas.)
  • Second, allow your ideas to flow through well-crafted words and examples.  Show data to back up your claims.  Explain what reports mean, show the ups and downs.  Give your audience the desired information in the most direct and informative way. (Paint that beautiful masterpiece!)
  • Third, a nice written follow up will concrete your ideas and give your audience a physical takeaway.  Concentrate on the content and intent of your proposal.  Pay attention to your tone and remember that there may be someone reading the report who was not in the meeting to see experience your passion.  (Hang that masterpiece in a gallery!)

Communication is just one of the many soft skills that can help propel your career.  I have a session called Become the Most Valuable Player: Soft Skills for the Hard Market.  This year I have been focused on presenting this session at various SQL Saturdays around the country.

Demystifying System Databases

Recently I have heard a lot of people discussing SQL Server System Databases.  The topic of system databases seems to be a deceptively basic one.  But how many people truly take the time to understand what system databases are and what purpose they serve?  Come along with me and let’s explore system databases.

System Databases

What are System Databases and what do they do?

System Databases are needed for your SQL Server to operate.  These include Master, Model, MSDB, Resource, and TempDB.  For Azure SQL Database, only Master and TembDB apply.

  • Master – The Master Database records all the system-level information for and instance of SQL Server. This information includes logon accounts, linked servers, and system configuration settings.  The Master also records the existence of all other databases and the location of those files, and records the initialization information for SQL Server.  This means that SQL Server CANNOT START if the Master database is unavailable.  Think of this like the master key to your SQL Server door.
  • Model – The Model Database is used as the template for all databases created on the instance.  Modifications can be made to the Model DB that will be applied to all databases created after the Model DB has been altered.  These changes include database size, collation, and recovery model.  A full list of options that can/cannot be modified on a Model DB for SQL Server 2016 is available here. The list for SQL Server  2014 Model DB options is located here. And for SQL Server 2012 the options are here.
  • MSDB – The MSDB database is used by SQL Server Agent for scheduling alerts and jobs. It is also used by Service Broker, Database Mail, SSIS, data collector, and policy based management.   SQL Server maintains a complete history of all online backups and restores within the tables in MSDB.  This history includes the name of the person or program that performed the backup, the time of the backup, and the drives of files where the backup is stored.  SQL Server Management Studio then uses this information to propose a plan for restoring a database and applying any transaction log backups.
  • Resource –  The Resource database is a read-only database that contains all the system objects that are included with SQL Server.  The Resource database does not contain user data or metadata.  Since it is a read-only database you will not see it listed on your instance as the other databases in the photo above.
  • TempDB – The TempDB Database is a database that is available to all users connected to the instance of SQL Server.  It is used to hold objects that are created by users such as temporary tables and indexes, temporary stored procedures, table variables, and cursors.  It also stores objects that are created internally such as work tables, work files, and sort results for operations such as creating or rebuilding indexes.  Think of  TempDB like the “junk drawer” in your home.  Each item is needed at specific times, then is thrown into the drawer to sit a while.  More items are thrown in the drawer.  Everyone throws items in the drawer.  Eventually the drawer becomes too full and it begins to spill out.  No one ever wants to clean out the junk drawer, and eventually you need a bigger drawer.
    • Restrictions – Despite all of the operations that can be performed on the TempDB, the following are operations that CANNOT:
      • Adding filegroups.
      • Backing up or restoring the database.
      • Changing collation. The default collation is the server collation.
      • Creating a database snapshot.
      • Dropping the database.
      • Dropping the guest user from the database.
      • Enabling change data capture.
      • Participating in database mirroring.
      • Removing the primary filegroup, primary data file, or log file.
      • Renaming the database or primary filegroup.
      • Running DBCC CHECKALLOC.
      • Running DBCC CHECKCATALOG.
      • Setting the database to OFFLINE.
      • Setting the database or primary filegroup to READ_ONLY.

You must backup your TempDB!  True or False?

In a previous blog, I discussed that the Most Important Role of a SQL Server DBA  is the ability to understand and perform backups and recovery.  I went on to discuss backups in A Beginner’s Guide to SQL Server Backups as well as Recovery Models.   Despite the importance of backups and recovery, the only database that cannot be backed up or restored is TempDB!  Why can it not be backed up or restored? TempDB is recreated each time the server is restarted, so any temporary objects like tables, indexes, etc., are cleared automatically.  As seen here, backup nor recovery are even an option!

TempDB No Backup

 Should System Databases be backed up?

Before I answer this right off, ask yourself these questions: “Do I want to lose access to all other databases on the instance?  What would happen if I lose the model my company demands for the specific way every new database must be created?  Would anyone notice if I had to restore after a disaster and no one had correct passwords to the database?”  That shiver that just ran up your spine is your answer.  Absolutely YES, the system databases (Master, MSDB, and Model) should have backups!

It is recommended that the Master be backed up as often as necessary to protect the data: a weekly backup with additional backups after substantial updates is highly recommended.  If for some reason the Master becomes unusable, restoring from the backup is the best way to get up and running.  Go here for information on Restoring the Master. If you do not have a valid backup of the Master, rebuilding the Master is the only option.  You can click here to find more information about what it takes to Rebuild System Databases.

Best practices recommend creating full backups of the Model Database, and doing so only when necessary for your business needs.  It is a small database that rarely sees changes; however, it is important to make sure it is backed up especially immediately after customizing its database options.  Microsoft also recommends to perform backups on the MSDB database whenever it is updated.

Feeling like this might be too much trouble?  As with any other backup, these too can be automated by using a SQL Agent job!   There is absolutely no reason NOT to back up your system databases as often as you backup your regular databases.  If you feel otherwise, might I suggest you keep an updated resume close at hand.

Which recovery model should be used?

This brings us down to recovery modes.  As a default the Master and MSDB are set to Simple recovery model, the Model is user configurable; however best practices recommends setting MSDB to Full recovery model, especially if the backup and restore history tables are used.

*Note that if you change the recovery model to Full, transaction log backups will need to be performed as well.  You don’t want your Master or MSDB logs to become full and risk losing all your data!

Quiz Time!

Pop Quiz

Pop quiz:  How often should you back up your TempDB?
(a) once a month
(b) weekly
(c) daily
(d) yearly on February 30th

Answer:  Trick question!  TempDB cannot be backed up.

Thank you for reading!

 

*Originally posted at Procure SQL:
https://www.procuresql.com/blog/2018/06/25/demystifying-system-databases/

Validating SQL Server Backups

You have your SQL Server Backup Plan and your Database Recovery Model set.  How do you know if your Backups are good?  TEST!  Validating SQL Server Backups will ensure that you are in a good place when it is time to bring your database back from the dead!

Don’t assume that your Backups are solid and let them sit on a shelf.  Corrupt backups are recoverable, but worthless.

“Testing leads to failure, and failure leads to understanding.” Burt Rutan

 There are several methods for validating your Backups.

  • RESTORE –  The most effective way to validate that your backups are good is to run a test Restore.  If your Restore is successful, you have a solid backup.  Make sure to run a test restore on your Full, Differential, Point in Time, and Transaction Logs!   “Bonus points” if you automate refreshing non-production.
  • Backup with CHECKSUM It may not be realistic to run regular test restores on every single database, this is where CHECKSUM is your friend.  CHECKSUM is part of a backup operation which will instruct SQL Server to test each page being backed up with its corresponding checksum, making sure that no corruption has occurred during the read/write process.  If a bad checksum is found, the backup will fail.  If the backup completes successfully, there are no broken page checksums.
    • BEWARE though, this does not ensure that the database is corruption free, CHECKSUM only verifies that we are not backing up an already-corrupt database. (Later in this post we discuss checking data for corruption.) If it seems like too much trouble to write a CHECKSUM script every time you want to perform a backup, keep in mind that these can be automated as SQL Agent Jobs!  A sample T-SQL script for using CHECKSUM is as follows:


Backup Database TestDB
To Disk='G:DBABackupsTestDBFull_MMDDYYYY.bak'
With CheckSum;

  • VERIFY – It is not wise to rely solely on CHECKSUM, a good addition is to use RESTORE VERIFYONLY.  This will verify the backup header, and also that the backup file is readable.  Note that much like CHECKSUM, this will check to see if there are errors during the read/write process of the backup; however, it will not verify that the data itself is valid or not corrupt.  Despite the name “RESTORE VERIFONLY”, it does not actually restore the data.   VERIFY too can be automated to perform each time your backups utilizing CHECKSUM run.
  • CHECKSUM on Restore –  Databases where BACKUP WITH CHECKSUM have been performed can then be additionally verified as part of the restore process.  This will check data pages contained in the backup file and compare it against the CHECKSUM used during the backup. Additionally, if available, the page checksum can be verified as well. If they match, you have a winner…
    More Details on CHECKSUM and BACKUP CHECKSUM

 


Restore Database TestDB;
From Disk='G:DBABackupsTestDBFull_MMDDYYYY.bak'
With VerifyOnly;

Data Validation Prior to Taking Backups

Keep in mind that if your data is corrupt prior to a backup, SQL Server can BACKUP that CORRUPTED DATA.  The validation methods mentioned above guard you against corruption occurring during backups, not against corrupted data within the backup.  For data validation prior to backups being run, it is suggested that DBCC CHECKDB be performed on each database on a regular basis.

  • DBCC CHECKDB –  SQL Server is very forgiving and will usually backup and restore corrupted data.  A best practice is to run a DBCC CHECKDB on your data to check for potential corruption.  Running CHECKDB regularly against your production databases will detect corruption quickly.  Thus providing a better chance to recover valid data from a backup, or being able to repair the corruption. CHECKDB will check the logical and physical integrity of the database by running these three primary checks*:
    • CHECKALLOC – checks the consistency of the database;
    • CHECKTABLE – checks the pages and structures of the table or indexed view; and
    • CHECKCATALOG – checks catalog consistency.

 Automate Validation Steps

Corruption can happen at any time, most of the time it is related to a hardware issue.  Automating the steps necessary to validate your data and backups will help ensure you have the best practices in place to efficiently recover from catastrophic data loss.  Being able to backup and restore is not as important as being able to recover with valid data.   Despite the above keys for validation, the only true way to verify that your backups are valid is to actually restore the database.  It bears repeating: corrupt backups are recoverable, but worthless.

*A full list of DBCC CHECKDB checks can be found here.

*Originally posted at Procure SQL:
https://www.procuresql.com/blog/2018/04/17/validating-sql-server-backups/

 

SQL Server Recovery Models

In my last post (A Beginner’s Guide to SQL Server Backups) we discussed the basics of SQL Server Backups.  As Backups are the foundation for Recovery, the next logical discussion should be Recovery Models.  For reference, I have included this Sample Backup Plan from the earlier post:

Sample Backup Plan
Sample Backup Plan

There are Three SQL Server Recovery Models

  • Simple Recovery Model – This is the most basic of the Recovery models.  It gives you the ability to quickly recover your entire database in the event of a failure; (or if you have the need to restore your database to another server) however, this only recovers data to the end of the last backup. Thankfully, Differential Backups can be utilized with this recovery model.  NOTE: Any changes made after the last Full or Differential Backup will be lost.  Transaction Log Backups cannot be used in Simple Recovery Model.  There is no Point-In-Time recovery in this model (recovering to a specific transaction or point in time).  Looking at the sample backup plan above… if you have an issue occur Saturday afternoon, you will lose any changes in data since your last Differential Backup that was run Friday night.  If you do not opt for Differential Backups and only perform Full Backups once a week, your changes and data will be lost for the full week.  (GASP!)
    • Why would you ever consider using such a basic, Simple Recovery Model?  Actually, there are a few really good reasons why you choose (and perfectly safe) to use Simple Recovery Model:
      • Your data is not critical and can easily be recreated
      • The database is only used for test or development
      • Data is static and does not change
      • Losing any or all transactions since the last backup is not a problem
  • Full Recovery Model – This is the most inclusive of the Recovery models; you can recover (depending on your valid backups) data up to the last transaction that was run before the failure.  Log backups are a must for this recovery model, including Tail Log Backups (which will be discussed at length in a later post).  Data can be restored to a specific point in time (once again, depending on your backup plan)!
    • Let’s explore that for a moment using the sample backup plan above.  Say you take Full Backups on Sunday night, Differential Backups every week night, and Transaction Log Backups every half hour.  If you have a failure and must restore data, by using all the backups to the specific time you need to restore, you can recover data at any moment in time!  Using this recovery model, you have a potential of minimal to no data loss (once again…depending on your backup plan and whether your backups are restorable).  You want to be the DBA who is able to restore quickly and recover data to the very last moment before the failure or crash occurred.  YES, we all want to be THAT person!
  • Bulk Logged Recovery Model – Once again, this Recovery model requires log backups in order to prevent the log file from continually growing.  This is a special purpose recovery model that is not widely used, and Microsoft states that it should only be used “intermittently to improve the performance of certain large-scale bulk operations, such as bulk imports of large amounts of data.”   It allows high performance bulk-copy operations and is only available when in Full Recovery.  Keep in mind that with the Bulk Logged model you can recover to the end of any backup; however, if a bulk transaction has occurred during the last log backup, those transactions will have to be redone.  From the bulk operation on, you can no longer utilize point in time restore.

OOOOOOOOOO, I dropped the ENTIRE database!

While studying Backups and Recovery, I have been working in SQL Server 2016 AdventureWorks Database (Download Here).  To see if I REALLY understood the process of Backup and Recovery, I made a Copy Only Backup of my original database, made some changes to my database, took Differential Backups, made another Full Backup, made more changes, and made more backups….and then dropped EVERYTHING.  POOF….gone.  While I did do this intentionally, my heart was racing.  Sparkly spots darted in and out of my vision, and my heart pounded in my ears.  Would I be able to get it back?  Would I be able to restore it?  WHAT HAVE I DONE??????

Photo Credit @Lance_LT
I Dropped the DB! Photo Credit @Lance_LT

How do I know that my backups were good?

I firmly believe you learn more from one of your failures than you do from one hundred of your successes.  TEST TEST TEST TEST those backups!  I am afforded some extra wiggle room while I am working on my virtual machine and not actually working on a client’s production database.  So, I did it, I dropped that DB like a hot rock.  I attempted to restore my database in Simple Recovery Model.   My Differentials would not restore.  Somewhere along the way in the backup or restore process, I messed up.   (I will give you a hint at my mistake, I did not restore the Full Backup with NORECOVERY before I tried to restore the Differential Backup with RECOVERY – we will go into Recovery processes in a later post).

Knowing that I had made a Copy Only Backup before I started monkeying around with the database, I set out to restore faith in myself.  And boom, I restored the Copy Only Backup!  The day was saved!  Well, mostly.  I lost some data changes, but still had the original database, so that was good-ish.

Lessons Learned

At a later date I repeated the steps above and was able to fully recover a database that was previously in Simple Recovery Model complete to the last Differential Backup (noting my mistake and learning from it).  I additionally created another database to test the Full Recovery Model with Full Database Backup, Differential, Transaction Log Backups, also utilizing Tail of the Log backup to get to the very last transaction that was run after the last backup and before my data files AND database were deleted. (This is pretty in-depth and is covered in Recovery Using Tail Log Backup.)

Please come back next time when we will explore ways to validate those backups.

Thank you for reading!

 

*Originally posted at Procure SQL:
https://www.procuresql.com/blog/2018/02/13/sql-server-recovery-models/