SQL Differential backups failing with “current database backup does not exist” error

I recently set up an Azure VM and installed SQL Server 2017 – it worked great until it came time to set up the database maintenance plans. My normal routine is to set up a weekly full database backup, daily differential database backups, and hourly log backups. Thanks to Azure, I was able to send them to URL so they went directly to my BLOB storage container.

After setting up the backups, I tested them and everything went time – I ran the weekly full successfully, then the daily differential, then the logs, and didn’t get any errors. However, that night I got an email that the differential had failed, and the agent log had this note in it:

Code: 0xC002F210
Source: (Maintenance plan)
Execute SQL Task
Description: Executing the query "BACKUP DATABASE [MyDatabase] TO U…" failed with the following error: "Cannot perform a differential backup for database "MyDatabase", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I had tested everything earlier, so I wasn’t sure why the differential backup was failing now (the log backups were working without issue the whole time, so it wasn’t storage). I executed the differential again and it failed with the same error. I ran the full backup successfully and then tested the differential backup a few times – it now ran successfully each time. I shrugged and went on my way, chalking it up to something going wrong, only to have the differential backup fail again that night with the same error.

To see what was going on, I took a look at the backup sets and noticed some backups I hadn’t scheduled going a device I didn’t recognize (all of my scheduled backups were to URL, so they were easy to spot because they all start with “HTTPS://”):

select top 100 *
from msdb.dbo.backupmediafamily mf
where TRY_CONVERT(uniqueidentifier, physical_device_name) is not null

The GUID indicates a backup taken through the VSS service – in this case, they were being created externally by the Azure VM Snapshot process. However, these backups are by default official database backups, and they interrupt the database backup chain – had I attempted to restore one of my full database backups followed by the log backups, I would have found that I couldn’t restore past the VSS backup timestamp because I didn’t have access to that media.

The good news is that there’s a registry key you can use to tell the VSS service that database backups should be taken as COPY_ONLY (meaning they don’t interrupt your backup chain). You can do this with the following registry key:

Windows Registry Editor Version 5.00 

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BcdrAgent]

"UseVSSCopyBackup"="True"

This tells the server that when VSS takes a snapshot, it should do it as a non-authoritative copy-only backup. This also means it won’t reset any attributes in the VM that say a backup has happened, but in my case, I didn’t want that to happen.

To learn more, check the Microsoft articles that provide details about snapshot backups for Azure VMs – neither refers to the error message specifically, but they provide some additional details about configuration. They’re https://docs.microsoft.com/en-us/azure/backup/backup-azure-vms-introduction#snapshot-creation and https://docs.microsoft.com/en-us/azure/backup/backup-azure-vms-troubleshoot#troubleshoot-vm-snapshot-issues (both linked to the most relevant section).

I hope this saves you the hours of aggravation that it cost me – let me know if it helps or if you have any issues!

Passing multi-value report parameters to SSRS using the URL querystring

Passing parameters to reports in SSRS using the URL can be really helpful, especially when you want to link to a report with some of the data aleady filled out. We had an email alert that some accounts in active directory were incorrect, and we wanted a way to link to a report that provided some additional detail for those accounts. Querystring parameters aren’t very well documented (and I couldn’t find anything on multi-value parameters), and I hope this saves you some time if you’re in the same situation.

There’s a pretty good reference on Technet, but it’s got some gaps (for me, the biggest was even mentioning multi-value/multi-select parameters). The basic syntax is pretty straightforward – normally, a report is called with a URL like the following:

http://YourSsrsServer/Reports/Some%20Folder/YourReportName

From there, you can select the parameters and run the report. To provide a link to the report with the parameters already filled out (if they’re blank or if you gnat to override the defaults), just append this to the end of the URL:

?ReportParam1=SomeValue&ReportParam2=SomeOtherValue

You can pass any type of parameter with this same syntax – text, datetime, integer, float, or boolean (use Yes/No). Your full url would look like this:

http://YourSsrsServer/Reports/Some%20Folder/YourReportName?ReportParam1=SomeValue&ReportParam2=SomeOtherValue

To do a multi-value parameter, you repeat the name of the parametr to provide it multiple values:

