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>'
http://msdn.microsoft.com/en-us/library/ms188902.aspx
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>'
http://msdn.microsoft.com/en-us/library/ms178536.aspx
Retrieve the header information of the backup set.

RESTORE FILELISTONLY FROM DISK='<your backup file name.bak>'
http://msdn.microsoft.com/en-us/library/ms173778.aspx
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
for    
      select
            spid
            ,loginame
      from
            sys.sysprocesses
      where
            -- 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)
      begin
            fetch next from users into @spid, @loginname;
            if @@fetch_status <> 0 break;
            
            set @cmd = 'kill ' + @spid
            if @kill = 1 
              exec (@cmd)
            else
              print @cmd + N' ' + @loginname;
      end

close users;
deallocate users;

go

 

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).

Microsoft Dynamics NAV: RTC and 2013 What happened to Ctrl+F8 a.k.a. Zoom?

A useful and widely used command in the Microsoft Dynamics NAV classic client in the good ole’ Ctrl+F8 a.k.a. Zoom. Most Microsoft Dynamics NAV forms only display some of the fields contained within a table. Using the Zoom (Ctrl+F8) command you can see all of the fields and the data contained within a table.

For those that have transitioned to the Role Tailored Client (RTC) or 2013 and have relied on the Zoom command you might have noticed that Ctrl+F8 no longer works.

So, what happened to it?

This is a question that I am often asked, and fortunately Zoom didn’t disappear; It was only moved. You can access the “Zoom” feature in Microsoft Dynamics NAV and the RTC by pressing Ctrl+Alt+F1 or selecting Help -> About this Page from the menu while on the desired record.

Microsoft Dynamics NAV: Send XML Document via HTTP Post with Basic Auth

In a previous post I had discussed sending an XML document via HTTP Post from Microsoft Dynamics NAV.   In that example user authentication was not part of the communication.  If you are required to post your information using BASIC AUTH there are two ways to handle it. 

Using the code from the previous post as an example:
Add the username and password parameter to the open method:


XMLHTTP.open('POST', WebAddress, FALSE, username, password);

Or add a  Adds custom HTTP headers to the request:

XMLHTTP.setRequestHeader('Authorization', 'Basic d2hvb3NlZXM6dGhpcw==');

Set the Authorization to a value of Basic + the Base64Encoded value of the Username:Password string.

Microsoft Dynamics NAV: Permission Error SET IDENTITY_INSERT

In your travel as a Dynamics NAV professional you make come across a permission error on a table like this:

“1088,“42000”,[Microsoft][ODBC SQL Server Driver][SQL Server] Cannot find the object … because it does not exist or you do not have permissions. SQL: SET IDENTITY_INSERT…”

Error SQL SET INDENTITY_INSERT

Initially, this may seem easy enough to correct by ensuring that the user has the necessary NAV security permissions set for the table listed. However, after setting the NAV security permission you will realize that the user still receives the error. The permission error is not generated from NAV, rather it is generated from SQL. The error is due to the user not having the permission to alter the structure of the table. Before you bang your head and do something silly like give the user permission to the object let’s take a look the cause the error.

Error SQL SET INDENTITY_INSERT

The SET IDENTITY_INSERT permission error occurs when a user tries to enter data in a field that is set to be a SQL Identity Column (a column is set to be an identity column when the AutoIncrement property of the field is set to yes in the NAV table designer; generally on “Entry No.” fields). An identity column is a column that has its value generated by the database (the identity column is often used as a primary key; however it is not required to be the primary key). Although, the identity column has a value generated by the database when a record is inserted, it is possible to edit the value if you have permission to alter the database object; the database internally tracks the last value entered to generate the next value.

Error SQL SET INDENTITY_INSERT

You should review any code that modifies or inserts records into the table is identified in the error. Verify that the any code is not trying to insert a value in the AutoIncrementing field. I have seen several cases where code was written to increment an “Entry No.” field that was set to AutoIncrement.

Error SQL SET INDENTITY_INSERT

You can properly correct the issue by setting the AutoIncrement property of the table to No or altering the code to not enter a value in the field. Obviously you should not edit any code or table design unless you know what you are doing and properly test the changes in a non-production environment.

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.