SSMS, or SQL Server Management Studio is a wonderful tool, and those who frequent this blog will notice that I tend to try and make day to day task simpler using the SSMS. In a development environment, databases are created and backups restored often. Most developers have rights to the development instance, and sometimes we run into problems with application developers going out of their comfort zone and attempting to be “accidental DBAs”.

This true not only for software, but for the world around us as well. When we undertake transatlantic journeys, it takes a while for us to get used to the tools and technology available because it’s different. I am quite sure that most have caused the untimely death of a costly electronic device because we failed to realize that the US works on a 110V supply, while the UK on a 220-240V supply.

Similarly, not that there is anything wrong in being an “accidental DBA”, but minor product issues do cause a usability annoyance when somebody who is not familiar with the tool (SSMS) attempts to use it.

The scenario

Let’s create a test database on a given SQL Server instance such that the database has at least two data files, and one log file (you can have more, no issues).

Once the database is created, we will fire the following query to confirm the file names and file paths:

USE TestDB
GO
SELECT * FROM sys.sysfiles

Now, let’s take a full backup of the database, and move the backup to another server where another instance of Microsoft SQL Server is running. As mentioned in my previous post earlier this week, check the default location of the data files for that instance.

Now, invoke the Restore wizard to attempt a restore of the database. Select the backup file location and check the appropriate backup set in the “General” tab.

Now, navigate to the “Options” tab and notice the default file names provided as part of the restore.

Simply clicking “OK” without modifying these paths will result in an exception:

TITLE: Microsoft SQL Server Management Studio
——————————
Restore failed for Server ‘VPCW2K8DENALI’.  (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.1103.9+((SQL_PreRelease).100924-2125+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

——————————
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: File ‘E:Program FilesMicrosoft SQL ServerMSSQL11.DENALIMSSQLDATAMyTestDB.mdf’ is claimed by ‘NAV_SECONDARY'(3) and ‘NAV_DATA'(1). The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.1103.9+((SQL_PreRelease).100924-2125+)&LinkId=20476
——————————
BUTTONS:
OK
——————————

This exception is coming because we did not modify the default file names. As you can see in the screen-shot, both logical files – NAV_DATA and NAV_SECONDARY have the same physical file location & name – E:Program FilesMicrosoft SQL ServerMSSQL11.DENALIMSSQLDATAMyTestDB.mdf

Simply changing the name to the logical file names (NAV_DATA.mdf and NAV_SECONDARY.mdf) would rectify the issue and the restore will succeed.

Microsoft Connect Case #668566

I had filed a Microsoft Connect bug report here – https://connect.microsoft.com/SQLServer/feedback/details/668566#details

I am pleased to announce that the bug has been fixed by Microsoft and will be available in the next public release of Microsoft SQL Server (code named “Denali”). Thank-you, Microsoft!

Until we meet next time,

Be courteous. Drive responsibly.

Today’s post takes me back to the days when I was learning SQL Server at the engineering school about 8 years ago. I learnt SQL 7 and SQL 2000 and then as I joined my present employer, SQL 2005 came along. While SQL 7/2000 had the Enterprise Manager, SQL 2005 onwards we have the SSMS – SQL Server Management Studio. Irrespective of the tool, each of these had wizards for Creating and Restoring a database.

Whenever we create or restore a database, we can see that the SSMS fills in a default path for placing the data and log files. This path is set during the Server installation, and is normally set to %PROGRAMFILES%Microsoft SQL ServerMSSQL11.(InstanceName)MSSQLDATA on a SQL 11 (Code Named: “Denali”) CTP 01 instance (Previous releases of SQL Server also follow a similar path).

Generally, I prefer to place the data and log files on a different physical drives whenever possible. I won’t go into the details of why I do this right now, but it is important to know that for most cases, it is a recommended best practice. It becomes an annoyance to change the data and log file paths every time I need to create or restore a database. Hence, I typically change the default file paths at the server instance level.

Here’s how you can do it yourself:

1. Launch the SQL Server Management Studio
2. Open the Object Explorer by going to View –> Object Explorer and connect to the SQL Server instance under consideration
3. Right-click the instance name and click on “Properties”
4. In the server properties window, navigate out to the Database Properties tab
5. Change the Data & Log file paths under the “Database Default locations” section

Once changed, you can then see that these changes are used by default by the Create Database and Restore Database wizards. On my test instance, I don’t have multiple drives to spread the data/logs across, hence I have added the default locations as E:DatabasesData for the data files and E:DatabasesLogs for the Log Files.

Until we meet next time,

Be courteous. Drive responsibly.

A couple of months ago, I ran into a strange issue. Whenever I fired the “RECONFIGURE” statement, the SQL Server returned an error message – “Ad hoc update to system catalog is not supported.” I was able to trace this to the fact that somebody had set the “allow updates” setting on the server to 1. This setting is 0 by default on a new install of SQL Server. This setting was used in SQL Server 2000 to allow direct ad-hoc updates to system catalogs and tables. This setting is now deprecated since SQL Server 2005 and all SQL Server installations come with this set to 0 by default. Per Books On Line (read the BOL help on this option here), this setting has no effect, except that the RECONFIGURE statement will fail, just as it was doing in my case.

The root cause in my case was that it was a shared environment and because this configuration switch lies in full public view when sp_configure is run, some novice developer attempting to learn SQL Server inadvertently set the switch but forgot to reset the same. You can read about my experience here: http://beyondrelational.com/blogs/nakul/archive/2011/02/14/ad-hoc-update-to-system-catalogs-is-not-supported-when-using-reconfigure.aspx

Just recently, I ran into the problem again – this time on a friend’s test configuration. That’s what prompted me to open a case in Microsoft Connect. You can find the case filed here: https://connect.microsoft.com/SQLServer/feedback/details/662305/sp-configure-allow-updates-should-be-an-advanced-option

My belief is that if something is deprecated, and is not meant to be used – it should not be in obvious view. We don’t keep unused stuff on the front lawns – we stuff it away in the garages and therefore, this option should at least be made an advanced option.

If you agree with me, please vote for fixing this at: https://connect.microsoft.com/SQLServer/feedback/details/662305/sp-configure-allow-updates-should-be-an-advanced-option

Until we meet next time,

Be courteous. Drive responsibly.

I recently completed running a series on the “Underappreciated Features of Microsoft SQL Server”, under which I introduced the SQLCMD mode available within the SQL Server Management Studio. SQLCMD mode is a means of using SQLCMD functionality within the SSMS, which would normally have been available on the command prompt.

Now, SQLCMD is a very powerful utility – highly productive and flexible. I learnt that myself when researching for the original post. Here’s one feature that caught my immediate attention, and I won’t shy away from saying that it has made me use SQLCMD even more!

Default Editor on SQLCMD

The need of a script editor in SQLCMD

SQLCMD, as we all know, is used to run T-SQL scripts against a SQL Server instance via the command line interface. For example, I can run the following query as shown below:

USE AdventureWorks2008R2
SELECT BusinessEntityID,JobTitle FROM HumanResources.Employee
GO

This works great on small queries, as shown in this example. However, what about complex or queries running into multiple lines? Those would become difficult to write on the command line. That leads us to a the need of a script editor in SQLCMD.

The default Editor of SQLCMD

SQLCMD comes with a set of highly commands. One such command is “[:] ED”. If we type in “ED” on the SQLCMD prompt and hit the Enter key, we find that it launches the default editor of the command prompt – the very familiar – blue & white interface!

Using the set script editor in SQLCMD

So, now that we have our SQLCMD editor open, we can write our multi-line/complex queries that we would want to run as a batch. Let’s use the following formatted query as an example:

-- Declare a Table variable, and insert some data into it
DECLARE @MyTable TABLE (ID INT IDENTITY (1,1), MyName VARCHAR(100))

INSERT INTO @MyTable
SELECT msc.name + CONVERT(VARCHAR(10),ROUND(RAND()*1000,0))
FROM msdb.sys.objects mso (NOLOCK)
CROSS JOIN msdb.sys.columns msc (NOLOCK)

SELECT * FROM @MyTable
GO

Once you save & exit out of the editor, the entire query is available in the SQLCMD editor:

Hit “Enter” and watch the query zoom by.

Setting a default SQLCMD Editor

Now, not everyone would want to use the default DOS editor. Lots of better editors are now available and in use, and here’s how you can set your environment to use this editor as default in SQLCMD:

1. Navigate out to Control Panel –> System
2. Click on “Advanced System Settings”
3. Click on the “Environment Variables” button in the “Advanced” tab
4. Add a user variable (to keep this setting restricted to your user/login):

Variable Name: SQLCMDEDITOR

Variable Value: Executable name (e.g. NOTEPAD.EXE)

Now, launch the editor from the SQLCMD, and NOTEPAD would open up as the query editor. Once the query editing is complete, simply save & exit out of the editor to return back to SQLCMD.

Clean Up – Where do my SQLCMD files go? 

We saw above that each of these SQLCMD editors create temporary files which are edited by the users. Where do all of these files go? They simply go to the path specified by the environment variable %TEMP%. Normally, after the SQLCMD session ends, this file should be removed. In case it is not removed, one can always remove this file from the user’s temporary folder.

Reference: http://msdn.microsoft.com/en-us/library/ms162773.aspx

Do let me know if you have any other cool SQLCMD tricks. I will publish them on my blog with due credit.

Until we meet next time,

Be courteous. Drive responsibly.

In May 2011, I presented an Exclusive Microsoft VTD Webcast on the “Underappreciated Features of Microsoft SQL Server”. It was an exclusive webcast series wherein industry experts came in and presented on various topics to create a scalable and secure environment for solution with SQL Server 2008 (http://beyondrelational.com/blogs/nakul/archive/2011/04/29/exclusive-sql-server-webcast-series-on-security-amp-scalability-register-today.aspx).

The session went very smooth, and I was fortunate enough to have a strong following throughout the session. I presented the following underappreciated features during the session:

1. For Administration: Object Explorer Details and Object Explorer
2. For Development: Template Explorer and T-SQL Debugger

The presentation material is now available on the SQLServerFAQ MSDN blog and the webcast videos are available for download on MSDN.

For my session on “Learn Underappreciated Features of Microsoft SQL Server”

• PPT here: http://beyondrelational.com/media/p/12497.aspx
• Webcast Download:http://download.microsoft.com/download/6/0/C/60CBF7F0-5558-4C00-ACC0-ABFF25A92244/Day5_SQLServer webcast.wmv

Other speakers:

Refer the links below for the presentation deck and webcast video download link for other speakers of the Webcast series.

• Managing and Optimizing Resources for SQL Server [PPT | Webcast Download] – Balmukund Lakhani [Blog | @Blakhani]
• Optimizing and Tuning Full Text Search for SQL Server [PPT | Webcast Download] – Sudarshan Narasimhan [Blogs @ SQLServerFAQ]
• Understanding Performance Bottlenecks using Performance Dashboard [PPT | Webcast Download | QnA]
• Cool Tools to have for SQL Server DBA [Webcast Download] – Pradeep Adiga [Blog | @PradeepAdiga]

Have you registered for Community Tech Days (CTD) – Tech-Ed On The Road in Ahmedabad on June 11, 2011?

So, have you? If not, you are missing out on a lot of cool technology discussions! If you would like to change your mind, head over to my previous post here to know more and register!

See you on Saturday, June 11, 2011! Oh…and when you come down to the venue,

Be courteous. Drive responsibly.