Writing scripts is often an iterative process. I’ll create something that works and use it for a couple months, then run into a case I didn’t think of or that behaves a little differently, and I’ll have to modify the script to handle it. Sometime later, the same thing will happen and I’ll make more changes. Over time, you end up with a fairly robust script that can handle quite a few different situations. This is another reason why I like to have consistent scripts across all my servers. If a problem crops up on one server and the fix has been implemented on all the servers, then you never have to worry about the problem showing up again.

I wrote previously about a step I added to my differential and transaction log backup routines to handle new databases that might have been created after the last full backup job ran. If a full backup of a database has not been made, the jobs that create a differential backup or transaction log backup will fail. My solution to this problem was to get a list of the databases on the server and compare that to entries in the MSDB backupset table. If there was no entry indicating a full backup had been made for a particular database, the script made a full backup. This then allowed the next step in the job, which creates either a differential backup or transaction log backup, to complete successfully. The script for this was given in that post.

This script works for brand new databases, but my differential and transaction log backups were still failing with the “no full backup” error in cases where I moved a database from one server to another.

I did some digging and discovered that the act of restoring a database will create an entry in the backupset table. Given that there are tables in MSDB that store information about restores, namely, restorefile, restorefilegroup, and restorehistory, you would think that a restore operation would be recorded in those tables only, and not also in backupset, but that is not so. Books Online says this about the backupset table:

Contains a row for each backup set. A backup set contains the backup from a single, successful backup operation. RESTORE, RESTORE FILELISTONLY, RESTORE HEADERONLY, and RESTORE VERIFYONLY statements operate on a single backup set within the media set on the specified backup device or devices.

From that, it’s not clear to me that a restore will put data into this table. In retrospect, I can see that yes, it does sort of imply that, but by no means does that jump out at you on the first read. But a simple test confirmed it. I made a test database on Server A, then restored it on Server B. Here is a query on Server B before the restore, showing there is no entry in the backupset table for my test database:

Then I did the restore and ran the same query. Here is the result:

This is why my script was not catching databases that had been moved and creating a full backup for them. To fix this, we can use the server_name column. Notice that the entry in the backupset table for the restored database has the server name of the original database – “EISSRS” is “Server A” in my example. So by modifying our script to check that there is a full back up entry with the same server name as the current server, we can handle cases of moved databases. I did this by adding to the WHERE clause a condition that the server_name column equal the global variable @@servername. The modified code is:

DECLARE @InitialBackupPath varchar(1000)
DECLARE @dbname varchar(256)
DECLARE @BackupPath varchar(1260)

SET @InitialBackupPath = '<insert your backup directory here\>'

DECLARE DatabaseCursor CURSOR FOR
	SELECT name FROM sys.databases WHERE name <> 'tempdb' AND source_database_id is NULL AND State_Desc = 'ONLINE'

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @dbname
WHILE @@FETCH_STATUS = 0
begin
	IF NOT EXISTS (	SELECT	1
				FROM	msdb.dbo.backupset
				WHERE	type = 'D'
						and database_name = @dbname
						and server_name = @@servername)
	begin
		SET @BackupPath = @InitialBackupPath + @dbname + '.bak'
		BACKUP DATABASE @dbName TO DISK=@BackupPath
	end

	FETCH NEXT FROM DatabaseCursor INTO @dbname
end

CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

Note that you may still run into an issue with this. On one of my servers, @@servername returned a null value. That particular server was set up before I started with this company, so I don’t know how that came about. However, it can be fixed by following the steps given here, which simply use the sp_dropserver and sp_addserver stored procedures to update SQL’s system tables with the local server name. (A SQL Server service restart will be required if you have to do this.) Once you have verified that @@servername returns a valid value, you can implement this script.

Speaking of iterative script writing, I snuck in another change to the above script. I set up mirroring of a couple databases recently. On the mirror server, these databases are in the RESTORING state and therefore, can’t be backed up. So I had to change the WHERE clause of the DatabaseCursor to include the condition AND State_Desc = ‘ONLINE’ (line 8). This will exclude the mirror databases (and other off-line databases) from the check.