http://YourSsrsServer/Reports/Some%20Folder/YourReportName?ReportMultiParam=SomeValue&ReportMultiParam=SomeOtherValue

If this is the only parameter on the report and it doesn’t need any other user input, the report will automatically execute because it has everything it needs. If you want the report to wait for the user to hit “View Report”, you need to leave at least one parameter empty.

Two quick notes if you’re having trouble:

  • Use the parameter name, not the prompt text. If your parameter is called “Office” but the prompt says “Which office?”, use “Office”. Parameter names aren’t always intuitive and you may need to open the report in Visual Studio to get these names if you can’t guess them.
  • In the same way, you need to use the field’s value, not the label. If the dropdown contains the full name of the office, but the value behind the list is the office number, you’ll need to provide the numbers on the querystring.

Good luck!

Microsoft Connect item for an SSRS pre-report canvas

Current state:

About six months ago, I submitted a connect request for SQL Server (my first one) to suggest an improvement to the SSRS interface – a customizable canvas that’s displayed before an SSRS report renders, while parameters are being entered. My issue was that there was information I wanted report users to have (like expected wait time, suggestions for the parameters, the report title and a short description that might help them, etc.) and there was no way to get it to them.

With a giant empty screen staring at them, it seemed like a great place to add that information:

SSRS Report pane current layout

The suggestion:

Ideally, while the user is considering parameters, I’d love to see something like this (ignoring that it’s not a super-photogenic report):

proposed-layout

This would be a great place for details about the report that help the user before (or while) they run it, like:

  • The name of the report (not always clearly visible in the URL or browser title bar, especially when you’re Sharepoint-integrated. Especially helpful if they’ve left multiple report tabs open or if report generation fails, they’re often left with a white screen and no easy way to tell the tabs apart.
  • A short description of the report, including when you’d use it or any notable caveats.
  • An estimated runtime – this isn’t always possible exactly as parameters vary, but even a general estimate (or better yet, an average or a 95% confidence range, pulled from the report server database) would be better than the complete blank they get today.
  • Suggestions/restrictions on the parameters – If a report can’t be run for all offices at once, you can say so here. If running it for everybody at once means you can’t export to Excel because it’s too large, let the user know.
  • A mock-up/screenshot of the report – so they user can see if it’s the one they want. They may be looking for a report with a specific chart, and waiting for 3 minutes for each one to render may not be ideal.
  • A link to any other documentation/resources, or related reports, or pretty much anything else.

It wouldn’t need to be anything fancy – I’d even settle for text you can format, or some kind of additional panel on the report that you can design, but isn’t rendered with the report itself. Access to data (to pull estimated on execution times or dynamic owner/description details, for example) would be nice, but not required – I’d rather have a design-only, no-data canvas than nothing at all.

The plea:

t-sql-tuesdayThanks to the motivation from the folks at Brent Ozar Unlimited, I’m asking for your help in voting for this suggestion (if you think it would be beneficial, of course). As part of T-SQL Tuesday, they’ve asked people to find a connect request that they’d like to see filled and post about it, so here I am asking humbly for your support. : )

If you like the idea, please vote for it – if you don’t, please comment and let me know why not (I’m always open to understanding the opposition). There are a ton of great Connect items out there and this is only one, but I think it would help out (especially for our longer-running reports).

Thanks for your help!

Direct URL for the Connect item: https://connect.microsoft.com/SQLServer/feedback/details/2809098/ability-to-customize-report-splash-screen-during-parameter-submission

Side note:

I’ve attached the two images (current/proposed) to my connect item three different times and they’re still not showing up there – if anybody knows how to get those images published, people can see what I’m talking about when I describe my suggestion! : )

Query Business Objects repository for list of reports

We’re migrating away from Business Objects Enterprise (XI-R2, not the newer 3.X version) and into SQL Reporting Services (it’s about time!) and I needed an inventory of everything that was still in BOE. The repository is obfuscated so that it’s not human readable, but thanks for some sleuthing and an 8-year-old post from a Business Objects forum, I wanted to share the script I was able to come up with.

The hard part (decoding the obfuscated text from the database into human-readable format) came from a post by “marklee” on a Business Objects board (his post is about 1/4 of the way down):

