Transaction log backup not completing because there is no database backup

1

This morning I switched my database from FULL to SIMPLE recovery mode and then back to FULL. As soon as I switched it back to FULL I ran my full backup job which is a custom script in an agent job. The full backup completed at which point I tried my transaction log backup and received the error below.

BACKUP LOG cannot be performed because there is no current database backup.

The last_log_backup_lsn is NULL in sys.database_recovery_status

Below is the transaction log script I am trying to run:

DECLARE @FileName varchar(100)
SET @FileName = 'W:\Backup\myDB\myDB_LOG_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(), 20),'-',''),':',''),' ','') + '.trn'
BACKUP LOG myDB
TO DISK = @FileName WITH INIT, COPY_ONLY

Anyone have any ideas?

2

Modify the script you used for full backup and remove the COPY_ONLY option and take a full back up using the modified script. Then do the log backup. When you do the log backup as part of your regular backup plan, you should not use the COPY_ONLY option.

The COPY_ONLY option allows you take a "non-invasive" backup. See details here.

3

Shouldn't raise an error though, if a FULL backup has been taken, should it?

I would check the Backup History in MSDB - maybe something other than "you" did something

SELECT TOP 100
	BS.backup_set_id
--	, BS.database_name
	, BS.backup_start_date
	, BS.backup_finish_date
	, BS.type
	, [Size KB] = CONVERT(int, BS.backup_size / (1024))
-- 	, [Comp KB] = CONVERT(int, BS.compressed_backup_size / (1024))
	, BS.name
	, BS.user_name
-- 	, BS.first_lsn
-- 	, BS.last_lsn
	, BS.database_backup_lsn
	, BS.description
	, BMF.family_sequence_number
	, BMF.device_type
	, BMF.physical_device_name
-- Slim field list:	SELECT TOP 100 BS.backup_start_date, [Dur]=DATEDIFF(Minute, BS.backup_start_date, BS.backup_finish_date), BS.type, [Size MB]=CONVERT(int, BS.backup_size/(1024*1024)), [Size]=BS.backup_size
FROM	msdb.dbo.backupset AS BS
	LEFT OUTER JOIN msdb.dbo.backupmediafamily AS BMF
		ON BMF.media_set_id = BS.media_set_id
WHERE	1=1
	AND BS.database_name = DB_Name()		-- Current DB
--	AND BS.database_name = N'MyDatabaseName'	-- SELECT DB_NAME()
--	AND BS.name LIKE 'MyDatabaseName' + '%'		-- Specific backup name
--	AND BS.physical_device_name LIKE 'MyDatabaseName_2012_03_01_1250%.trn'	-- Specific filename
	-- type : D=Full, I=Differential, L=Log, G=FileGroup, V=VerifyOnly
-- 	AND BS.type IN ('D', 'I', 'L')
--	AND BS.backup_size > 10000000
--	AND BS.compressed_backup_size > xxx
--	AND BS.backup_start_date >= '20160101 00:00'
-- 	AND BS.backup_start_date <  '20161231 00:00'
--
ORDER BY BS.backup_start_date DESC, BS.database_name, BMF.family_sequence_number
4

Ah ... unless the FULL backup was also using the COPY_ONLY option, I suppose that wouldn't have made a suitable start-point for LOG backups

5

James - thanks but the reason for the COPY_ONLY option is because this is a local backup. We have other backups being done by another department (off site) using NetBackup. They are doing Full backups and Differential. They could not restore the differential backup because we were doing our own local SQL Server backups (FULL and TRN). Once I switched our to COPY_ONLY we were able to conduct both the offsite backup and our local one. It has been working fine this way for months until I changed the recovery mode to simple then back to full.

6

Sounds like you used COPY_ONLY on the FULL backup. That won't create a start-point for Log Backups

7

Personally I think it would be much better that NetBackup did the COPY backup and you had control over the actual backup ...

Far greater likelihood that you want to restore from a local file than a Jumbo Jet landing on the building and you having to do a Disaster recovery restore ... or you want to do some maintenance on your DB (like now)

What if you want to do some index rebuilds and jack up the Log backup frequency to, say, every minute to stop the logs growing large. You are currently reliant on when the next NetBackup runs to keep the Logs under control.

Out of curiosity why did you change the DB to Simple?

