(This is an update to Backup Restore Chain revisited I posted February 2016) .
In the morning hours our disk system lost the log files for all my user databases due to a firmware bug.... The connection to the disks could not be re-established and new drives was set up. With all the databases being in Full Recovery mode, as well as having JUST completed transaction log backups a minute prior to the loss of disks, it was established that we could restore what we had with minimal loss.
However, there was many databases affected, and each would have about 45 files to restore. I quickly pulled the query out from the previous article and the numbers from it matched what I had found just taking a quick look at the backup tables. My previous script would just give me a list of files, in the correct order to be restored. For a total of 450 files, this seemed like too much to manually copy and paste without messing up somewhere. With my boss we decided it was better to spend my time rewriting my query to also give the restore statements.
All comments from previous blog posts on this subject still applies. The script assumes a couple of things, mainly that restores are happening to the same location as they where backed up from, i.e. no WITH MOVE commands included. This would be fairly easy to add if you need it, and I might go ahead and made another version later that does this as time permits.
The output from the procedure when called with a 2 for the output parameter looks like this:
Code is below, can also be found in my Github
set quoted_identifier off;
go
use master
go
create procedure sp_RestoreChainByDB2
@DBName sysname,
@Output int = 1, -- default to previous versions output
@MaxFullBackupDate date = NULL
as
begin
/****************
Created by @SQLSoldier
http://www.sqlsoldier.com/wp/sqlserver/day3of31daysofdisasterrecoverydeterminingfilestorestoredatabase
2015-09-24
Modified by Anders Pedersen
Twitter: @arrowdrive
Blog: Blog.AndersOnSQL.com
Made to work with SQL 2008R2.
2016-02-26
Modified by Anders Pedersen
Fix issue where First_LSN and Last_LSN values are the same on a record for the trasaction log backups.
These transaction log backups can be ignored in the restore chain.
Also made into a procedure, with the database name for a required parameter, and max date for the full backup to start with as a parameter
2016-11-28
Modified by Anders Pedersen
Added support for scripting out restore statements.
Added parameter @output for what the script should print out: 1 = Restore chain only, 2 = Restore Script only, 3 = both
Note that the restore scripts assume the files are going back to the original location.
This part could easily be modified by adding parameters for data and log file location
Or even made table driven if a lot of files going to different locations.
This was made for my own need to quickly create a restore chain to recover a server where the databases existed, but had to be over written.
*****************/
Declare @DBBackupLSN numeric(25, 0)
Declare @Baks Table (
BakID int identity(1, 1) not null primary key,
backup_set_id int not null,
media_set_id int not null,
first_family_number tinyint not null,
last_family_number tinyint not null,
first_lsn numeric(25, 0) null,
last_lsn numeric(25, 0) null,
database_backup_lsn numeric(25, 0) null,
backup_finish_date datetime null,
type char(1) null,
family_sequence_number tinyint not null,
physical_device_name nvarchar(260) not null,
device_type tinyint null,
checkpoint_LSN numeric (25,0)-- Anders 2015-09-24
)
Set NoCount On;
-- Get the most recent full backup with all backup files
Insert Into @Baks (backup_set_id,
media_set_id,
first_family_number,
last_family_number,
first_lsn,
last_lsn,
database_backup_lsn,
backup_finish_date,
type,
family_sequence_number,
physical_device_name,
device_type
,checkpoint_LSN)-- Anders
Select Top(1) With Ties B.backup_set_id,
B.media_set_id,
B.first_family_number,
B.last_family_number,
B.first_lsn,
B.last_lsn,
B.database_backup_lsn,
B.backup_finish_date,
B.type,
BF.family_sequence_number,
BF.physical_device_name,
BF.device_type
, B.checkpoint_lsn -- Anders 2015-09-24
From msdb.dbo.backupset As B
Inner Join msdb.dbo.backupmediafamily As BF
On BF.media_set_id = B.media_set_id
And BF.family_sequence_number Between B.first_family_number And B.last_family_number
Where B.database_name = @DBName
And B.is_copy_only = 0
And B.type = 'D'
And BF.physical_device_name Not In ('Nul', 'Nul:')
and convert(DATE,B.backup_start_date) <=(CASE
when @MaxFullBackupDate is not null then @MaxFullBackupDate
else convert(DATE,B.backup_start_date) end) -- Anders 2016-02-26
Order By backup_finish_date desc, backup_set_id;
-- Get the lsn that the differential backups, if any, will be based on
Select @DBBackupLSN = checkpoint_LSN -- Anders 2015-09-24
From @Baks;
-- Get the most recent differential backup based on that full backup
Insert Into @Baks (backup_set_id,
media_set_id,
first_family_number,
last_family_number,
first_lsn,
last_lsn,
database_backup_lsn,
backup_finish_date,
type,
family_sequence_number,
physical_device_name,
device_type)
Select Top(1) With Ties B.backup_set_id,
B.media_set_id,
B.first_family_number,
B.last_family_number,
B.first_lsn,
B.last_lsn,
B.database_backup_lsn,
B.backup_finish_date,
B.type,
BF.family_sequence_number,
BF.physical_device_name,
BF.device_type
From msdb.dbo.backupset As B
Inner Join msdb.dbo.backupmediafamily As BF
On BF.media_set_id = B.media_set_id
And BF.family_sequence_number Between B.first_family_number And B.last_family_number
Where B.database_name = @DBName
And B.is_copy_only = 0
And B.type = 'I'
And BF.physical_device_name Not In ('Nul', 'Nul:')
And B.database_backup_lsn = @DBBackupLSN
Order By backup_finish_date Desc, backup_set_id;
--select * from @Baks
-- Get the last LSN included in the differential backup,
-- if one was found, or of the full backup
Select Top 1 @DBBackupLSN = last_lsn
From @Baks
Where type In ('D', 'I')
Order By BakID Desc;
-- Get first log backup, if any, for restore, where
-- last_lsn of previous backup is >= first_lsn of the
-- log backup and <= the last_lsn of the log backup
Insert Into @Baks (backup_set_id,
media_set_id,
first_family_number,
last_family_number,
first_lsn,
last_lsn,
database_backup_lsn,
backup_finish_date,
type,
family_sequence_number,
physical_device_name,
device_type)
Select Top(1) With Ties B.backup_set_id,
B.media_set_id,
B.first_family_number,
B.last_family_number,
B.first_lsn,
B.last_lsn,
B.database_backup_lsn,
B.backup_finish_date,
B.type,
BF.family_sequence_number,
BF.physical_device_name,
BF.device_type
From msdb.dbo.backupset B
Inner Join msdb.dbo.backupmediafamily As BF
On BF.media_set_id = B.media_set_id
And BF.family_sequence_number Between B.first_family_number And B.last_family_number
Where B.database_name = @DBName
And B.is_copy_only = 0
And B.type = 'L'
And BF.physical_device_name Not In ('Nul', 'Nul:')
And @DBBackupLSN Between B.first_lsn And B.last_lsn
Order By backup_finish_date, backup_set_id;
-- Get last_lsn of the first log backup that will be restored
Set @DBBackupLSN = Null;
Select @DBBackupLSN = Max(last_lsn)
From @Baks
Where type = 'L';
--select * from @Baks;
-- Recursively get all log backups, in order, to be restored
-- first_lsn of the log backup = last_lsn of the previous log backup
With Logs
As (Select B.backup_set_id,
B.media_set_id,
B.first_family_number,
B.last_family_number,
B.first_lsn,
B.last_lsn,
B.database_backup_lsn,
B.backup_finish_date,
B.type,
BF.family_sequence_number,
BF.physical_device_name,
BF.device_type,
1 As LogLevel
From msdb.dbo.backupset B
Inner Join msdb.dbo.backupmediafamily As BF
On BF.media_set_id = B.media_set_id
And BF.family_sequence_number Between B.first_family_number And B.last_family_number
Where B.database_name = @DBName
And B.is_copy_only = 0
And B.type = 'L'
And BF.physical_device_name Not In ('Nul', 'Nul:')
And B.first_lsn = @DBBackupLSN
and B.First_lsn <> B.Last_Lsn -- Anders 2016-02-26
Union All
Select B.backup_set_id,
B.media_set_id,
B.first_family_number,
B.last_family_number,
B.first_lsn,
B.last_lsn,
B.database_backup_lsn,
B.backup_finish_date,
B.type,
BF.family_sequence_number,
BF.physical_device_name,
BF.device_type,
L.LogLevel + 1
From msdb.dbo.backupset B
Inner Join msdb.dbo.backupmediafamily As BF
On BF.media_set_id = B.media_set_id
And BF.family_sequence_number Between B.first_family_number And B.last_family_number
Inner Join Logs L On L.database_backup_lsn = B.database_backup_lsn
Where B.database_name = @DBName
And B.is_copy_only = 0
And B.type = 'L'
And BF.physical_device_name Not In ('Nul', 'Nul:')
And B.first_lsn = L.last_lsn
and B.First_lsn <> B.Last_Lsn) -- Anders 2016-02-26
Insert Into @Baks (backup_set_id,
media_set_id,
first_family_number,
last_family_number,
first_lsn,
last_lsn,
database_backup_lsn,
backup_finish_date,
type,
family_sequence_number,
physical_device_name,
device_type)
Select backup_set_id,
media_set_id,
first_family_number,
last_family_number,
first_lsn,
last_lsn,
database_backup_lsn,
backup_finish_date,
type,
family_sequence_number,
physical_device_name,
device_type
From Logs
Option(MaxRecursion 0);
-- Select out just the columns needed to script restore
IF OBJECT_ID('tempdb..#Baks') IS NOT NULL
DROP TABLE #Baks
Select RestoreOrder = Row_Number() Over(Partition By family_sequence_number Order By BakID),
RestoreType = Case When type In ('D', 'I') Then 'Database'
When type = 'L' Then 'Log'
End,
DeviceType = Case When device_type in (2, 102) Then 'Disk'
When device_type in (5, 105) Then 'Tape'
End,
PhysicalFileName = replace (physical_device_name, '\\agdata.int\backups\ebusiness\eBiz\MONSANTO\MONSANTO', '\\BU\AndersTest\AndersTest')
into #Baks -- to stop from having to read it twice, this could be done neater
From @Baks
Order By BakID;
Set NoCount on;
set quoted_identifier off;
if @Output in (1,3)
select * from #Baks
if @Output in (2,3)
begin
/****
Anders 2016-11/28
This could be done going into a temp table to create one output, but that would create a column name
Done this way, the script can simply be copied and pasted to a new query window to be executed
*****/
select "restore database " + @DBName +" from DISK = '" + PhysicalFileName + "' with NORECOVERY" + case when RIGHT(PhysicalFileName, 3) = 'bak' then " , REPLACE" else "" end
from #Baks
where RestoreType = 'Database'
order by RestoreOrder
select "restore log " + @DBName +" from DISK = '" + PhysicalFileName + "' with NORECOVERY"
from #Baks
where RestoreType = 'Log'
order by RestoreOrder
-- recover the database
select "RESTORE DATABASE " +@DBName + " WITH RECOVERY"
end
Set NoCount Off;
END -- proc
Monday, November 28, 2016
Tuesday, October 11, 2016
T-SQL Tuesday #83. We're still dealing with the same problems.
This months T-SQL Tuesday is hosted by Andy Mallon at T-SQL Tuesday #83 , the subject is "We're still dealing with the same problems."
In the 20+ years I have been working with MS SQL Server, some issues continue to pop up, miss configured NIC settings, badly configured servers in general, bad disk configurations, horrible use of tempdb, table variables, SQL Variant... just to name a few.
However, in most of the jobs I have had, I come in as the DBA for a system already in use, sometimes for many years. And many times without there ever having been a person in charge of the database(s). I end up dealing with pre-existing issues in the architecture of the databases.
The one thing I see over and over, is a complete lack of foresight in design. Or as I like to call it "Spec Fixation," which is where things are developed exactly to specifications, without any thoughts to how the system will be used, or what could be needed in the future.
Two examples are in order I believe. One worked out, the other, well, still a work in progress.
Example 1:
Working to maintain listener information, such as age groups, for a large nation wide radio company, the developer I was helping was developing it with the 3 buckets hard coded for age groups. I rejected the design and coached him how to make the buckets table driven. He kept insisting that is not what the design called for, to which I replied I really did not care what the spec said as long as we met the minimum. Since I refused to move anything to production that was not table driven, he finally accepted my solution and implemented it. On the day of the deployment, everything worked the way the business wanted it. However, by 4 PM, the business manager came to him and basically said "uhm, you know, we really need more fidelity to this data, can you make it 5 buckets instead of 3?" In 15 minutes of work we had it with the 5 buckets. Estimated delivery time if we had gone with his original design would have added 2 columns to the table, changes to the front end etc. Win!
Example 2:
ETL. Spec said only Address Line 1 is needed to be loaded, so the developers only bring that line in (plus name, city etc.). Fast forward 8 years, I get a request on my desk: "Please add Address Line 2 to import, and all tables. Oh, and we need historical data for previously loaded files. And for all address types".
Groan.
No normalization in this database (which is just one of about 40 databases with SIMILAR structure, but not identical).
2 weeks of research, here is what I found that needed changed:
60 tables
10 views
260+ procedures that reference the above tables and views.
1 DTS(!!!!!!!!!!!!!!!) package
1 SSIS package.
And that was just to get data INTO SQL. Also needed to rewrite to get the data back out to the client after we did our job with it. Oh, and no way to get the historical data loaded.
This could ALL have been prevented if the original developers had just loaded all address information available in the file from day 1 (all lines have been in the file since the very first test file we got, which I managed to dig up). Instead it was a major undertaking.
So, please, take a moment to think how the system will be used, and how it could evolve. It can save major headaches in the future.
First time presenting at a SQL Saturday (#560 Charlotte)
Back in February I ended up doing a presentation called IO Tuning 101 for the local PASS chapter, so when the local BI PASS chapter announced they where looking for speakers for a SQL Saturday in September I cleared my schedule and decided to send in my submission.
I had tried to get the speech in for Summit, but was turned down. Part of it was lack of experience, both in submitting and speaking. Admittedly it was an extreme long shot for Summit, but you never know if you do not try.
I was however happy to have been selected to speak at SQL Saturday #560 in Charlotte on September 17, 2016. This approximately 1 hour introduction to query tuning using SET STATISTICS is an extreme entry level speech, no slides, just demos and me explaining what we are seeing on the screen. It shows how some very simple tuning of disk I/O can drastically improve query performance, and is something just about anyone can try out. This is a reactionary tactic, not something that necessarily works in the design phase, but in 20+ years of doing this, one of the most common requests I get goes along the lines of "this ran fine yesterday, why is it slow today?"
Was I nervous? Yupp. Did it go well? I thought so, the feedback was mostly positive, some thought it was too basic, but that is the entire reason for this presentation. Having seen so many good speakers coming to our user group to speak, most of it is too high end for a lot of people and it scared them off. I'm going to be working on a series of introductory topics, and intermediary topics, too try to help more people get up to speed. Now, too find the time....
And yes, I do plan on applying to speak at more SQL Saturdays. It helps me learn, and hopefully it helps other people in our community.
I had tried to get the speech in for Summit, but was turned down. Part of it was lack of experience, both in submitting and speaking. Admittedly it was an extreme long shot for Summit, but you never know if you do not try.
I was however happy to have been selected to speak at SQL Saturday #560 in Charlotte on September 17, 2016. This approximately 1 hour introduction to query tuning using SET STATISTICS is an extreme entry level speech, no slides, just demos and me explaining what we are seeing on the screen. It shows how some very simple tuning of disk I/O can drastically improve query performance, and is something just about anyone can try out. This is a reactionary tactic, not something that necessarily works in the design phase, but in 20+ years of doing this, one of the most common requests I get goes along the lines of "this ran fine yesterday, why is it slow today?"
Was I nervous? Yupp. Did it go well? I thought so, the feedback was mostly positive, some thought it was too basic, but that is the entire reason for this presentation. Having seen so many good speakers coming to our user group to speak, most of it is too high end for a lot of people and it scared them off. I'm going to be working on a series of introductory topics, and intermediary topics, too try to help more people get up to speed. Now, too find the time....
And yes, I do plan on applying to speak at more SQL Saturdays. It helps me learn, and hopefully it helps other people in our community.
Tuesday, March 8, 2016
T-SQL Tuesday #76, text searching the hard way
T-SQL Tuesday #76 invite
Text searches.
Possibly one of the least fun things to do in SQL.
Back in the days before Full-Text Search in SQL, I was tasked with replacing the search engine at ABC Radio News, this search engine helped them research any broadcast they had ever done. The Fox Pro application they had in place was so slow, and unsupported, they needed something better.
Some of the requirements was either exact match, wild card searches, anywhere searches (i.e. the words typed into the search bar, should find any records that had the words anywhere in them). Because they had gotten so used to the search being slow, they also had an ability to narrow down the search results with further key words.
Starting work on this, I first convinced them not to implement the search of the search function, and let me implement this the way I want, convinced I could make this fast enough without the use of such trickery.
Looking into what was needed, I quickly realized there was a LOT of data, guess 50+ years of news broadcasts will do this. Consider this was in the early 2000s, some innovation was needed from anything I had coded before. Obviously LIKE searches was out of the question, full text search was not available. So what to do?
Basically I decided to break down each broadcast to words into a separate table, the entire application fit in 2 tables: Story and Words.
The Story table had everything in it, broadcast date, tape location, tape number, tape type, and the text of the broadcast itself. The Words table had 3 columns: StoryID, Word and WordCount.
The parsing of the Story table took a while, with old school TEXT data type, no easy way to do this. I would then insert the results into the Words table, with the count showing how many times that word appeared in that one story (filtered out articles like the, a and an).
On the search itself, if "Anywhere search" was selected, it simply found any article that had all those words in the Words table, then returned the records from the Story table. If "exact match" was selected it would use the Words table to find candidate records, then do a like search on the text column in the Story table that had the entire story. Pretty simple. And it was quick! We could search all of their history in sub seconds.
When I first demonstrated this to a few users, it was immediately accepted and one of our front end developers made a quick application to sit as a front end for it. The search capability was now so good some of them where upset since in the past they had used the time while waiting to go get coffee etc. Win in my book.
Monday, February 29, 2016
Backup Restore Chain revisited.
(This is an update to Backup Restore Chain article I posted back in September.)
Again, much thanks to SQLSoldier for the initial code.
Recently I got promoted to a new team that has been without a BDA for a while, and as such started poking around, first of all making sure backups are running, and that we have a reasonable expectation of being able to recover in case of a failure or problem with our databases.
I started some of these by grabbing some random database to test the restore chain on, running the script from the previous article. On one of the databases this query just went into a tail spin, never returning, on a hunch I changed the MaxRecursion option from 0 to 100, knowing that it should never hit more than about 30 at the time due to the data. Just selecting out of the Logs CTE, it became very apparent that I got into a cycle where it kept repeating over and over until it hit the max recursion allowed.
Looking at the backupset table, I saw this (condensed down for clarity and size since this table is huge):
Backup_set_id 1713355 and 1713378 has First_Lsn = Last_Lsn, and they are the same between the two records. There was more records where this was true, just happens to be early in the result set so was easy to spot.
Question then arose in my mind if this caused a problem with the CTE, which it is obvious that it did, but to fix it, I would have to remove those records from being considered in the restore chain (or switch to a loop from a recursive CTE).
I have to admit, I had always been of the impression that ALL backups in a chain had to be restored, but then started to think about the error message when you try to skip one, it says to the effect "LSN to large, try an earlier backup." This makes sense, but would the reverse be true, that if the LSN did not change, you would not need to restore it?
Testing this I restored backup_set_ids 1713302, 1713332 and 1713401 (i.e. the full backup, and the 5:15 am and 6:00 am transaction log backups, skipping the 5:30 and 5:45 backups). After the last transaction log backup was restored, I recovered the database and it came up just fine.
This tested, it was time to figure out exactly how I could make the Backup Restore Chain script work, after a few, uhm, misfires, it turned out to be very simple, simply ignore the records where the two LSNs where identical, added this to both queries inside the Logs CTE:
AND B.First_lsn <> B.Last_Lsn
This simple change did the trick, now when I run the query, I got this as my result:
I have tested the following script in as many scenarios as I could think of, but if you find something wrong, please let me know, either in comments here, or to my twitter account.
In rewriting this, I also decided to make it into a stored procedure that could take the database name as a required parameter, and a date as an optional. With the date sent in, it will use the newest FULL backup prior on or before that date, this could very easily be changed if you need to do it by date/time if you run multiple full backups per day. I decided to add this in case I ever need to restore prior to the latest full backup.
Code for procedure below, you can also find it saved on Github
USE MASTERGO
CREATE PROCEDURE sp_RestoreChainByDB
@DBName SYSNAME,
@MaxFullBackupDate date = NULL
AS
BEGIN/****************
Created by @SQLSoldier
http://www.sqlsoldier.com/wp/sqlserver/day3of31daysofdisasterrecoverydeterminingfilestorestoredatabase
2015-09-24
Modified by Anders Pedersen
Twitter: @arrowdrive
Blog: Blog.AndersOnSQL.com
Made to work with SQL 2008R2.
2016-02-26
Modified by Anders Pedersen
Fix issue where First_LSN and Last_LSN values are the same on a record for the trasaction log backups.
These transaction log backups can be ignored in the restore chain.
Also made into a procedure, with the database name for a required parameter, and max date for the full backup to start with as a parameter
*****************/
DECLARE @DBBackupLSN numeric(25, 0)
DECLARE @Baks TABLE (
BakID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
backup_set_id INT NOT NULL,
media_set_id INT NOT NULL,
first_family_number tinyint NOT NULL,
last_family_number tinyint NOT NULL,
first_lsn numeric(25, 0) NULL,
last_lsn numeric(25, 0) NULL,
database_backup_lsn numeric(25, 0) NULL,
backup_finish_date DATETIME NULL,
TYPE CHAR(1) NULL,
family_sequence_number tinyint NOT NULL,
physical_device_name NVARCHAR(260) NOT NULL,
device_type tinyint NULL,
checkpoint_LSN numeric (25,0)-- Anders 2015-09-24
)
SET NOCOUNT ON;
-- Get the most recent full backup with all backup files
INSERT INTO @Baks (backup_set_id,
media_set_id,
first_family_number,
last_family_number,
first_lsn,
last_lsn,
database_backup_lsn,
backup_finish_date,
TYPE,
family_sequence_number,
physical_device_name,
device_type
,checkpoint_LSN)-- Anders
SELECT TOP(1) WITH Ties B.backup_set_id,
B.media_set_id,
B.first_family_number,
B.last_family_number,
B.first_lsn,
B.last_lsn,
B.database_backup_lsn,
B.backup_finish_date,
B.TYPE,
BF.family_sequence_number,
BF.physical_device_name,
BF.device_type
, B.checkpoint_lsn -- Anders 2015-09-24
FROM msdb.dbo.backupset AS B
INNER JOIN msdb.dbo.backupmediafamily AS BF
ON BF.media_set_id = B.media_set_id
AND BF.family_sequence_number BETWEEN B.first_family_number AND B.last_family_number
WHERE B.database_name = @DBName
AND B.is_copy_only = 0
AND B.TYPE = 'D'
AND BF.physical_device_name NOT IN ('Nul', 'Nul:')
AND CONVERT(DATE,B.backup_start_date) <=(CASE
WHEN @MaxFullBackupDate IS NOT NULL THEN @MaxFullBackupDate
ELSE CONVERT(DATE,B.backup_start_date) END) -- Anders 2016-02-26
ORDER BY backup_finish_date DESC, backup_set_id;
-- Get the lsn that the differential backups, if any, will be based on
SELECT @DBBackupLSN = checkpoint_LSN -- Anders 2015-09-24
FROM @Baks;
-- Get the most recent differential backup based on that full backup
INSERT INTO @Baks (backup_set_id,
media_set_id,
first_family_number,
last_family_number,
first_lsn,
last_lsn,
database_backup_lsn,
backup_finish_date,
TYPE,
family_sequence_number,
physical_device_name,
device_type)
SELECT TOP(1) WITH Ties B.backup_set_id,
B.media_set_id,
B.first_family_number,
B.last_family_number,
B.first_lsn,
B.last_lsn,
B.database_backup_lsn,
B.backup_finish_date,
B.TYPE,
BF.family_sequence_number,
BF.physical_device_name,
BF.device_type
FROM msdb.dbo.backupset AS B
INNER JOIN msdb.dbo.backupmediafamily AS BF
ON BF.media_set_id = B.media_set_id
AND BF.family_sequence_number BETWEEN B.first_family_number AND B.last_family_number
WHERE B.database_name = @DBName
AND B.is_copy_only = 0
AND B.TYPE = 'I'
AND BF.physical_device_name NOT IN ('Nul', 'Nul:')
AND B.database_backup_lsn = @DBBackupLSN
ORDER BY backup_finish_date DESC, backup_set_id;
--select * from @Baks
-- Get the last LSN included in the differential backup,
-- if one was found, or of the full backup
SELECT TOP 1 @DBBackkupLSN = last_lsn
FROM @Baks
WHERE TYPE IN ('D', 'I')
ORDER BY BakID DESC;
-- Get first log backup, if any, for restore, where
-- last_lsn of previous backup is >= first_lsn of the
-- log backup and <= the last_lsn of the log backup
INSERT INTO @Baks (backup_set_id,
media_set_id,
first_family_number,
last_family_number,
first_lsn,
last_lsn,
database_backup_lsn,
backup_finish_date,
TYPE,
family_sequence_number,
physical_device_name,
device_type)
SELECT TOP(1) WITH Ties B.backup_set_id,
B.media_set_id,
B.first_family_number,
B.last_family_number,
B.first_lsn,
B.last_lsn,
B.database_backup_lsn,
B.backup_finish_date,
B.TYPE,
BF.family_sequence_number,
BF.physical_device_name,
BF.device_type
FROM msdb.dbo.backupset B
INNER JOIN msdb.dbo.backupmediafamily AS BF
ON BF.media_set_id = B.media_set_id
AND BF.family_sequence_number BETWEEN B.first_family_number AND B.last_family_number
WHERE B.database_name = @DBName
AND B.is_copy_only = 0
AND B.TYPE = 'L'
AND BF.physical_device_name NOT IN ('Nul', 'Nul:')
AND @DBBackupLSN BETWEEN B.first_lsn AND B.last_lsn
ORDER BY backup_finish_date, backup_set_id;
-- Get last_lsn of the first log backup that will be restored
SET @DBBackupLSN = NULL;
SELECT @DBBackupLSN = MAX(last_lsn)
FROM @Baks
WHERE TYPE = 'L';
--select * from @Baks;
-- Recursively get all log backups, in order, to be restored
-- first_lsn of the log backup = last_lsn of the previous log backup
WITH Logs
AS (SELECT B.backup_set_id,
B.media_set_id,
B.first_family_number,
B.last_family_number,
B.first_lsn,
B.last_lsn,
B.database_backup_lsn,
B.backup_finish_date,
B.TYPE,
BF.family_sequence_number,
BF.physical_device_name,
BF.device_type,
1 AS LogLevel
FROM msdb.dbo.backupset B
INNER JOIN msdb.dbo.backupmediafamily AS BF
ON BF.media_set_id = B.media_set_id
AND BF.family_sequence_number BETWEEN B.first_family_number AND B.last_family_number
WHERE B.database_name = @DBName
AND B.is_copy_only = 0
AND B.TYPE = 'L'
AND BF.physical_device_name NOT IN ('Nul', 'Nul:')
AND B.first_lsn = @DBBackupLSN
AND B.First_lsn <> B.Last_Lsn -- Anders 2016-02-26
UNION ALL
SELECT B.backup_set_id,
B.media_set_id,
B.first_family_number,
B.last_family_number,
B.first_lsn,
B.last_lsn,
B.database_backup_lsn,
B.backup_finish_date,
B.TYPE,
BF.family_sequence_number,
BF.physical_device_name,
BF.device_type,
L.LogLevel + 1
FROM msdb.dbo.backupset B
INNER JOIN msdb.dbo.backupmediafamily AS BF
ON BF.media_set_id = B.media_set_id
AND BF.family_sequence_number BETWEEN B.first_family_number AND B.last_family_number
INNER JOIN Logs L ON L.database_backup_lsn = B.database_backup_lsn
WHERE B.database_name = @DBName
AND B.is_copy_only = 0
AND B.TYPE = 'L'
AND BF.physical_device_name NOT IN ('Nul', 'Nul:')
AND B.first_lsn = L.last_lsn
AND B.First_lsn <> B.Last_Lsn) -- Anders 2016-02-26
INSERT INTO @Baks (backup_set_id,
media_set_id,
first_family_number,
last_family_number,
first_lsn,
last_lsn,
database_backup_lsn,
backup_finish_date,
TYPE,
family_sequence_number,
physical_device_name,
device_type)
SELECT backup_set_id,
media_set_id,
first_family_number,
last_family_number,
first_lsn,
last_lsn,
database_backup_lsn,
backup_finish_date,
TYPE,
family_sequence_number,
physical_device_name,
device_type
FROM Logs
OPTION(MaxRecursion 0);
-- Select out just the columns needed to script restore
SELECT RestoreOrder = Row_Number() OVER(Partition BY family_sequence_number ORDER BY BakID),
RestoreType = CASE WHEN TYPE IN ('D', 'I') THEN 'Database'
WHEN TYPE = 'L' THEN 'Log'
END,
DeviceType = CASE WHEN device_type IN (2, 102) THEN 'Disk'
WHEN device_type IN (5, 105) THEN 'Tape'
END,
PhysicalFileName = physical_device_name
FROM @Baks
ORDER BY BakID;
SET NOCOUNT OFF;
END -- proc
Again, much thanks to SQLSoldier for the initial code.
Recently I got promoted to a new team that has been without a BDA for a while, and as such started poking around, first of all making sure backups are running, and that we have a reasonable expectation of being able to recover in case of a failure or problem with our databases.
I started some of these by grabbing some random database to test the restore chain on, running the script from the previous article. On one of the databases this query just went into a tail spin, never returning, on a hunch I changed the MaxRecursion option from 0 to 100, knowing that it should never hit more than about 30 at the time due to the data. Just selecting out of the Logs CTE, it became very apparent that I got into a cycle where it kept repeating over and over until it hit the max recursion allowed.
Looking at the backupset table, I saw this (condensed down for clarity and size since this table is huge):
SELECT backup_set_id, backup_Start_date, B.First_Lsn, B.Last_Lsn, B.TYPE
FROM msdb.dbo.backupset B
WHERE database_name = 'AndersTest'
Backup_set_id 1713355 and 1713378 has First_Lsn = Last_Lsn, and they are the same between the two records. There was more records where this was true, just happens to be early in the result set so was easy to spot.
Question then arose in my mind if this caused a problem with the CTE, which it is obvious that it did, but to fix it, I would have to remove those records from being considered in the restore chain (or switch to a loop from a recursive CTE).
I have to admit, I had always been of the impression that ALL backups in a chain had to be restored, but then started to think about the error message when you try to skip one, it says to the effect "LSN to large, try an earlier backup." This makes sense, but would the reverse be true, that if the LSN did not change, you would not need to restore it?
Testing this I restored backup_set_ids 1713302, 1713332 and 1713401 (i.e. the full backup, and the 5:15 am and 6:00 am transaction log backups, skipping the 5:30 and 5:45 backups). After the last transaction log backup was restored, I recovered the database and it came up just fine.
This tested, it was time to figure out exactly how I could make the Backup Restore Chain script work, after a few, uhm, misfires, it turned out to be very simple, simply ignore the records where the two LSNs where identical, added this to both queries inside the Logs CTE:
AND B.First_lsn <> B.Last_Lsn
This simple change did the trick, now when I run the query, I got this as my result:
I have tested the following script in as many scenarios as I could think of, but if you find something wrong, please let me know, either in comments here, or to my twitter account.
In rewriting this, I also decided to make it into a stored procedure that could take the database name as a required parameter, and a date as an optional. With the date sent in, it will use the newest FULL backup prior on or before that date, this could very easily be changed if you need to do it by date/time if you run multiple full backups per day. I decided to add this in case I ever need to restore prior to the latest full backup.
Code for procedure below, you can also find it saved on Github
USE MASTERGO
CREATE PROCEDURE sp_RestoreChainByDB
@DBName SYSNAME,
@MaxFullBackupDate date = NULL
AS
BEGIN/****************
Created by @SQLSoldier
http://www.sqlsoldier.com/wp/sqlserver/day3of31daysofdisasterrecoverydeterminingfilestorestoredatabase
2015-09-24
Modified by Anders Pedersen
Twitter: @arrowdrive
Blog: Blog.AndersOnSQL.com
Made to work with SQL 2008R2.
2016-02-26
Modified by Anders Pedersen
Fix issue where First_LSN and Last_LSN values are the same on a record for the trasaction log backups.
These transaction log backups can be ignored in the restore chain.
Also made into a procedure, with the database name for a required parameter, and max date for the full backup to start with as a parameter
*****************/
DECLARE @DBBackupLSN numeric(25, 0)
DECLARE @Baks TABLE (
BakID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
backup_set_id INT NOT NULL,
media_set_id INT NOT NULL,
first_family_number tinyint NOT NULL,
last_family_number tinyint NOT NULL,
first_lsn numeric(25, 0) NULL,
last_lsn numeric(25, 0) NULL,
database_backup_lsn numeric(25, 0) NULL,
backup_finish_date DATETIME NULL,
TYPE CHAR(1) NULL,
family_sequence_number tinyint NOT NULL,
physical_device_name NVARCHAR(260) NOT NULL,
device_type tinyint NULL,
checkpoint_LSN numeric (25,0)-- Anders 2015-09-24
)
SET NOCOUNT ON;
-- Get the most recent full backup with all backup files
INSERT INTO @Baks (backup_set_id,
media_set_id,
first_family_number,
last_family_number,
first_lsn,
last_lsn,
database_backup_lsn,
backup_finish_date,
TYPE,
family_sequence_number,
physical_device_name,
device_type
,checkpoint_LSN)-- Anders
SELECT TOP(1) WITH Ties B.backup_set_id,
B.media_set_id,
B.first_family_number,
B.last_family_number,
B.first_lsn,
B.last_lsn,
B.database_backup_lsn,
B.backup_finish_date,
B.TYPE,
BF.family_sequence_number,
BF.physical_device_name,
BF.device_type
, B.checkpoint_lsn -- Anders 2015-09-24
FROM msdb.dbo.backupset AS B
INNER JOIN msdb.dbo.backupmediafamily AS BF
ON BF.media_set_id = B.media_set_id
AND BF.family_sequence_number BETWEEN B.first_family_number AND B.last_family_number
WHERE B.database_name = @DBName
AND B.is_copy_only = 0
AND B.TYPE = 'D'
AND BF.physical_device_name NOT IN ('Nul', 'Nul:')
AND CONVERT(DATE,B.backup_start_date) <=(CASE
WHEN @MaxFullBackupDate IS NOT NULL THEN @MaxFullBackupDate
ELSE CONVERT(DATE,B.backup_start_date) END) -- Anders 2016-02-26
ORDER BY backup_finish_date DESC, backup_set_id;
-- Get the lsn that the differential backups, if any, will be based on
SELECT @DBBackupLSN = checkpoint_LSN -- Anders 2015-09-24
FROM @Baks;
-- Get the most recent differential backup based on that full backup
INSERT INTO @Baks (backup_set_id,
media_set_id,
first_family_number,
last_family_number,
first_lsn,
last_lsn,
database_backup_lsn,
backup_finish_date,
TYPE,
family_sequence_number,
physical_device_name,
device_type)
SELECT TOP(1) WITH Ties B.backup_set_id,
B.media_set_id,
B.first_family_number,
B.last_family_number,
B.first_lsn,
B.last_lsn,
B.database_backup_lsn,
B.backup_finish_date,
B.TYPE,
BF.family_sequence_number,
BF.physical_device_name,
BF.device_type
FROM msdb.dbo.backupset AS B
INNER JOIN msdb.dbo.backupmediafamily AS BF
ON BF.media_set_id = B.media_set_id
AND BF.family_sequence_number BETWEEN B.first_family_number AND B.last_family_number
WHERE B.database_name = @DBName
AND B.is_copy_only = 0
AND B.TYPE = 'I'
AND BF.physical_device_name NOT IN ('Nul', 'Nul:')
AND B.database_backup_lsn = @DBBackupLSN
ORDER BY backup_finish_date DESC, backup_set_id;
--select * from @Baks
-- Get the last LSN included in the differential backup,
-- if one was found, or of the full backup
SELECT TOP 1 @DBBackkupLSN = last_lsn
FROM @Baks
WHERE TYPE IN ('D', 'I')
ORDER BY BakID DESC;
-- Get first log backup, if any, for restore, where
-- last_lsn of previous backup is >= first_lsn of the
-- log backup and <= the last_lsn of the log backup
INSERT INTO @Baks (backup_set_id,
media_set_id,
first_family_number,
last_family_number,
first_lsn,
last_lsn,
database_backup_lsn,
backup_finish_date,
TYPE,
family_sequence_number,
physical_device_name,
device_type)
SELECT TOP(1) WITH Ties B.backup_set_id,
B.media_set_id,
B.first_family_number,
B.last_family_number,
B.first_lsn,
B.last_lsn,
B.database_backup_lsn,
B.backup_finish_date,
B.TYPE,
BF.family_sequence_number,
BF.physical_device_name,
BF.device_type
FROM msdb.dbo.backupset B
INNER JOIN msdb.dbo.backupmediafamily AS BF
ON BF.media_set_id = B.media_set_id
AND BF.family_sequence_number BETWEEN B.first_family_number AND B.last_family_number
WHERE B.database_name = @DBName
AND B.is_copy_only = 0
AND B.TYPE = 'L'
AND BF.physical_device_name NOT IN ('Nul', 'Nul:')
AND @DBBackupLSN BETWEEN B.first_lsn AND B.last_lsn
ORDER BY backup_finish_date, backup_set_id;
-- Get last_lsn of the first log backup that will be restored
SET @DBBackupLSN = NULL;
SELECT @DBBackupLSN = MAX(last_lsn)
FROM @Baks
WHERE TYPE = 'L';
--select * from @Baks;
-- Recursively get all log backups, in order, to be restored
-- first_lsn of the log backup = last_lsn of the previous log backup
WITH Logs
AS (SELECT B.backup_set_id,
B.media_set_id,
B.first_family_number,
B.last_family_number,
B.first_lsn,
B.last_lsn,
B.database_backup_lsn,
B.backup_finish_date,
B.TYPE,
BF.family_sequence_number,
BF.physical_device_name,
BF.device_type,
1 AS LogLevel
FROM msdb.dbo.backupset B
INNER JOIN msdb.dbo.backupmediafamily AS BF
ON BF.media_set_id = B.media_set_id
AND BF.family_sequence_number BETWEEN B.first_family_number AND B.last_family_number
WHERE B.database_name = @DBName
AND B.is_copy_only = 0
AND B.TYPE = 'L'
AND BF.physical_device_name NOT IN ('Nul', 'Nul:')
AND B.first_lsn = @DBBackupLSN
AND B.First_lsn <> B.Last_Lsn -- Anders 2016-02-26
UNION ALL
SELECT B.backup_set_id,
B.media_set_id,
B.first_family_number,
B.last_family_number,
B.first_lsn,
B.last_lsn,
B.database_backup_lsn,
B.backup_finish_date,
B.TYPE,
BF.family_sequence_number,
BF.physical_device_name,
BF.device_type,
L.LogLevel + 1
FROM msdb.dbo.backupset B
INNER JOIN msdb.dbo.backupmediafamily AS BF
ON BF.media_set_id = B.media_set_id
AND BF.family_sequence_number BETWEEN B.first_family_number AND B.last_family_number
INNER JOIN Logs L ON L.database_backup_lsn = B.database_backup_lsn
WHERE B.database_name = @DBName
AND B.is_copy_only = 0
AND B.TYPE = 'L'
AND BF.physical_device_name NOT IN ('Nul', 'Nul:')
AND B.first_lsn = L.last_lsn
AND B.First_lsn <> B.Last_Lsn) -- Anders 2016-02-26
INSERT INTO @Baks (backup_set_id,
media_set_id,
first_family_number,
last_family_number,
first_lsn,
last_lsn,
database_backup_lsn,
backup_finish_date,
TYPE,
family_sequence_number,
physical_device_name,
device_type)
SELECT backup_set_id,
media_set_id,
first_family_number,
last_family_number,
first_lsn,
last_lsn,
database_backup_lsn,
backup_finish_date,
TYPE,
family_sequence_number,
physical_device_name,
device_type
FROM Logs
OPTION(MaxRecursion 0);
-- Select out just the columns needed to script restore
SELECT RestoreOrder = Row_Number() OVER(Partition BY family_sequence_number ORDER BY BakID),
RestoreType = CASE WHEN TYPE IN ('D', 'I') THEN 'Database'
WHEN TYPE = 'L' THEN 'Log'
END,
DeviceType = CASE WHEN device_type IN (2, 102) THEN 'Disk'
WHEN device_type IN (5, 105) THEN 'Tape'
END,
PhysicalFileName = physical_device_name
FROM @Baks
ORDER BY BakID;
SET NOCOUNT OFF;
END -- proc
Subscribe to:
Posts (Atom)