Restoreability and SSMS

I have written about this before, how SSMS generates restore commands that will fail. This post is about showing what it might look like using screenshots. If you always use T-SQL directly to do restore, then you won’t be affected by this. But if you expect to be able to perform restore using the restore dialog, then read on.

The problem
The issue is that SSMS base a restore sequence on backups that are impossible to restore from. There are two cases I have found:

Copy only backups
The purpose of the COPY_ONLY option for the backup command is to do an out-of-bands backup. I.e., you do an ad-hoc backup as a one-time thing, restore this on your test server and then delete the backup file. The copy only option is for you to say that you don’t want to affect your scheduled backups with this ad-hoc backup. We expect SSMS to not base the restore sequence on these backups – since it is likely that the backup file has been deleted.

Snapshot backups
If you perform a snapshot of a virtual machine (or “checkpoint” as Hyper-V calls it) then this snapshot might interact with VSS in Windows so you get a consistent state of your SQL Server databases. I.e., when you restore/apply such a snapshot, your databases should be just fine. This is great, as long as it doesn’t mess up the SQL Server backups that we produce. It won’t. But the restore GUI in SSMS can be messed up by such a snapshot.

The timeline dialog
The problem seems to occur in the timeline dialog. In the restore GUI, there’s a nifty timeline dialog where you can use a slider to graphically specify at what point in time you want to restore the database to. The GUI uses the backup history information in msdb and based on that figures out what restore operations to perform. This is actually really nice and friendly. As long as it works. The problem is when you specify an earlier point in time, it sometimes uses the incorrect full backup – a full backup which isn’t possible to restore from.

Examples:

RestoreabilityCopyOnlyOK.PNG

Above, the GUI correctly skips a copy only backup. A full copy only backup was produced after the full backup listed above, but the GUI is smart enough to not base the restore sequence on this copy only backup. This is how it should work.

RestoreabilityTimeline

RestoreabilityCopyOnlyNotOK

Above, the GUI incorrectly base the restore on a copy only backup. After using the timeline dialog to point to an earlier point in time, you can see that the GUI now has changed so it bases the restore on this potentially non-existing copy only backup. Not a nice situation to be in if the person doing the restore hasn’t practiced using the T-SQL RESTORE commands.

RestoreabilitySnapOK

Above, the GUI correctly skips a snapshot backup. A snapshot backup using VSS was produced after the full backup listed above, but the GUI is smart enough to not base the restore sequence on this snapshot backup. This is how it should work.

RestoreabilitySnapNotOK.PNG

RestoreabilitySnapNotOKTSQL

Above, the GUI incorrectly base the restore on a snapshot backup. After using the timeline dialog to point to an earlier point in time, you can see that the GUI now has changed so it bases the restore on the snapshot backup. This is immensely bad since the snapshot doesn’t exist in SQL Server. It is just a placeholder so that SQL Server is aware that a snapshot was performed at that point in time. Look at the RESTORE command it produces!

You might wonder how to tell if something is producing VSS snapshots of your machine? You can see that in the errorlog file. Here are a few lines from the errorlog on a machine where I used Hyper-V to produce a proper VSS snapshot (edited for readability):

2016-03-16 13:30:23.75      I/O is frozen on database Adventureworks2014.
2016-03-16 13:30:23.75      I/O is frozen on database theBackupTestTib.
2016-03-16 13:30:23.75      I/O is frozen on database master.
2016-03-16 13:30:29.33      I/O was resumed on database master.
2016-03-16 13:30:29.33      I/O was resumed on database theBackupTestTib.
2016-03-16 13:30:29.34      I/O was resumed on database Adventureworks2014.

Bottom line
You already know this. Practice doing restores – as realistically as possible and using different scenarios.

Disclaimer: Things might change. The tests I did was using SSMS 2014 for the copy only backups and for SSMS 2016 CTP 3.3 for snapshot backups. I have seen this behaviour since SQL Server 2012, though. I wish that this will be fixed in a future version of SSMS, but considering that my requests has gone unnoticed before, I don’t have much hopes. But if you do see something different, let me know and I can re-test. Just make sure to add as much details as possible.

I used my stored procedure at found here to perform the backups – made the T-SQL used for these tests a bit easier to read:

--Show that restore based on backup history idn't possible
--is the most recent full backup is missing, even if COPY_ONLY was used.

--Create the BackupDbWithTs procedure first:
--http://www.karaszi.com/sqlserver/util_backup_script_like_MP.asp

--We will do full backups, diff backups and log backups.
--In between, we will also do a COPY_ONLY full backup (think of it as an ad-hos backup subsequentially deleted).