http://www.forumtopics.com/busobj/viewtopic.php?t=124272

His query was also written for Oracle, so I converted it to one that would run against SQL Server, and then I added some additional content types based on what we had in our database, and then attached a few additional rollups (like showing the root folder, building out the full path to the report, and returning the number of instances of each report that had been generated.

You can download the full script if you’d like, or you can view the contents below. Enjoy!

select 
 objectid, 
 parentid, 
 ownerid, 
 convert(datetime, REPLACE(left(lastmodifytime, 10),' ','-') + ' ' +
			replace(substring(lastmodifytime, 12, 8), ' ', ':') +
			'.' + substring(lastmodifytime, 21, 3)) as lastmodifytime, 
 upper(left(objectname,len(objectname)-2)) ObjectName, 
 typeid, 
 type, 
 convert(bit, 0) as IsInstance,
 si_cuid 
 into #results
 from 
 ( 
 SELECT 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 replace( 
 cast(objname as varchar(2000)) 
 ,'S','v') 
 ,'M','s') 
 ,'A','m') 
 ,')','a') 
 ,'+','b') 
 ,'C','n') 
 ,'-','c') 
 ,'/','d') 
 ,'O','t') 
 ,'E','o') 
 ,'1','e') 
 ,'3','f') 
 ,'G','p') 
 ,'5','g') 
 ,'7','h') 
 ,'W','x') 
 ,'U','w') 
 ,'Q','u') 
 ,'I','q') 
 ,'9','i') 
 ,':','i') 
 ,';','j') 
 ,'K','r') 
 ,'=','k') 
 ,'?','l') 
 ,'[','y') 
 ,']','z') 
 ,'!@',' ') 
 ,'B~S','&') 
 ,'!BO','.') 
 ,'B|C"','(') 
 ,'!B|D',')') 
 ,'M|Z','-') 
 ,'M}L',',') 
 ,'M|N','_') 
 ,'M}Z',':') 
 ,'!B{B','''') 
 ,'`|<','0') 
 ,'`|>','1') 
 ,'`|@','2') 
 ,'`|B','3') 
 ,'`|D','4') 
 ,'`|F','5') 
 ,'`|H','6') 
 ,'`|J','7') 
 ,'`|L','8') 
 ,'`|N','9') 
 ,'{','') 
 ,'!','') 
 ,'"','') 
 ,'@','') 
 ObjectName, 
 convert(varchar(100), case 
	 when TypeID = 1 then 'Folder' 

	 when TypeID = 2 then 'Crystal Report'
	 when TypeID = 260 then 'Web Intelligence Document'
	 when TypeID = 290 then 'Object Package' 
	 when TypeID = 265 then 'Report Instance (TXT)'
	 when TypeID = 262 then 'Webi Report' 
	 when TypeID = 314 then 'Deski Report' 

	 when TypeID = 283 then 'PDF' 
	 when TypeID = 267 then 'Text' 
	 when TypeID = 323 then 'Excel' 
	 when TypeID = 319 then 'Excel (XLS)'
	 when TypeID = 259 then 'Word (DOC)'
	 when TypeID = 279 then 'Report Instance (PDF)'

	 when TypeID = 266 then 'Universe' 
	 when TypeID = 278 then 'Publication' 
	 when TypeID = 299 then 'Connection' 
	 when TypeID = 8  then 'Shortcut' 
	 when TypeID = 20 then 'Groups' 
	 when TypeID = 13 then 'Server' 
	 when TypeID = 16 then 'BO Server' 
	 when TypeID = 21 then 'Event' 
	 when TypeID = 24 then 'License Key' 

	 when TypeID = 19 then 'User type 19' 
	 when TypeID = 18 then 'User type 18' 
	 when TypeID = 47 then 'User type 47' 
	 when TypeID = 48 then 'User type 48' 
 
	 else 'Other' 
 end) Type, 
 * 
 FROM [dbo].[CMS_InfoObjects5] 
 where typeid <> 284 -- appears to be some kind of temporary file
 ) BORepository 

create clustered index #idx_c_results on #results (objectid)
create index #idx_results_parentid on #results (parentid)
create index #idx_results_ownerid on #results (ownerid)

-- Mark the ones that are instances of another object (children of non-folder)
update r
   set r.IsInstance = 1
 from #results r
join #results r2
  on r.ParentID = r2.ObjectID
where r2.TypeID <> 1
  and r.TypeID <> 1




-- Define all the levels of the objects and add them to a lookup table
DECLARE @ObjectLevel INT = 0

select 23 as objectid, 0 as parentid, 0 as ownerid, GETDATE() as lastmodifytime,
		convert(varchar(100), '$') as objectname, convert(int, null) as ChildInstances, convert(datetime, null) as LastInstanceTime,
		1 as typeid, convert(varchar(100), 'Root Folder') as type, @ObjectLevel as objectlevel 
into #objects

while @@rowcount > 0
begin
	;with p as (select parentid, count(*) as instances, max(lastmodifytime) as LastInstanceTime from #results group by parentid)
	update #objects
	   set ChildInstances = p.instances,
		   LastInstanceTime = p.LastInstanceTime
	  from #objects o
	  join p
	    on o.objectid = p.ParentID
	 where o.ChildInstances is null
	   and o.typeid <> 1
	
	SET @ObjectLevel = @ObjectLevel + 1

	insert into #objects
	select r.objectid, r.parentid, r.ownerid, r.lastmodifytime, r.objectname, null, null, r.typeid, r.type, @ObjectLevel as objectlevel
	from #results r
	join #objects o
      on r.parentid = o.objectid
	 and o.typeid = 1
	left
	join #objects o2
	  on r.objectid = o2.objectid
   where o2.objectid is null

end


-- Set up the object paths
select objectid, parentid, lastmodifytime, convert(varchar(4000), '') as ObjectPath,
		objectname, childinstances, lastinstancetime, typeid, type
  into #objectswithpath
  from #objects
 where typeid <> 1

while @@rowcount > 0
BEGIN
	update #objectswithpath
	   set parentid = o.parentid,
			objectpath = o.objectname + '\' + objectpath
	  from #objectswithpath owp
	  join #objects o
	    on owp.parentid = o.objectid
END

select objectpath, objectname, lastmodifytime, ChildInstances, LastInstanceTime, type
 from #objectswithpath
order by ObjectPath, objectname


--select * from #results
--select typeid, type, count(*) from #objects group by typeid, type order by count(*) DESC

/*
select type, case when isnull(ChildInstances,0) = 0 then 0 else 1 end, count(*), max(LastInstanceTime) --objectpath, objectname, lastmodifytime, ChildInstances, LastInstanceTime, type
 from #objectswithpath
 where ObjectPath not like '$\REPORTS OFF LINE\%'
group by type, case when isnull(ChildInstances,0) = 0 then 0 else 1 end
order by type, case when isnull(ChildInstances,0) = 0 then 0 else 1 end
*/

--select * from #objectswithpath where type = 'Word (DOC)'


drop table #objectswithpath
drop table #objects
drop table #results

SQL Agent job fails with QUOTED_INDENTIFIER error

If you have a SQL Agent job that’s failing, but the query succeeds when you run it manually in SQL Management Studio, it’s possibly a difference between the SQL Server default connection settings and those SSMS uses as the default. In my case, I was inserting from a stored procedure and received the following error:

[SQLSTATE 42000] (Error 1934) INSERT failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

To view the default connection options for your SQL Server instance, right-click on your instance in SSMS and select “Properties”, and then browse to “Connections” – you’ll see something like the following list (on my server, none of these are enabled by default for new connections):

Server Defaults

To compare it to the default settings for your installation of SSMS, click on “Tools” and then “Options…”, and then browse to “Query Execution” -> “SQL Server” and view the settings under both “Advanced” and ANSI” (In my case, SSMS was enabling a number of settings by default for my connections that SQL Agent jobs wouldn’t have enabled):

SSMS Results ANSI

SSMS Results Advanced

In my case, I just added an explicit “SET QUOTED_IDENTIFIER ON” to the beginning of the script in my SQL Agent job and it resolved the error message. Hope this helps you too!