Posts

Great question!

     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!  

Master

     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.

Model

       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. 

MSDB

     Microsoft recommends to perform backups on the MSDB database whenever it is updated.  

TempDB

      What about TempDB?  Isn’t it a System Database as well?  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!  

“But, backups take so long to run!”

    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.  If you feel otherwise, might I suggest you keep an updated resume close at hand.

    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.

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.

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 hereThe 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!

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!

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.

As was discussed in a previous blog, (The Most Important Role of a SQL Server DBA) Backups and Recovery are the cornerstone of all successful SQL Server DBA careers.  Exactly how much attention is paid to backups until something drastic happens?  NOT AS MUCH AS SHOULD BE!

What is a SQL Server Backup?

A backup is the process of putting data into backup devices that the system creates and maintains.  A backup device can be a disk, file, a tape, or even the cloud.  Easy enough!

Backups are your Keys to Success

Backups are your Keys to Success

There are four different methods of backup in SQL Server:

  • Full Backup – A Full Backup is a copy of the entire database as it exists at the time the backup was completed. It is all-encompassing; every detail of your database (data, stored procedures, tables, functions, views, indexes, and so forth) will be backed up. This is why the Full Backup is the foundation of every restore sequence.  These Full backups can be quite large and put a strain on your storage capacity.  Despite the name, a full backup will only backup the data files; the transaction log must be backed up separately.  To ensure you can recover to the point of failure quickly, you will want to also utilize Differential and Transaction Log Backups (we will cover these next).  Without a Full Backup, your Differential and Transaction Log Backups are useless as you cannot restore a database without a Full Backup!  
  • Differential Backup – Differential Backups capture only changed data from the last Full Backup. Simple terms: this will backup only the changes since the last Full backup, not backup the entire database. In the event of an outage, the Differential Backups can greatly reduce the time to recover.  Using both Differential Backups and Full Backups will dramatically reduce required storage space, as the Full Backups can be very large and the Differentials are remarkably smaller.  
    • How does a Differential Backup know what data has changed?  When data is changed on a page, it sets a flag.  This flag indicates which extent (collection of 8 database pages) the Differential Backup will need to backup.  If your Differential Backups are set to run daily, each day’s data changes are recorded and the flag will not be cleared until a Full Backup has been executed.  Say on Monday you have 3 flags set, and Thursday you have 4 more set, your differential backup on Thursday night will contain data changes represented by all 7 flags, so the flags from the Monday Differential are not cleared by the subsequent Differential Backups.  The flags would only be cleared by a Full Backup.
  • Transaction Log Backup – A Transaction Log is a physical file that stores a record of all the transactions that have occurred on a specific database; much like a recorder, maintaining a record of all the modifications.  The Transaction Log Backup will capture the information and then releases the space in the log file so it is able to be used again.  In doing this, the Transaction Log Backup truncates the information in the Transaction Log, but the data is not deleted, it is stored in the backup!  Without Transaction Log Backups, the Transaction Logs will run and grow nonstop, chewing through valuable storage space.   
    • Not only does backing up and truncating the Transaction Log manage the filesize, it also decreases the potential for catastrophic data loss.  Having all the Transaction Log Backups since your last Full Backup will allow you to perform point in time restores.  Transaction Log Backups are ideally set up to execute every few minutes to every hour, depending on your company’s threshold for data loss.
  • Copy Only Backup – A Copy Only Backup doesn’t change the Differential.  It can be a full backup in that it is a record of the database as it exists when the backup is taken; however, it does not reset flags.  It is ideal for doing a full backup in the middle of the week without disrupting any other backups.  A Copy Only Backup can be used in a Dev space for troubleshooting, or preparing to move to a new environment.

Here is an example of a solid weekly backup plan which uses Full, Differential, and Transaction Log Backups:

Sample Backup Plan

Sample Backup Plan

Okay, great…backed up, now what?

Okay, so now you know what SQL Server backups are, a description of each backup type, an idea of how and what they back up, and have an idea of a good plan of action to create a solid backup plan.  So, why are backups so important?  Do you know how easy it is to accidentally update or delete data?   It is just one T-SQL statement away with the wrong filter, or no filter at all.  Having good up to date backups and being able to restore them is the difference between looking like a hero and being forced to find a new job.  Backups are your foundation for Recovery.

Please come back, this is the 2nd in a series of blog posts regarding SQL Server Backups and Recovery.  See you next time when we begin to discuss SQL Server Recovery Models.  Thank you for reading!