---------------------------------------------------------------------------------------------
--Drop and create the test database
--NOTE: This will drop a database named theBackupTestTib is such exists!!!!
---------------------------------------------------------------------------------------------
IF DB_ID('theBackupTestTib') IS NOT NULL
DROP DATABASE theBackupTestTib
GO

CREATE DATABASE theBackupTestTib
GO

--Set to full recovery
ALTER DATABASE theBackupTestTib SET RECOVERY FULL
GO

---------------------------------------------------------------------------------------------
--Create a table so we can make a modification in the database between each backup
---------------------------------------------------------------------------------------------
IF OBJECT_ID('theBackupTestTib.dbo.t') IS NOT NULL DROP TABLE theBackupTestTib.dbo.t
GO

CREATE TABLE theBackupTestTib.dbo.t(c1 INT IDENTITY, c2 CHAR(30) DEFAULT 'Hello backup world')
GO

---------------------------------------------------------------------------------------------
--Declare and set variables.
--Adjust to your liking.
DECLARE
@i INT = 1
,@time_in_between VARCHAR(20) = '00:01:00'    --Time between each backup. 1 sec is fine to test thet restore GUI work.
--Set to higher if you want to use the timeline restore dialog and visually see your backups.
,@iterations INT = 2                      --Number of iterations. Adjust as you wish, but do at least two.
,@db SYSNAME = 'theBackupTestTib'
,@path VARCHAR(200) = 'R:\'                   --Where to put your backup files. Delete backup files after you are finished.

WHILE @i <= @iterations
BEGIN
INSERT INTO theBackupTestTib.dbo.t  DEFAULT VALUES;

EXEC MASTER..BackupDbWithTs @db, @path, 'FULL', 'N', 'N', 'N'; INSERT INTO theBackupTestTib.dbo.t DEFAULT VALUES; WAITFOR DELAY @time_in_between
EXEC MASTER..BackupDbWithTs @db, @path, 'LOG ', 'N', 'N', 'N'; INSERT INTO theBackupTestTib.dbo.t DEFAULT VALUES; WAITFOR DELAY @time_in_between
EXEC MASTER..BackupDbWithTs @db, @path, 'LOG ', 'N', 'N', 'N'; INSERT INTO theBackupTestTib.dbo.t DEFAULT VALUES; WAITFOR DELAY @time_in_between

--Perform the COPY_ONLY or snapshot backup now:
EXEC MASTER..BackupDbWithTs @db, @path, 'FULL', 'N', 'N', 'Y'; INSERT INTO theBackupTestTib.dbo.t DEFAULT VALUES; WAITFOR DELAY @time_in_between --COPY_ONLY

EXEC MASTER..BackupDbWithTs @db, @path, 'DIFF', 'N', 'N', 'N'; INSERT INTO theBackupTestTib.dbo.t DEFAULT VALUES; WAITFOR DELAY @time_in_between
EXEC MASTER..BackupDbWithTs @db, @path, 'LOG ', 'N', 'N', 'N'; INSERT INTO theBackupTestTib.dbo.t DEFAULT VALUES; WAITFOR DELAY @time_in_between
EXEC MASTER..BackupDbWithTs @db, @path, 'LOG ', 'N', 'N', 'N'; WAITFOR DELAY @time_in_between

SET @i = @i + 1
END

Misunderstandings about the COPY_ONLY backup option

The COPY_ONLY option for the backup command never ceases to cause confusion. What does it really do? And in what way does it affect your restore sequence? Or not?

There are two sides to this. Restoreability and how the GUI behaves:

Restoreability

If you specify COPY_ONLY for a full backup, it will not affect the following differential backups. I.e., the following differential backups will be based on  the last full backup which was not performed with COPY_ONLY. Another way of looking at this is that a full backup using COPY_ONLY will not reset the DIFF (also known as DCM) page, which is page 6 and repeated approximately every 4 GB in each database file.

If you specify COPY_ONLY for a log backup, it will not affect the following log backups. I.e., the log backup chain will be without the one(s) produced using COPY_ONLY. Another way of looking at this is that a log backup using COPY_ONLY does not empty (truncate) the log.

That’s it! COPY_ONLY for a full backup does not, in any way, affect the restoreability for log backups. Say you perform

FULL_A
LOG_A
LOG_B
FULL_B
LOG_C

You can restore FULL_A, LOG_A, LOG_B and LOG_C, regardless of whether you used COPY_ONLY for FULL_B or not! I see misunderstanding about this all the time.

The restore GUI

