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!

Refreshing changed .NET SQL CLR assemblies after patching/updates

After applying some Windows updates to one of my servers, I started getting the following error when I ran a customer .NET SQL-CLR stored proc:

Server: Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate ‘somemethodyourecalling’:

System.IO.FileLoadException: Could not load file or assembly ‘System.Drawing, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050)

I’d imported some additional assemblies into SQL Server for use in SQL CLR mapped stored procedures, and the Windows updates had included a service pack for .NET, which changed some of the assemblies. Now the version I’d linked to SQL Server no longer existed on disk, and SQL couldn’t load it – that made sense, and is documented pretty clearly in this MS Support article: http://support.microsoft.com/kb/949080

However, I had dozens of servers with SQL CLR components enabled, and hundreds of different assemblies loaded across them all, and not always the same in each server, so a standard update script wouldn’t work to refresh all the changed assemblies (the MS Support link provides a list of the standard ones that cause that error, but if you’ve got custom assemblies loaded, or you’ve loaded an assembly that’s not specifically cleared for SQL CLR, then it’s not on the list either). To deal with this, I wrote a script that fetches the assembly list for a database and attempts to refresh every one of them from their disk location. If they haven’t changed, the update attempt will fail with a specific error message about the MVID, and there’s no change for that assembly.

Also, I’ve commented out the line that restricts it to just framework assemblies (System.* and Microsoft.*), but you can uncomment that line if you’d like to restrict the refresh from attempting to reload your custom assemblies as well.

DECLARE @AssemblyName VARCHAR(255),
	    @AssemblyLocation VARCHAR(255),
	    @AlterAssemblyCommand NVARCHAR(1024),
	    @DotNetFolder VARCHAR(100)

   SET @DotNetFolder = 'C:\Windows\Microsoft.NET\Framework\v2.0.50727'

CREATE TABLE #Results (
		AssemblyName VARCHAR(255),
		AssemblyLocation VARCHAR(255),
		AlterAssemblyCommand NVARCHAR(1024),
		Results VARCHAR(1024)
)