I've seen lots of Netbackup type things set up by IT Support folk who don't properly understand SQL DBs ... I was visiting one last week, they had a 100MB SQL data file and a 40GB Log File. Backups set up by an outsourced IT company who didn't realise that they needed to make Log Backups ... the indexes were badly fragmented, the statistics never rebuilt, and all that jazz too ...

8

This explains everything.

The reason I switched to SIMPLE recovery model was because the transaction log backups were increasing. The database backup is around 30GB and the transaction log backup was up to 12GB. I wasn't monitoring the size regularly but it seemed larger to me.

Does that mean those transaction log backups I have been doing were based off the last full backup that was done without the copy_only option? The copy_only option does not reset the transaction log to the initial size when the full backup runs either, I assume.

9

The purpose of differential backups and log backups is to give you the ability to restore your database to some point in time after your full backup. SQL Server achieves that by restoring the full backup and then the differential backups (or log backups with an unbroken chain). So for log backups to be of any use, you need a full backup (taken without the COPY_ONLY option).

In full and bulk-logged recovery models, the log file would not be "cleared" until you take a log backup. Clearing is the process of marking the unused virtual log files as being available for reuse. So taking a log backup does not necessarily reduce the size of the log file on the disk, but it does make more free space available within the file for SQL server to reuse. For all of that to happen, the log file has to be taken without the COPY_ONLY option.

Does that mean those transaction log backups I have been doing were based off the last full backup that was done without the copy_only option?

Correct. When you switched the recovery models, there was no starting point for the log backups; that is why it was complaining.

10