And then we have the restore GUI. I really want to like the GUI, but it… keep disappointing me. For various reasons. To the extent that it frequently becomes useless. I’ve blogged about it before. I know that many of you readers don’t use the GUI when you perform restore. But there are so many “accident” DBAs and less experienced DBA’s which expect to be able to use the restore GUI – and they should! But they can’t. Enough ranting, time for some details.

Prior to SQL Server 2012, the restore GUI didn’t include COPY_ONLY backups. The design reasoning was probably that you did an out-of-band backup and that backup file is no longer there (you deleted it after restoring to the test server, for instance). Sound reasoning, in my opinion.

But, as of 2012, the restore GUI includes COPY_ONLY backups. So, say you do:

FULL_A
LOG_A
LOG_B
FULL_B using COPY_ONLY
LOG_C

Also, say that the backup file for FULL_B was deleted. You use the GUI and specify that you want to the restore up until LOG_C. The restore GUI will now select FULL_B and LOG_C, which is a disaster since FULL_B was deleted (it was produced using COPY_ONLY). And you can’t make the restore GUI to base the restores on FULL_A. If you don’t have the practiced to type your restore commands, your organization (boss?) will not be happy with you now.

So, why did Microsoft do this change in 2012? My guess is it because of availability groups. If you perform a backup on a read-only replica, you need to use COPY_ONLY. Btw, we have the same situation if you did a VSS snapshot of the machine (which I blogged about before).

Bottom line? You know this already. Learn how things work, and practice your restore scenarios. Nothing new here.

(I typed everything above off the top of my head. If there is anything you don’t agree with, then please be very specific. I don’t mind discussions, I appreciate it. But to be fruitful, it need to be concrete. So, be specific, ideally including TSQL commands, and specifics on how you drive the GUI (if relevant). And, remember that things can change over time; service packs and new versions of SSMS – so please include those details as well. 🙂 )

Can you restore from your backups? Are you sure?

 

A few days ago, we were doing restore practice drills with a client. I had tested the stuff before this, so the practice was more for the client’s DBAs to test various restore scenarios, with me being able to point to the right direction (when needed), supplement the run-book and stuff like that. Always fun, I like these drills!

Anyhow, This client does regular SQL Server backups to disk (full, and for some databases also log) at 19:00. They also snap the machines every night at 04:00. We don’t want to have dependencies on the machine snap, but it is nice to have in case a machine it totaled and we now can restore from such a snapshot. The issue is that this machine snapshot is seen as a full backup by SQL Server. We all know that a full backup do not affect the log backup chain, but the restore GUI doesn’t care about that!

So the restore GUI suggest that you restore from 04:00 full backup (which isn’t a restoreable backup as it was a snapshot) and then the subsequent log backups. What we need to do is to restore from earlier 19:00 full backup, and then all log backups – ignoring the 04:00 snapshot backup.

Fortunately, my client by themselves (without my intervention) did the restore using T-SQL commands, knowing what backup exists, and figuring out what to restore. But I also wanted them to test the GUI, just so they know how that look like. Of course, you can do a restore from 19:00 to 03:55, and script that to a query window. Then then from 04:00 to current time (or whatever) and script that too,. And then stitch these together. But just typing (with some copy-paste) the commands are much easier.

My point? Test your restores. Do not expect anything. A production situation is not the right time to try to figure these things and trying to cope with it.

About this snapshot software: The next version is expected to have an option to produce the snapshot as a COPY_ONLY backup. Isn’t that great? Now we expect the restore GUI to skip this COPY_ONLY backup, right? No, that was not that I saw. Having an option to produce the backup as COPY_ONLY finally allow us to implement differential backups, but it (from my tests) won’t help with the restore GUI issues. Btw, here is a related post.

Here’s a query that might be helpful if you want to see what type of backups are produced. (I appreciate feedback from anybody if you can see if a snapshot backup sets 1 in the is_snapshot column – I don’t have environment to test at the moment…)

 

SELECT TOP(100)
database_name
,CASE bs.TYPE
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file '
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END AS backup_type
,bs.is_copy_only
,bs.is_snapshot
,DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS backup_time_sec
,mf.physical_device_name
,bs.database_name
FROM msdb.dbo.backupset AS bs
INNER JOIN msdb.dbo.backupmediafamily AS mf ON bs.media_set_id = mf.media_set_id
ORDER BY backup_finish_date DESC;

 

 

Does your 3:rd party backup software ruin your SQL Server backup plan?

Sounds scary, huh? And it surely can be! Here’s an example of one such case:

A client of mine had implemented a 3:rd party backup solution in their VM Ware environment. This shouldn’t affect the SQL Server backups. Or that was the plan. I won’t mention what backup software this is, since I’m sure that there are other software also doing strange things. And in the end, it is up to you to verify that your backup and restore strategy is solid. So let us just call this software “BACK”.

