Author Archives: Amit Banerjee

Is my RESTORE operation complete?

Posted on by

When you perform a database restore operation using Management Studio Object Explorer, you might be baffled as to why the confirmation pop-up window doesn’t pop-up saying that the database restore operation is complete.

image

This is because the RESTORE operation in SQL Server can be divided into two stages if you are having a high level discussion and don’t want to get into the intricacies:

1. Copy the information from the backup media and write them into the physical files on the Windows File System

2. Perform recovery on the database to bring it online (provided in the OPTIONS tab you have selected the first radio button under the Recovery State)

The highlighted counter shows you the progress of the first stage only. The second stage doesn’t have a progress counter associated with it. If you look at the percent_complete column value in the sys.dm_exec_requests DMV.

So, if your database restore screen is showing 100% completed status and the SQL Server Errorlog is not reporting the following for the database being restored:

2010-03-13 02:56:22.650 spid61       Starting up database ‘distribution_new’.
2010-03-13 02:56:23.140 Backup       Database was restored: Database: distribution_new, creation date(time): 2009/08/04(01:37:56), first LSN: 865:804:70, last LSN: 865:833:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\distribution.bak’}). Informational message. No user action required.

Then you are phase 2 of the restore provided that session performing the database restore is not experiencing blocking.

Now for the most interesting part, Why is this happening i.e. the long recovery? One of the reasons is documented here.

How to change the TCP/IP port for a SQL Server instance

Posted on by

I wrote this script sometime back. I thought it would be a good idea to share this script. The background for writing this script was there were a bunch of SQL Server installations in the environment with the same instance names but on different boxes. The installations were pushed through programmatically and company policy was set in place which needed the TCP/IP port to be changed to a different port other than the default port i.e. 1433. The DBA obviously wouldn’t be too happy sitting and using SQL Server Configuration Manager on multiple servers doing the same monotonous task. The ask was to write a script which could be pushed to all the servers so that the port on which the SQL instance was listening on was modified programmatically.

This script changes the PORT number for a named instance of SQL Server 2008 called KATMAI to 1434. The namespace used here are:

SQL Server 2005: \root\Microsoft\SqlServer\ComputerManagement

SQL Server 2008: \root\Microsoft\SqlServer\ComputerManagement10

So if you wanted to use the script for SQL Server 2005, you would need to change the namespace to the one for SQL Server 2005. The other variables that come into play for this script are the Instance Name and the Port number:

‘Specify the instance name if any. Default is MSSQLSERVER.
‘Assumption is that we are running this for the default instance.
‘For a named instance, only provide the instance name. Eg: For a named instance LABDC\INST1, the parameter below would be strInstance = "INST1"
‘For a cluster, we need to run this script on the active node.

strInstance = "KATMAI"

‘Specify the new PORT

strPort = "1434"

So, you would have to modify the above variables for the correct Instance Name and Port number.

The script then makes use of the ServerNetworkProtocolProperty class methods to set the TCP port value.

If you need to extend this script, you can do so by creating an Array for the list of Servers in your environment and running the entire code in the script in another WHILE loop.

Another customized implementation could be if you want to change the port number for multiple instances with incremental values of Port Numbers. This can be done by querying the same namespace for getting the SQL instances on the box using SqlService. A sample WMI script is provided in the blog post below.

HOW TO DETECT SQL SERVER INSTANCES / FEATURES INSTALLED ON A MACHINE

Once you have the Instance Names, then you can again create a loop with the Port Number variable being incremented and execute the function to set the TCP/IP Port number.

Download the script from here

Reference:

How to- Configure a Server to Listen on a Specific TCP Port

Happy Automation!! 🙂

March Architect Innovation Cafe Webcasts-Register Today!

Posted on by

 

image

March 25, 2010 at 1:00pm – 2:00pm EST

Title: Extending Your Sites Reach with IE8 Add On Features

Abstract Today’s users are getting more sophisticated and they expect more features from the sites and services they use. In this session learn about how to implement low effort, high value add-ons that expand your sites reach and bring value to your customer base .

Link to Register: https://swrt.worktankseattle.com/webcast/4240/preview.aspx

Presenter:
Jim Cirone
Architect Evangelist, Microsoft
Jim Cirone is an Architect Evangelist with Microsoft’s Developer and Platform Evangelism team. He joined the DPE team 3 years ago after spending 10 years in Microsoft’s services group architecting and delivering complex solutions. In his current role he is focused on new and emerging web technologies.

March 26, 2010 at 1:00pm – 2:00pm EST

Title: Windows Azure Design Patterns

Abstract: One of the challenges in adopting a new platform is finding usable design patterns that work for developing effective solutions. The Catch-22 is that design patterns are discovered and not invented. Nevertheless it is important to have some guidance on what design patterns make sense early in the game.

This webcast attacks the problem through a set of application scenario contexts, Azure features and solution examples. It is unique in its approach and the fact that it includes the use of features from all components of the Windows Azure Platform including the Windows Azure OS, Windows Azure AppFabric and SQL Azure. In this webcast you will learn about the components of the Windows Azure Platform that can be used to solve specific business problems.

Link to Register: https://swrt.worktankseattle.com/webcast/4241/preview.aspx

Presenter:
Bill Zack
Architect Evangelist, Microsoft

Bill Zack is an Architect Evangelist with Microsoft. He comes to this role after serving as a Solutions Architect in the Financial Services Unit of Microsoft Consulting Services. His experience includes developing, supporting and evangelizing .NET/SOA based frameworks used to jump-start development projects for financial services companies.

Stay Connected:

clip_image002 clip_image004clip_image006

How to remove unwanted Quotation Marks while importing a data file

Posted on by

I have had multiple questions on how users wanted to remove the quotation marks while importing the data into a SQL Server database table while using BCP or BULK INSERT. Last month I worked on a similar issue where the need was to strip out the quotation marks for all the data columns present in the .DAT file. Read my post on SQLServerFAQ to find out how this can be accomplished using BCP, BULK INSERT or if you like using UI, the Export/Import Wizard.

How to identify the SQL Server version from a Profiler Trace

Posted on by

You use certain little tips and tricks with the SQL Server tools which are not documented anywhere. This is one of them. I needed to find out the version of a SQL Server instance from which the Profiler Trace was captured. I opened the Profiler trace using SQL Profiler 2008. I clicked on the Trace properties and lo behold I had the SQL Server instance name and version.

image

I got the SQL Server release, version and the instance name (Trace provider name).