Not quite, they would be based off when the last NetBackup Log backup ran. Which would be useless, IMHO, unless NetBackup Log is running at an interval you are happy with. We run our Log Backups every couple of minutes - that's the maximum data loss we want !! Most failures are not catastrophic, so with less frequent Log Backups you might have enough hardware left running that you CAN run a Tail Log Backup before you Restore (and thus still have zero data loss), but if your disks are fried, say, that won't be the case and your data-loss is then however long it was since the NetBackup ran either a Full Backup or a Log Backup. If your Log Backup is once a day its utterly useless. You might as well run Simple and just run a full backup once a day and be prepared to lose 23h59m data max. (I'm being slightly unkind, IF you hardware is not bust then you would be able to run a Log Backup just before the Restore, so to fix an accident where someone deleted half your customer records by mistake even a once-a-day Log Backup will do. But in terms of data loss after a disaster its useless.

Full backup doesn't reset the Log anyway. Only backing up the Log does that.

You can restore a Full backup from ages ago, and then restore all log backups since then, provided you restore them all, in order. There is nothing in the Log backups which is dependent on a particular Full backup (except when you Restore or change SIMPLE to FULL recovery model, in that situation you need to start-from the same restored backup, or the first Full Backup after the recovery model change)

Point to note:

Assuming your Log file is on a different Disk and Controller to your Data File, and if the Disk and/or Controller that your Data file is on became faulty, and started corrupting your database ... and you didn't notice for a couple of weeks ... then because your Logs were on a different drive / controller you would be able to make a final (Tail) Log Backup, restore from Full Backup two weeks ago (i.e. before the hardware fault), and then all Log Backups since, and you would have zero data loss. This is often the case even if the Log and Data files are on the same disk-spindle, because of the different way that the two are written. Not guaranteed in that scenario though. Also helps if the Log Backups are not on the same device as the Data File ...

11

Whilst we're on the subject of avoiding disasters :slight_smile: might be worth you checking that you have CHECKSUM turned on on all your databases. That would give you early warning (compared to NOT having Checksum) if you get any disk corruption

https://www.brentozar.com/blitz/page-verification/

12

Thank you both for the information.

Just to clarify one point. The Netbackup is a weekly full backup and differential backups every other night. They are not doing log backups. The issue is with Netbackup apparently not being able to restore differential backups if you are doing local SQL backups. So unfortunately having the Netbackup do the copy_only is not an option.

https://www.veritas.com/support/en_US/article.TECH70184

13

If your database is in FULL or BULK LOGGED recovery mode, you MUST do log backups (without the COPY_ONLY option). This is independent of whether you (or they) are doing full backups and differential backups. If you don't do log backups, your log file will keep growing. You might throw away the log backup that you take, but you have to do it.

In your scenario, if Netbackup is doing Full backup (and differential backup, but that is irrelevant), you should schedule log backups on your own if that is what it takes.

Another alternative would be to change to simple recovery model and leave it at that. That will mean that your recovery options will be limited to the point in time at which the last full backup or differential backup was taken (which ever is later). If you have log backups, you can recover to any point in time up to the last log backup (and even later if you are able to take a tail-log backup in the event of a disaster)

14

So, basically, after doing something like the FULL-SIMPLE-Back-to-FULL manoeuvre you have to wait, potentially, as long as a week until the next NetBackup full backup runs.

Which IMNO neatly illustrates why this type of backup is rarely a good solution.

Every-other-day DIFF would worry me, as that's potentially 48 hours data loss, but it very much depends on how much data change you have going on, and how easily that could be repeated. If its all key-to-disk from paper forms then easy to repeat (albeit annoying ...) but if its real-time from phone calls etc. then its probably impossible to contemplate repeating the data entry and you need up-to-the-minute backup.

With where you are right now you need to take a Full Backup, so that your Log Backups start working. Yes, that will probably break the DIFF backups via NetBackup (well ... it might not, it might just mean that you would have to restore YOUR Full Backup and the next DIFF off NetBackup ... if you needed to). Definitely the Netbackup DIFF backups are going to be reliant on the NetBackup Full backups, so important that you only make COPY Full backups (I can't see that a Non-Copy DIFF backup would matter though), but in this case you have no choice - short of waiting up to a week for the next Full NetBackup.

So... if you did restore off NetBackup how would you, then, include a Log Restore? By the sound of it the Restore would come direct off NetBackup and not be able to incorporate a Log Restore as well ... in which case the Log backups are not much use, unless you also have a COPY Full and optional Differential backup.

I have a strongly held personal view here, so ignore it if you don't agree :slight_smile:

We only make backups to disk, using SQL's own backup command. We then use "some other means" to safeguard those backup files (in our case: as soon as the backup is made it is immediately copied to another server, physically elsewhere in the building, and within a short period of time it is offsite to our Cloud Backup).

I am in total control of being able to easily restore, and any other changes that I want - such as taking additional Log Backups when rebuilding Indexes and so on. I have access to all the Backup Files that I make, without being reliant on some 3rd party solution.

I have 2 days worth of log backups locally on the server, a weeks worth of daily DIFF backups, and 4 weeks work of Sunday full backups. I can restore from any of those without needing to involve anyone else, or having to figure out how to get something back from the Cloud ... so we really only expect to have to restore from the off-site backups in a total disaster scenario.

The reason for using the MS SQL Backup command is that I don't have to worry that "some 3rd party driver" (in your case the version of NetBackup or a NetBackup "Agent") is no longer compatible with the version of SQL that I have patched / upgraded to. I can also take advantage of compressed backups which significantly reduces Backup Time, Disk Storage and also Restore Time.

I'm in total control of being able to do a Restore. Having to restore a production database is an almost-never situation here - probably for you too - but restoring an old backup to a New, Temporary database, having a look at some historical data, figuring out WHY something happened, or (on a couple of occasions) investigating a possible Fraud ... and then dropping the TEMP database ... is quite common - for me at least.

Personally I'd get rid of NetNackup and replace it with something that backs up the physical backup files. If you are not confident about setting up a robust backup system then have a look at Minion Backup - it reliable ""out of the box" with its default settings, and you can fiddle with it to suit you better only if you feel the need.

15

Just to clarify, apologies if I'm labouring the point!

If you have been making COPY ONLY Log Backups the log file will have grown continuously.

Because your NetBackup is NOT doing any Log Backups you can just do a normal Log Backup. Each log backup you make will "clear" the file, making it available for reuse.

Schedule the log backups as often as you can afford to lose data. If you schedule them for every-minute you will have a lot of files at the end of each day, but they will all be small, and their total filesize (ignoring a little "overhead") will be the same as a single daily log backup.

NOTE: You will NOT be able to restore from the Log Backup unless you can also, in the same operation, restore from a Full Backup (and optionally a Diff backup too). If you do NOT think you can do that with the NetBackup then I suggest you also make a COPY ONLY Full Backup (or do that once a week and a COPY ONLY Differential backup daily ... but by then you will be doing the same job as Netbackup, surely?!!)

If you have runaway log growth in future, rather than doing the FULL-SIMPLE-FULL toggle do this instead:

  • Make a Log backup
  • Shrink the Log File
  • If necessary (it doesn't shrink enough) repeat once more.

You should not repeatedly shrink the log file, it will get fragmented, so:

  1. only shrink it to its normal working size, not smaller
  2. if this is anything other than a one-off type situation investigate why the log file is growing so big.
16

Kristen - thanks again for taking so much time to respond. I understand what you are saying and we are complete agreement. Unfortunately there are a lot of forces at work here and it is out of my control. We are a division at the mercy of two other IT divisions that call most of the shots. When I got here we were doing a single night backup locally and overwriting it each night. Nobody was monitoring the success/failure and no restores were ever scheduled to our dev server. I was not satisfied with it which is when I started saving three nights full backups and doing the transaction log backups every two hours (The data is entered in by our finance staff and it is not critical that it be backed up more frequently) during the day. It is not a huge database so I opted against doing differential backups.

So for now I have to switch the local backup to full copy only (so it does not conflict with netbackup) and stop the transaction log backups. The only option I see is if we can convince OIT to do the full backup copy only through Netbackup which would give me a lot more flexibility to do the local backups.

thanks again.

17

Sounds like you are in pretty good shape.

If I were you I'd do an unannounced fire-drill. Backup the database, restore it to a new TEMP database name (to prove that you can!), run CHECKDB DBCC on it to prove its in good shape, and then ask for a Restore from NetBackup. choose a time that is late-on on day-two between DIFF backups so that you have maximum data loss ... and see whether the people around you think that's good enough ...

Probably not a good idea if it simultaneously requires you to smarten up your CV !!

18

You should not need to do this. I also suspect this situation is fairly common.
On some of our servers we have VSS doing FULL (non-copy only) and DIFF backups. (Your Netbackup will be using VSS.) In theory this allows our sysadmin team to recover the server if needed but I do not think it has every been tested. For the DIFFs to work the VSS FULL backup will need to be non-copy only.

The production DBs are in FULL recovery. I take COPY_ONLY full backups and non-copy only LOG backups which I immediately copy to another server. (The LOG backups need to be non-copy only in order to truncate the log.) This keeps the log files at a reasonable size, allows point in time restores and, in the event of disaster, would probably mean less data loss. I also automate the restore of my backups and the running of CHECKDB to ensure integrity.

This setup allows sysadmin backups and DBA backups to play nicely together. The only downside is that I cannot use DIFF backups so can end up restoring a lot of logs. I have a powershell script to generate the RESTORE LOG commands in the correct order from files on disk.

19

ps On the rare occasions when I need to change a production DB from FULL to SIMPLE recovery and back again I:

  1. Give our sysadmin team notice of what I intend to do.
  2. Ask sysadmin to do a full backup once the DB is back in FULL recovery.
  3. Do a COPY_ONLY full backup myself.

I do not get any log backup errors as I use Ola Hallengren's scripts. These will not attempt a log backup until they detect that a non-copy only FULL backup has been done.

20

Got that. But if you do anything that breaks the LOG chain you can't restart your logs unless you either a) make a FULL (non-COPY_ONLY) backup (and break the alternative systems's DIFF chain) or b) wait for the next FULL backup done by the alternatively backup system.

I would consider that a huge disadvantage, but maybe you very very rarely have to restore a DB.

If I have to restore a DB to point-in-time late in the day I curse that we do not, routinely, make DIFF backups DURING the day!! because of the amount of time it takes to restore all the Log files.

If we have, say, corrupted Full backups such that I have to go back a long way and restore, say, a week's worth of LOG files it becomes a huge undertaking.

Restore a Production database is an almost-never occurrence for me, but restore one to a TEMP DB and investigate something, or do that for a DEV DB etc., is quite common.

That's been my experience too , and I haven't come across one instance, yet, where it was a good idea. Backing up to Files and then having something that backs up those Files would have been a much better solution in all in instances I've come across. These tend to be relatively small companies where the IT people have installed the equivalent of NetBackup and naively thought that was all that was required. I don't think I've seen a single one where a restore to -point-in-time was possible, and yet all the users do need that ability as they couldn't possibly recreate a whole day's data in the event of a total failure