BACK performs a snapshot backup of the virtual machine. The client had scheduled this so it happened at about 4 am each day.

Our SQL Server backups were implements so that 7 pm we did a full backup and every hour we did a log backup.

Nothing strange here and these should be independent of each other, right? Wrong!

When looking at the backup history, I realized that SQL Server would see the snapshot backup as a full backup. OK, that will possibly affect out plan to go for differential backups, but it shouldn’t affect our transaction log backups. We all know that a database backup doesn’t break or affect the log backup chain.

But what I did find was that BACKUP performed a log backup immediately after the snapshot database backup. And the log backup was taken to the file name “nul”. Yes, the binary wasteland.

The end result of this was that the log backups that we performed were usable between 7 pm and 4 am. After that, the log backups are useless. No option to restore anything for the work performed during the working day. It would have been more honest if BACK would set the recovery model to simple instead. That would at least give us a more fair chance to catch the problem (sooner then we did).

Now, we did find an option in BACK to not truncate the log (or whatever they called the checkbox), but by default it did perform this disastrous log backup to nul.

The next step was to consider implementation of the differential backup plan. But time was running out for my assignment so we only managed a quick check. And from what we found out, it seemed that BACK doesn’t have an option to produce its snapshot backup to be seen as a COPY_ONLY backup. This means that it prohibits us to implements SQL Server differential backup and saving some 500 GB backup storage produced each week (for only one of the servers). Now, let me leave a disclaimer here since I didn’t have time to investigate this much as all, but this is what it looks like at the moment. I will dig deeper into the differential backup strategy next time I’m with this client.

The moral of the story? You already know that. Test your restore strategy. You might just be (unpleasantly) surprised!

Backup and the evil RETAINDAYS option

“So what bad has this option done?”, you probably as yourself. Well, not much, but I find it evil because it confuses people, especially those new to SQL Server. I have many times seen people specifying something like 3, and expect SQL Server to keep the three most recent backups in the backup file and overwrite everything which is older than that. Well, that is not what the option does.

But before we go into details, let’s look at an example backup command which is using this option:

BACKUP DATABASE sqlmaint TO DISK = 'R:\sqlmaint.bak' WITH RETAINDAYS = 3

The RETAINDAYS is also exposed in the backup dialog in SSMS: “Backup set will expire: After x days“.

It is also exposed in Maintenance Plans, the backup task. The option is named “Backup set will expire: After x days“. It is only enabled if you select the “Back up databases across one or more files” option, which is not the default option. This makes sense.
The default option is “Create a backup files for every database“, which means that every time a backup is performed, a new file is created consisting of the database name, date and time. Since we will see that this option is only relevant when we do append, it makes sense in the RETAINDAYS not being enabled for this choice.

So what does this option do? All it does is make SQL Server return an error message of you try to do a backup using the INIT option (which means overwrite) before the date and time has occurred. In other words, it tries to help you in not overwriting a backup file, using the INIT option, before it is time. You can still overwrite earlier by either using the stronger FORMAT option instead of INIT; or by simply deleting the backup file. Btw, the RETAINDAYS parameter has a cousin named EXPIREDATE, which does the same thing but you specify a datetime value instead of number of days.

Backup generations
So, we have seen that RETAINDAYS do not in any way provide any automatic backup generation handling. There is no such functionality built-in in the BACKUP command. This means that when you are looking for functionality such as “keep backup files three days, and remove older than that”, you need to look outside the BACKUP command. I.e., some script or tool of some sort.

I have an example (without the “delete old files” part) here, which I mostly created as a starting point for those who want to roll their own and want to have some example to start with. Many of you are probably using Maintenance plans (the “Create a backup files for every database” option in the backup task, along with Maintenance Cleanup task). Another popular script/tool for this is Ola Hallengren’s Maintenance Solution, which you find at http://ola.hallengren.com/.

Point-in-time restore of database backup?

SQL Server 2005 added the STOPAT option for the RESTORE DATABASE command. This sounds great – we can stop at some point in time during the database backup process was running! Or? No, we can’t. Here follows some tech stuff why not, and then what the option is really meant for:

A database backup includes all used extents and also all log records that were produced while the backup process was running (possibly older as well, to handle open transactions). When you restore such a backup, SQL Server will from backup media copy the data pages to mdf and ndf file(s), log records to ldf file(s) and then do REDO (roll forward the log records) and possibly also UNDO (rollback open transactions – this is skipped if you specify NORECOVERY option). Why does it need the log records? Because you can do modifications in the database while the database backup is running. After backup process was started (and before it finishes) you can both modify pages not yet copied to backup media, but also pages already copied to backup media. So, the data pages in themselves do not present a consistent state of the database. At restore time, SQL Server uses the log records included in the database backup to “sync” the modifications that were performed while the backup process were running. So, what you end up with is what the database looked like at the end time of the backup process. I’m sure this is well documented somewhere in Books Online so I won’t dive further into the tech stuff here.

So, what is the purpose of the STOPAT option for RESTORE DATABASE?

It is to give you an early warning, in case you try to restore a database backup which is too late for a subsequent STOPAT operation for a log restore. Here’s an example:

  1. 13:00 db backup
  2. 13:10 log backup
  3. 13:20 log backup
  4. 13:30 db backup
  5. 13:40 log backup
  6. 13:50 log backup

Say you now want to restore to 13:15. Intuitively, you say that you will restore 1, 2 and for 3 you do STOPAT 13:15. Yes, that is right.

But say you are under stress, and perhaps even used some tool which confuses things for you? So, you end up restore 4, 5 and for 6 you try STOPAT 13:15. Clearly, you can’t stopat 13:15 for backup number 6 (it only covers 13:40 to 13:50). I.e., restore of 6 gives you an error message. So, you have to re-start the restore process (1, 2 and 3), which could take a long time (if the backups are large). If you had specified STOPAT 13:15 when you restore backup 4, SQL Server would have given you an error immediately, so you wouldn’t have wasted valuable time restoring unnecessary backups!

That is the purpose of STOPAT for RESTORE DATABASE.

Are non-modified FILESTREAM files excluded from DIFFERENTIAL backups?

Short answer seems to be “yes”.

I got this from a forum post today, so I thought I’d test it out. Basically, the discussion is whether we can somehow cut down backup sizes for filestream data (assumption is that filestream data isn’t modified very frequently). I’ve seen this a few times now, and often suggestions arises to somehow exclude the filestream data and fo file level backup of those files. But that would more or less leaves us with the problem of the “old” solution: potential inconsistency.

So I thought that perhaps diff backups can be helpful here? I.e., perhaps SQL Server might be smart enough to in a diff backup exclude the filestream files that were already in the most prior database backup? Sure seems so, according to below script. (I’m on the road now, so scrips could be neater…):

USE master
GO
IF DB_ID('fstest'IS NOT NULL DROP DATABASE fstest
GO

CREATE DATABASE fstest ON PRIMARY
NAME fstest_data,
FILENAME N'C:\DemoDatabases\DbFiles\a\fstest_data.mdf'),
FILEGROUP FileStreamFileGroup CONTAINS FILESTREAM
NAME FileStreamTestDBDocuments,
FILENAME N'C:\DemoDatabases\DbFiles\a\Documents')
LOG ON
NAME 'fstest_log',
FILENAME N'C:\DemoDatabases\DbFiles\a\fstest_log.ldf');
GO

--Baseline:
BACKUP DATABASE fstest TO DISK = 'C:\x\fst.bak' WITH INIT
--4.08 MB

BACKUP DATABASE fstest TO DISK = 'C:\x\fst_d.bak' WITH INITDIFFERENTIAL
--3.08 MB

CREATE TABLE fstest..t (
TestId UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE
,Name VARCHAR (25)
,
Document VARBINARY(MAXFILESTREAM);
GO

INSERT INTO fstest..t
VALUES(NEWID (), 'Hello there'CAST(REPLICATE(CAST('M' AS VARBINARY(MAX)), 1000000AS VARBINARY(MAX)));
GO 10

BACKUP DATABASE fstest TO DISK = 'C:\x\fst.bak' WITH INIT
--13 MB

BACKUP DATABASE fstest TO DISK = 'C:\x\fst_d.bak' WITH INITDIFFERENTIAL
--3.08

--Cleanup
IF DB_ID('fstest'IS NOT NULL DROP DATABASE fstest

Restore database to the point of disaster

This is really basic, but so often overlooked and misunderstood. Basically, we have a database, and something goes south. Can we restore all the way up to that point? I.e., even if the last backup (db or log) is earlier than the disaster?
Yes, of course we can (unless for more extreme cases, read on), but many don’t realize/do that, for some strange reason.

This blog post was inspired from a thread in the MSDN forums, which exposed just this misunderstanding. Basically the scenario was that they do db backup and only log backup once a day. Now, doing log backup that infrequent is of course a bit weird, but that is beside the point. The point is that you can recover all the way up to the point of disaster. Of course, it depends on what the disaster is (don’t expect too much if the planet blows up, for instance).

Since “log backup only once a day” was mentioned, I will first elaborate a bit on frequency for database vs log backups. For the sake of discussion, say we do both db and log backup once a day. You say:
“What? Both db backup and log backup once a day – why would anybody do that way? Wouldn’t one do log backup more frequently than db backup?”
Yes, of course (but I actually see such weird implementations from time to time). But again, that doesn’t change the topic at hand, but I will first elaborate on this; just so we don’t see blurring comments later arguing this irrelevant argument.

So, lets first sort out two different cases:

A) Log backup before the db backup
1: db backup

2: log backup
3: db backup
crash
Here we will use backup 3 when we later will restore.

B) Db backup before log backup
1: db backup

2: db backup
3: log backup
crash
Here we will use backup 2 and 3 when we later will restore.

You see that A) and B) are really the same thing? What is relevant is that we have all log records available (in ldf file/log backups) since the db backup we chose to use as starting point for the restore. Actually, for A), we could might as well use backup 1 and 2 (and skip 3)!

