SQL: How to test a SQL Backup

When reviewing disaster recovery processes I often get asked how a verify / test that a SQL Backup is “good”. Restoring the backup file is a solid and reliable way to verify the backup file, however it may not always be practical due to space and/or time restrictions.

During the backup task, you can check the “Verify backup when finished” reliability option to verify the backup set is complete and readable.

After the backup file has been create you can execute the following commands:

RESTORE VERIFYONLY FROM DISK='<your backup file name.bak>'
This option verifies the backup set is complete and readable, however it does not validate the data structure.

RESTORE HEADERONLY FROM DISK='<your backup file name.bak>'
Retrieve the header information of the backup set.

RESTORE FILELISTONLY FROM DISK='<your backup file name.bak>'
Returns a list of files in the backup set.

There are options available to test the integrity of the backup file, however you should periodically test your disaster recovery / business continuity plan. It is important to verify that the contents of your “backup” contains the pertinent data for a proper recovery.

SQL: Microsoft Dynamics NAV "kill" an idle session

One issue with applications that use a concurrent license model is the dreaded “idle user”. This is the user that had gone to lunch, to a meeting or even home for the and didn’t “logout” of the application. Under the concurrent license model this user does take up a user session, which prevents other users from accessing the application if the maximum number of seats has been reached or lead to the purchase of additional and unnecessary user licenses due to the perception of not enough seats.

Microsoft Dynamics NAV is an example of application that uses a concurrent license model and is also one application that people often ask “How can I disconnect an idle user?” While there isn’t a magic “kill idle” user setting there are a several options available. A few of these options are:

  • If the users are using Remote Desktop you can set an idle timeout to force a disconnect or manually force a disconnect of idle remote users
  • Manually remove idle users from the Microsoft Dynamics NAV session list
  • Kill a user’s SQL Server connection

Let’s jump into the last option that is listed above. In Microsoft SQL server there isn’t an “idle time” value defined for a user, however there is a last_batch value that indicates the last time the users session sent an execution to the SQL server. With that information we can easily create (and schedule) a script that can compare the current date and time to the last_batch value to see how long a user has been “idle” and kill their session if it is over a certain amount of time (script should be adjusted for your implementation):

set nocount on;
set ansi_padding on;
set ansi_nulls on;
set quoted_identifier on;

-- declare variables.
declare @spid as varchar(20)
declare @loginname as varchar(255)
declare @kill as int
declare @cmd as varchar(max)

-- set the variable @kill to 1 to actually perform the “kill or set it to 0 to see a list of users that
-- would be killed
set @kill = 0

declare users insensitive scroll cursor
            -- NAV Db Name    
            (db_name(dbid) = N'CRONUS')
            and (program_name = N'Microsoft Dynamics NAV Classic client')
            -- number of seconds idle -- remember this does not mean there isn't a long job running
            -- 3600 is 1 hour
            and (datediff(ss, last_batch, getdate()) > 3600)

open users
while (1=1)
            fetch next from users into @spid, @loginname;
            if @@fetch_status <> 0 break;
            set @cmd = 'kill ' + @spid
            if @kill = 1 
              exec (@cmd)
              print @cmd + N' ' + @loginname;

close users;
deallocate users;



One thing to point out is that a user may be idle because their session is performing a lengthy operation and they have not had any session activity while they are awaiting results for their command. There tends to be some disconnect and excitement when someone is in the middle of some operation and their session is killed.

It should be evident that there is a risk when abnormally terminating a user’s session. There is the risk of data loss or corruption so be certain to understand the environment that you’re in and the implications of terminating a user’s session in a not so elegant way (i.e. properly logging off).

SQL: Check if a File Exists

There may be a case where a SQL table contains a field that references a filename that is stored on the file system. The file could be an image (for example in the case of a web application that displays images) or another document that is linked from an application. To avoid errors it may be necessary to verify the existence of the file (i.e. the file exists). Fortunately, this is easily done with the xp_fileexist command. The xp_fileexist command returns an integer result that indicates if the file exists. The usage of the command is:
EXECUTE xp_fileexist [, OUTPUT]
The following script shows and example of looping through a set of records and validating, with xp_fileexist, that a file exists. If it doesn’t exist it updates the field with another value (if you are referencing images it could be a default image filename).
set nocount on;
set ansi_padding on;
set ansi_nulls on;
set quoted_identifier on;

-- declare variables.
declare @item as varchar(255)
declare @filename as varchar(255)
declare @filepath as varchar(255)
declare @cmd as varchar(1024)
declare @result as int

-- declare a cursor for storing the records in the table.
declare items insensitive scroll cursor
		,[File Name]
		,[File Path]
	from  [Table1]
	order by 
		[File Name]

-- open the cursor.
open items

-- loop through all the indexes.
while (1=1)
		fetch next from items into @item, @filename, @filepath;
		if @@fetch_status <> 0 break;
		set @filepath = @filepath + @filename
		exec master.dbo.xp_fileexist @filepath, @result OUTPUT
		if (@result = 0) -- image does not exist
				--print (@result)			
				-- Update the record to point to a generic image
				set @cmd = 'UPDATE [Table1] SET [File Name] = ''NA.jpg'' WHERE [Item] = ''' + @item + ''''
				-- print (@cmd)
				exec (@cmd)

-- close cursor
close items;
deallocate items;


SQL: SSIS, a Flat File and the _x003C_none_x003E__x003C_none_x003E_ Text Qualifier

I had recently experienced a strange issue with a SSIS Package.  This was basic package that copied data from a SQL Server table to a text file. This SSIS package was deployed in production for several months without issue, until – a minor change needed to be made.  The change was insignificant; the location (path) of the output file (a config file is usually used but in this case I wanted to edit the package) needed to be changed.   I had changed the path, built and deployed the package. Soon after deploying the package I received word that the file created was not correct.

I reviewed the output file and noticed that each record (line) ended with strange hex looking data: _x003C_none_x003E__x003C_none_x003E_

_x003C_none_x003E__x003C_none_x003E_ text qualifier

I scratched my head for a bit, reviewed the package’s source and destination connections and could not find where this odd _x003C_none_x003E_ data was coming from. I decided to view the package with a different editor and realized that somehow the Text Qualifier property for the Flat File Connection Manager was set to this “strange” sequence.

TextQualifier Property _x003C_none_x003E__x003C_none_x003E_

I removed the value, built and deployed the package and everything was back to normal - well as normal as it could be...

SQL: SQLServerAgent Error: Job xxx does not exist in the job cache


I recently encountered the error SQLServerAgent Error: Job xxx does not exist in the job cache (where xxx is the internal id number). This error was a bit misleading at first as I confirmed the job entry did exist. The cause of the error was that the SQLAgent service account didn’t have the necessary permissions to read the job data. Once the permissions were properly setup the job executed. 

Reading Stuff

Information in this document subject to change without notice.
All Software source code published is for demonstration and knowledge sharing purposes only. The Code is supplied "as is" without warranty as to result, performance or merchantability. Use at your own risk.
The opinions expressed herein are the opinions of the author and do not reflect those of any other entity.