SSMS: How to restore differential backups

There are two ways to restore a differential backup in SQL Server:

• Directly use TSQL statements as described in this MSDN page
• Use SQL Server Management Studio user interface as described here.

If you are using SQL Management Studio to restore differential backups, and you have restored full backups several time using SSMS, but this is your first time to restore a differential backup then you are likely to encounter the following error:

Restore failed for Server 'servername'. (Microsoft.SqlServer.SmoExtended)
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The log or differential backup cannot be restored because no files are ready to rollforward. (Microsoft.SqlServer.Smo)

This is because you tried to restore a differential backup on an available/operational/functional/running database which is not allowed.

In order to restore a differential backup, you will first need to restore the last full backup with NO RECOVERY option. So, in SSMS you need to select the appropriate full-backup and choose Restore With NoRecovery option from the Options page as depicted in the following screenshot.

Once restored, the database will be shown in the Object Explorer as Restoring.

Notice that the database is non-available/non-functional at this time and is waiting for a differential backup to be applied. Now, restore the appropriate differential backup and choose Restore With Recovery from the Options page:

That’s it. You have successfully restored a differential backup.

A Final Note:

Note that differential backups are cumulative and each differential backup contain changes since the last full backup, not the last differential backup. So if you have a full backup of 2009-01-01 and have differential backups for each day, and you want to restore your database to 2009-01-10, then you just need to restore the full backup (with no recovery) of 2009-01-01 followed by the differential backup of 2009-01-10. For more information, read the following MSDN articles from SQL Server books online:

• Differential Database Backups
• How Differential Backups Work

C#: Executing batch T-SQL Scripts containing GO statements

At times, we developers need to run SQL scripts from our .NET applications, in, say Installer Applications. This is not always easy since large SQL scripts typically contain a GO statement to separate individual batches and our ADO.NET classes under System.Data namespace do not know how to handle it. The reason is that the “GO” statement is not a native T-SQL statement but rather used by SQL Server Management Studio to terminate the batches it is sending to the server. To tackle this situation, we have the following choices:

1. Split the script on “GO” command into smaller scripts and execute those individual scripts

A very primitive solution would be to split the script on “GO” text and run the individual sub-scripts in sequence. The problem is how to get a robust split mechanism. Generally, a line break before and after the GO works fine. Here’s how to do this:

//get the script
string scriptText = GetScript();

//split the script on "GO" commands
string[] splitter = new string[] { "\r\nGO\r\n" };
string[] commandTexts = scriptText.Split(splitter,
  StringSplitOptions.RemoveEmptyEntries);
foreach (string commandText in commandTexts)
{
  //execute commandText
}

The above code can produce unnecessary splitting in some situations, thus creating an incorrect SQL Command. A more better approach would be to use Regular Expressions. Again, the problem is how to create a pattern that is robust enough to tackle all sort of scripts. Lets look into the second solution now.

2. Use the Server class from SQL Server Management Objects (SMO)

For this, we need to add the following references in our project:

• Microsoft.SqlServer.Smo
• Microsoft.SqlServer.ConnectionInfo
• Microsoft.SqlServer.Management.Sdk.Sfc

After that, we can simply execute the entire script (with all the “GO” statements) using the code below:

string connectionString, scriptText;
SqlConnection sqlConnection = new SqlConnection(connectionString);
ServerConnection svrConnection = new ServerConnection(sqlConnection);
Server server = new Server(svrConnection);
server.ConnectionContext.ExecuteNonQuery(scriptText);

This is a fairly generic and robust solution and the great thing is that it does not require any change in our original script. Hooray!!!

SSIS: Comparing two versions of same package

SQL Server Integration Services yields a very bitter experience when comparing two versions of a package to see the differences. The problem is that the package XML is arbitrarily reordered when we try to save a package in the Visual Studio designer. Well, luckily, there’s a free and very nice solution to this problem: BIDS helper, a Visual Studio add-on that leverages BI development using SQL Server 2005/2008. SmartDiff is a part of BIDs helper and it compares two packages after pre-processing the package definition files and producing a uniform layout for comparison.

To demonstrate, let me show the result of two versions of one of my SSIS packages. I did not modify anything and just saved the same package twice. Here’s a summary for an ordinary diff that shows how the VS designer messes up the two versions:

Using SmartDiff, here’s the result:

Looks great, only two differences. Here’s the actual output from SmartDiff: Click to enlarge the image:

That’s really cool. BIDs helper is an add-on every BI developer must have. It contains many other features other than SmartDiff. Have a try here.

SSRS: Another way to use DataSet fields in page header/footer

I previously blogged about a method to refer to DataSet fields in page header/footer here. This time, I will present another way to achieve this.

Although SSRS does not allow us to use DataSet fields in page headers but it allows us to refer to report items. So we could place a textbox (that takes its value from a DataSet field) anywhere in our report’s body and set its Hidden property to true. Then, we could easily refer to that textbox in the page header with an expression like: =ReportItems!TextBox1.Value and we are done. Note that the textbox that is being referred should be present on every page, or otherwise the header will print empty value.

SSRS: Using DataSet fields in page header/footer

SQL Server Reporting Services does not allow you to refer to DataSet fields in page header/footer. Sometimes, you really need this feature. For example, You are displaying some data related to a Project and the Project is to be selected from the user via a report parameter. Now typically, you want to display the Project Name in the header of every page. Here’s a hack on accomplishing this:

• Create a dataset dsProjectName in which you select the desired information (in this case, the ProjectName column) of the selected Project. A simple query for this could be:
  Select ProjectName from Project where ProjectID = @ProjectID
• Create a new internal parameter ProjectName, set its default value to get the data from dataset dsProjectName and then set the value field to ProjectName. Also, make sure this new parameter is below the ProjectID parameter to allow cascading.

You can now use this new ProjectName paramater anywhere in your page header textbox expressions.

Nulls can be evil

Nulls can produce weird results when used with comparison operators. Few days back, I was debugging a query like the one below.

Select ....,
  Case When myColumn = 'SomeValue1' Then 'True' Else 'False' End As CaseOutput1,
  Case When myColumn = 'SomeValue2' Then 'True' Else 'False' End As CaseOutput2,
  Case When myColumn != 'SomeValue1' and myColumn != 'SomeValue2'
    Then 'True' Else 'False' End As CaseOutput3,
...

Looking at the case statements, we can infer that at least one of the case statements will always output a True, but this was not the case. After much effort, I found that myColumn resulted in null due to a left join and so all the case statements evaluated to False. Getting to the cause of problem wasn’t an easy task since the query was quite complex with multiple joins and several case statements.