“Hang on”, you say, “we’re not done yet. What about the modifications since the last log backup! Gotcha!”
No worries, this is where it gets interesting, and below is really the heart of the topic. Clearly, we need to get the log records out of the ldf file into a log backup (file). If we can do that, then we will call this backup number 4, and use as the last backup for our restore. After doing that restore, we have no data loss!

So, how do we produce a log backup after a disaster?
It depends on the disaster! Let’s discuss a few scenarios:

a) Planet Earth blows up.
No can do. I doubt that anyone of you has mirrored data centers on Moon or March; and also people stationed off-Earth for these situations. Of course, I’m being silly. But my point is that you can always have a disaster such that you can’t produce that last log backup. No matter how much you mirror: if the disaster takes out all mirrors, then you are toast. Remember that when you talk SLA’s. That fact is not popular, but it can’t be argued. It is all about limiting the risk exposure – not eliminating it. Anybody who believes we can eliminate risk exposure is dreaming. Agreed? Good. Let’s move on to (hopefully) more realistic scenarios:

b) Something happens with the data file(s), lost, corrupt or so. Ldf file is still there.
This is the easy case, but so often overlooked. What you do now is to backup the log of the damaged database, using the NO_TRUNCATE option. Something like:
BACKUP LOG dbname TO DISK = ‘C:\dbname.trn’ WITH NO_TRUNCATE
Yes, it really is that simple. Then restore backups from above, including this last log backup. Don’t believe me? Test it.

  1. Create database and table
  2. Insert some data
  3. Do db backup (1)
  4. Insert some more data
  5. Do log backup (2)
  6. Insert some more data
  7. Stop SQL Server
  8. Delete mdf file
  9. Start SQL Server
  10. Do log backup using NO_TRUNCATE (3)
  11. Restore 1, 2 and 3.

c) Something happens with the database. Ldf file is NOT still there.
Clearly, if the ldf file is really gone, we can’t do a log backup – how much as we might want to. Remember the old days, when redundancy for disks (RAID) wasn’t as common as today? “If there’s anywhere you want redundancy, it is for the transaction log files!”

d) Something happens with the data file(s), lost, corrupt or so. Ldf file is still there. The installation is toast – we can’t start SQL Server.
This seems a bit more nerve-wracking, right? Not to worry, just do the right steps and you will be fine. You probably ask now:

“But how can we backup the transaction log when our SQL Server won’t start?”

That is a good question. You need to get that ldf file to a healthy SQL Server, and make SQL Server believe this is the ldf file for a broken database on that instance. It is not really complicated. Just use a dummy database on that SQL Server as intermediate – to get the right meta-data into that SQL Server, so in turn it will allow you to produce this last log backup. I will show just that:

I have two instances on my machine (named “a” and “b”). I will create and damage a database on instance a, and then produce a log backup for that orphaned ldf file a different instance, b. I will pretend these are on two different machines, using separate folders for the database files “C:\a” and “C:\b”. Here’s the T-SQL, starting with instance a:

IF DB_ID('x'IS NOT NULL DROP DATABASE x
GO
CREATE DATABASE x
ON PRIMARY
(NAME N'x'FILENAME N'C:\a\x.mdf'SIZE 10MBFILEGROWTH 10MB)
LOG ON
(NAME N'x_log'FILENAME N'C:\a\x_log.ldf'SIZE 5MBFILEGROWTH 5MB)
GO
ALTER DATABASE SET RECOVERY FULL
CREATE TABLE 
x.dbo.t(c1 INT IDENTITY)
INSERT INTO x.dbo.t DEFAULT VALUES --1
BACKUP DATABASE TO DISK = 'C:\x.bak' WITH INIT
INSERT INTO x.dbo.t DEFAULT VALUES --2
BACKUP LOG TO DISK = 'C:\x1.trn' WITH INIT
INSERT INTO x.dbo.t DEFAULT VALUES --3
SELECT FROM x.dbo.t

--Stop SQL Server and delete below file
--C:\a\x.mdf
--Start SQL Server

--Oops, damaged database...:
SELECT FROM x.dbo.t

--Stop SQL Server, pretend installation is toast

Do we agree that we have a damaged database, and there has been done modifications since the last log backup? Ok, fine. We now pretend that SQL Server instance “a” doesn’t start anymore. So, I will try to produce a log backup from that ldf file on instance “b”:

IF DB_ID('x2'IS NOT NULL DROP DATABASE x2
IF DB_ID('x'IS NOT NULL DROP DATABASE x
GO
CREATE DATABASE x2
ON PRIMARY
(NAME N'x2'FILENAME N'C:\b\x2.mdf'SIZE 9MBFILEGROWTH 8MB)
LOG ON
(NAME N'x2_log'FILENAME N'C:\b\x2_log.ldf'SIZE 6MBFILEGROWTH 7MB)
GO

--Stop SQL Server and delete below files
--C:\b\x2.mdf
--C:\b\x2_log.ldf

--Copy the C:\a\x_log.ldf to C:\b\x2_log.ldf

--Start SQL Server

--Produce our last log backup:
BACKUP LOG x2 TO DISK = 'C:\x2.trn' WITH INITNO_TRUNCATE

--Restore the database, up to last transaction.
--Investigate logical file names for MOVE options first:
RESTORE FILELISTONLY FROM DISK = 'C:\x.bak'

RESTORE DATABASE FROM DISK = 'C:\x.bak'
WITH
NORECOVERY
,MOVE 'x' TO 'C:\b\x.mdf'
,MOVE 'x_log' TO 'C:\b\x_log.ldf'

RESTORE LOG FROM DISK = 'C:\x1.trn' WITH NORECOVERY
RESTORE LOG FROM DISK = 'C:\x2.trn' WITH RECOVERY

--Data there?
SELECT FROM x.dbo.t
--See? That wasn't so difficult.

Note how I even named the dummy database differently on instance b, with different physical file names and different file sizes (all compared to what we had on instance a). Typically, you will use same database name and same filename, but I want to show that we don’t really have to know a whole lot about the damaged database in order to produce a log backup from the ldf file!

Case closed.

 

Do you perform log backup for the model database?

Are you stupid, you might think… But stop and think for a while. Model is no different from other databases. And by default it is in full recovery model. So as soon as you do your first database backup (you do backup your system databases, right?) the log for model will start filling up and autogrow. “But, hey, I don’t do any modifications in model!”, you probably say now. Fair, but other things happens in each database from time to time. Bottom line is that ldf file for model will start growing after a while . Perhaps not huge, but I find it “un-neat” to have a model with 3 MB mdf file and 20 MB ldf file.

Personally I prefer to have model in simple recovery since I feel that is a better default recovey model. An alternative is to regurarly set model in simple recovery and back to full recovery (schduled job).

Table restore and filegroups

The story usually goes something like:

Q – How can I restore only this table?
A – Put it on its own filegroup and you can do filegroup level backup and restore.

The problem with above answer is that it most likely misses the point. We need to ask ourselves:
Why do you want to do a table level restore?

The answer to the question is very often that the table need to be reverted to an earlier point in time, possibly because some accident happened; like deleting all rows in the table by mistake. (See my minimizing data loss when accidents happens article for a more general discussion.) So, why is not filegroup backup that usable for this scenario?

SQL Server will not let you into a database where different data is from different points in time!
(2005+, Enterprise Edition and Developer Edition, has online restore which allow you into the database but you wont be able to access the restored data until you make it current – so it doesn’t really changes the basic issue here.)

Now, think about above. If we restore the filegroup backup containing the emptied table, but then need to restore all subsequent log backups up to “now”, what good did this song-and-dance-act do us? No good at all (except for a learning experience, of course).
We can of course restore the primary filegroup and the one with the damaged data into a new temp database – to the desired earlier point in time, and then copy the relevant data from this temp database into the production database. But this operation is certainly not as straight forward as just restoring the filegroup backup into the production/source database.

Now, about having data from different point in time (regardless of how you achieve it): Handle with care. Just think about relationship and dependencies you have inside a database. Reverting some table to an earlier point in time can cause havoc for those dependencies.

I won’t get into details about how filegroup backups work, online restore, the PARTIAL option of the restore command etc. – you can read about all that in Books Online. The point about this blog is to have somewhere I can point to when I see the “put-the-table-on-its-own-filegroup-and-backup-that-filegroup” recommendation.

As usual, I have a TSQL script to display my points. If you happen to think that it *is* possible to restore part of the database to an earlier point in time into the production/source database – I’m all ears. You can post a comment here, I will be notified. Please use below script as a template, and modify so that we can execute it and re-execute it.
The usual disclaimer is to not execute below if you don’t understand what it is doing, etc.

 

--Drop and create the database
USE master
IF DB_ID('fgr'IS NOT NULL DROP DATABASE fgr
GO
--Three filegroups
CREATE DATABASE fgr ON  PRIMARY
NAME N'fgr'FILENAME 'C:\fgr.mdf'),
FILEGROUP fg1
NAME N'fg1'FILENAME 'C:\fg1.ndf'),
FILEGROUP fg2
NAME N'fg2'FILENAME 'C:\fg2.ndf')
LOG ON
NAME N'fgr_log'FILENAME 'C:\fgr_log.ldf')
GO
ALTER DATABASE fgr SET RECOVERY FULL