select sa.name as AssemblyName,
		saf.name as Assemblylocation,
		case when charindex('', saf.name) = 0
			then 'ALTER ASSEMBLY [' + sa.name + '] FROM ''' + @DotNetFolder
			else 'ALTER ASSEMBLY [' + sa.name + '] FROM '''
		end + saf.name + (case right(saf.name, 4) when '.dll' then '' else '.dll' end) + ''''
		as AlterAssemblyCommand
INTO #Refresh
from sys.assemblies sa
join sys.assembly_files saf
  on sa.assembly_id = saf.assembly_id
where sa.name <> ('Microsoft.SqlServer.Types')
  --and (sa.name like 'System.%' or sa.name like 'microsoft.%')

DECLARE Commands CURSOR FAST_FORWARD FOR
SELECT AssemblyName,
	   AssemblyLocation,
	   AlterAssemblyCommand
  FROM #Refresh

OPEN Commands

FETCH NEXT FROM Commands
INTO @AssemblyName,
	   @AssemblyLocation,
	   @AlterAssemblyCommand

WHILE @@FETCH_STATUS = 0
BEGIN

	BEGIN TRY
		exec sp_executesql @AlterAssemblyCommand

		insert into #Results
		SELECT @AssemblyName,
				@AssemblyLocation,
				@AlterAssemblyCommand,
				'Assembly refreshed successfully'

	END TRY
	BEGIN CATCH

		insert into #Results
		SELECT @AssemblyName,
				@AssemblyLocation,
				@AlterAssemblyCommand,
				CASE ERROR_NUMBER()
					WHEN 6285 THEN 'No update necessary (MVID match)'
					WHEN 6501 THEN 'Physical assembly not found at specified location (SQL Error 6501)'
					ELSE ERROR_MESSAGE() + ' (SQL Error ' + convert(varchar(10), ERROR_NUMBER()) + ')'
				END

	END CATCH

	FETCH NEXT FROM Commands
	INTO @AssemblyName,
		   @AssemblyLocation,
		   @AlterAssemblyCommand

END

CLOSE Commands
DEALLOCATE Commands

SELECT * FROM #Results

drop table #refresh
drop table #Results

While troubleshooting the error, I came across this as well – I don’t have a SQL 2012 server handy to check with, it looks like this problem might be resolved with a reboot in SQL 2012:

http://msdn.microsoft.com/en-us/library/hh479773.aspx

I’m not sure if that’s the case, but it would make things easier. Also, it would be nice if there was some kind of “ALTER ASSEMBLY [SomeAssembly] REFRESH” command to address this problem, but maybe in a future version.

Additionally, this error can be caused if the signing of an assembly has changed, but not necessarily the signature, but just reloading it from disk won’t work because the method that SQL Server uses to determine if there’s been a change is the MVID, and that doesn’t change unless there’s been a code change of some kind (see the Connect bug here). In those cases, you’ll actually need to drop and recreate the assembly, including any supporting objects that rely on that assembly.

Making sense of SQL Server 2012 MCP Certification paths

I earned my MCDBA on SQL 2000 and then skipped the certification tests for 2005 and only took one for 2008. Now that I’m thinking about taking some 2012 tests to get my certifications updated, I find myself confused – no matter how many times I check the Microsoft Certification page for SQL Server, I find myself still a bit unclear about how things upgrade from 2008 to 2012, and if it makes sense to squeeze in a few SQL 2008 tests while I still can (they retire on July 31st, 2013).

To help make sense of them, I made a few cheat sheets that I’m hoping will clarify what tests are needed for which certifications (including which ones apply towards multiple certifications, so you get the biggest “bang for your buck”, in a way).

In these charts, the certifications are on the left side and the individual tests are across the top – the boxes marked in the chart correspond to the tests required to earn a particular certification. Also, you can click on each chart to get a slightly larger/clearer version.

SQL 2008 Certifications:
If you want to earn the “MCSA: SQL 2008”, you’d find the certification on the left (it’s the last row) and see which boxes are shaded (exams 70-432 and 70-448).

You may also notice that some of the certifications are colored – that’s to help make sense of the SQL 2012 upgrade paths. Each of the colored certifications can be used as part of an upgrade to a certification in SQL 2012. In the chart below, the left set of “Exams” along the top are certifications – the boxes are colored the same as the above chart, to help make clear which certifications can be upgraded:

Upgrading certifications from SQL 2008 -> SQL 2012
Using this chart, say you want to earn your “MCSA: SQL 2012” (it’s the first row) and you already have your “MCSA: SQL 2008” (it’s the first column – green from the previous chart). To complete your certification, you’ll need to pass exams 70-457 and 70-458.

Finally, here are the same SQL 2012 certifications, but without the upgrades from SQL 2008 – in this chart, it assumes you’re starting from scratch:

SQL 2012 Certifications:
If you want to earn the same “MCSA: SQL 2012” as before, find it on the left (it’s the first line), and then you can see that it requires passing exams 70-461, 70-462, and 70-463.

Hopefully this helps sort things out a bit and make the upgrade paths a little more clear.

More information about Microsoft Certifications for SQL Server:
SQL Server certification – main page
MCSA: SQL Server (covers both SQL 2008 and 2012)
MCSE: Data Platform (new for SQL 2012)
MCSE: Business Intelligence (new for SQL 2012)

Moving a SQL Server database to another server on a schedule – without using replication

Recently, I had the need to copy a set of databases from a dozen remote servers to a central server, restore them, and have it happen automatically, with no intervention from me at all. Replication wouldn’t work for the following reasons:

  1. Many tables didn’t have primary keys, so merge replication was out (even though this was only one-way replication)
  2. The size of the databases (28GB in one instance) and the quality/speed of the WAN removed the log shipping option
  3. There’s too much activity to consider any kind of live replication

Given our restrictions, we decided to go the following route. On the remote server, we set up a batch file that did the following:

  1. Use OSQL to back up the databases in question to a folder
  2. Run 7Zip from the command line to compress the backups into separate archives. For each auto-attaching later, each archive had the name we wanted it attached to the remote server with (for example, Site1ProdDB was backed up to Site1ProdDB.BAK, then compressed to Site1ProdDB.7z)
  3. Delete the BAK files
  4. Archives were renamed from *.7z to *.7zz (this is important – I’ll explain why in the server part)
  5. Scripted FTP using Windows command line FTP tool to a folder on our central collection server
  6. Once the FTP was complete, rename the archives on the remote server back from *.7zz to *.7z
  7. Delete the local *.7zz files

That’s it for the client – the BAT file was scheduled as a SQL Agent job so that we could kick it off remotely from any site we wanted, or so we could set them up on a schedule. Then, we put a BAT file on the server that did the following:

  1. Check folder for files that match *.7z
  2. For each one found, do the following:
    1. Extract it to a “Staging” folder
    2. Delete the 7z file for that archive
    3. Use OSQL to restore the file from the command line
    4. Use OSQL to run a script that changes the DB owner, adds some user permissions, and generally does some housework on the database
    5. Use an SMTP tool to send a email notice that the backup has been restored
  3. Repeat step 2 for every .7z file in the folder
  4. As a second step in the SQL Agent job, run “MoveLog.bat” (included below) to finish rotating the logs – it ensures that only logs with meaningful information are kept

The server BAT process can run as often as desired – in our case, we run it every 30 minutes, so the backup will be picked up and restored as soon as it’s available. That’s where the rename from the client side comes into play: If the files were named Database.7z, then the server process would attempt to pick them up while they’re being uploaded via FTP, and shenanigans would ensue. By renaming them when they’re done uploading, they become immediately available for restoring on the server side.

As I said before, I scheduled both the client (source) and the server (restore/destination) process as SQL Agent jobs – the Windows scheduler is too cumbersome to work with remotely, and kicking them off on demand was a pain. With the SQL Agent, they can be started on demand, and then I get an email notification as soon as they’ve been successfully restored.

I’ve attached the files below, and I welcome any feedback that you have or any improvements that can be made – I’m happy to give you credit and post a new version here. Specifically, I’m interested in any feedback about how to make this process more dynamic – I know BAT scripting supports FOR EACH and wildcards, but I was unable to make them work properly with OSQL, so I’d appreciate any input there. Enjoy!

Download the ZIP archive containing the files for this post