--Base backup
BACKUP DATABASE fgr TO DISK = 'C:\fgr.bak' WITH INIT
GO

--One table on each filegroup
CREATE TABLE fgr..t_primary(c1 INTON "PRIMARY"
CREATE TABLE fgr..t_fg1(c1 INTON fg1
CREATE TABLE fgr..t_fg2(c1 INTON fg2

--Insert data into each table
INSERT INTO fgr..t_primary(c1VALUES(1)
INSERT INTO fgr..t_fg1(c1VALUES(1)
INSERT INTO fgr..t_fg2(c1VALUES(1)

BACKUP LOG fgr TO DISK = 'c:\fgr.trn' WITH INIT --1

--Filegroup backup of fg2
BACKUP DATABASE fgr FILEGROUP 'fg2' TO DISK = 'C:\fgr_fg2.bak' WITH INIT

BACKUP LOG fgr TO DISK = 'c:\fgr.trn' WITH NOINIT --2

--Delete from t_fg2
--Ths is our accident which we want to rollback!!!
DELETE FROM fgr..t_fg2

BACKUP LOG fgr TO DISK = 'c:\fgr.trn' WITH NOINIT --3

--Now, try to restore that filegroup to previos point in time
RESTORE DATABASE fgr FILEGROUP 'fg2' FROM DISK = 'C:\fgr_fg2.bak'
GO

SELECT FROM fgr..t_fg2 --error 8653
GO

--If we are on 2005+ and EE or Dev Ed, the restore can be online
--This means that rest of the database is accessible during the restore
INSERT INTO fgr..t_fg1(c1VALUES(2)
SELECT FROM fgr..t_fg1

--We must restore *all* log backups since that db backup
RESTORE LOG fgr FROM DISK = 'c:\fgr.trn' WITH FILE = --out of 3
RESTORE LOG fgr FROM DISK = 'c:\fgr.trn' WITH FILE = --out of 3
GO

SELECT FROM fgr..t_fg2 --Success
--We didn't get to the data before the accidental DELETE!
GO

----------------------------------------------------------------------------
--What we can do is restore into a new database instead,
--to an earlier point in time.
--We need the PRIMARY filegroup and whatever more we want to access
----------------------------------------------------------------------------
IF DB_ID('fgr_tmp'IS NOT NULL DROP DATABASE fgr_tmp
GO
RESTORE DATABASE fgr_tmp FILEGROUP 'PRIMARY' FROM DISK = 'C:\fgr.bak'
WITH
MOVE 'fgr' TO 'C:\fgr_tmp.mdf'
,MOVE 'fg2' TO 'C:\fg2_tmp.ndf'
,MOVE 'fgr_log' TO 'C:\fgr_tmp_log.ldf'
,PARTIALNORECOVERY

RESTORE DATABASE fgr_tmp FILEGROUP 'fg2' FROM DISK = 'C:\fgr_fg2.bak'

RESTORE LOG fgr_tmp FROM DISK = 'c:\fgr.trn' WITH FILE = 1NORECOVERY
RESTORE LOG fgr_tmp FROM DISK = 'c:\fgr.trn' WITH FILE = 2RECOVERY

--Now the data in PRIMARY and fg2 is accessible
SELECT FROM fgr_tmp..t_fg2

--We can use above to import to our production db:
INSERT INTO fgr..t_fg2(c1)
SELECT c1 FROM fgr_tmp..t_fg2

--And now the data is there again 🙂
SELECT FROM fgr..t_fg2