Change default backup path for SQL Server

Fed up having to change the path everytime you want to manually take a database backup?

Have you found places to change the default database and log file path, but are wondering why there doesn't seem to be anywhere you can change the default backup path?

If so, then this is the article for you (if not, then sorry - nothing to see here!)

To change the default backup path for a SQL instance, you need to open the Registry Editor (run "RegEdit").

Now Navigate to-
> HKEY_LOCAL_MACHINE
> SOFTWARE
> Microsoft 
> Microsoft SQL Server 
> SQL Instance (see below for naming style)
> MSSQLServer

Note the SQL Instance folder may be in an odd format - it seems to show MSSQL10_50.instancename for SQL 2008 R2, and just MSSQL.1 for SQL 2005 - but you want the one that has another 10 or so directories inside it, one of which is MSSQLServer.

In this MSSQLServer key you should see an entry for "BackupDirectory".

Simply open this and change the value to your backup directory path of choice, and next time you try to take a backup, you will find it defaults to that path instead.

Refresh SQL Intellisense cache

It may not be as good as Red-Gates SQLPrompt, but I have to admit I'm getting used to SQL 2008's Intellisense.

What I was starting to get fed up with though was creating new objects or fields, and finding them underlined by intellisense who refused to acknowledge that I was correct.

I figured it must have an internal cache, but I was always too lazy to hunt around to find out how to refresh it - until now.
To be honest, it wasn't that tricky and there are mouse click or keyboard shortcut methods.

Keyboard method
In the query window with the offending underlined object - press Ctrl + Shift + R.

Mouse method
Take your hand off the mouse, put a finger on a shift key, a Ctrl key, and the R key, and press down!!!
In fact you could keep your right hand on the mouse, and press the keys with your left hand.
However, if you really want to increase your chances of RSI, and insist of doing everything through slow and cumbersome graphical interface menus, far be it from me to get grumpy (too late), go up to the Edit menu in SSMS, and under "Intellisense", you will find "Refresh Local Cache" for you to click away on to your hearts content.

It even tells you there though just to press Ctrl + Shift + R, so just do that OK?!!

SQL 2008 - adding a column to a replicated table fails

There seems to be an issue in certain situations with adding columns to replicated tables in SQL 2008 where the publication is set to include schema updates.

The first error we saw was:

Msg 650, Level 16, State 1, Procedure sp_MSfixup_base_columns, Line 40
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

I'm also aware of a similar error mentioning a different system proc:

Msg 650, Level 16, State 1, Procedure sp_MSreinit_article, Line 66
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

It doesn't look like there is currently any supported workaround for it, but I managed to get it work by doing the following.

Step 1 - In the database where you are trying to change the table, script out the database trigger called "tr_MStran_altertable" as a create statement

Step 2 - Give the trigger a new name in this script, e.g. tr_MStran_altertable_ReadCom.  
Also, in the code you will see it has several SET statements, and to this section add:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Step 3 - run this script to create a new trigger.  

Note the reason for creating a new trigger is that you will get an error if you try to just ALTER the existing trigger to add this setting saying:

Msg 21598, Level 16, State 1, Procedure sp_MStran_ddlrepl, Line 60

Modifying DDL triggers created by replication is disallowed since these are required to track DDL changes.

Step 4 - disable the original trigger

DISABLE TRIGGER [tr_MStran_altertable] ON DATABASE

You should now find the original ALTER TABLE statement completes without an error, and the new field is also added to the replication.

Check which node a clustered SQL Instance is on

Sometimes it is useful to know which physical node a SQL instance is running on in a cluster - e.g. for performance monitoring to check CPU attributable to an instance.

You can check this via the cluster failover manager, but you can also run the following SQL statement on the instance in question:

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

This will  return the netbios name of the server the node is currently active on.

I've used this in conjunction with CPU monitors, and setting processor affinity by NUMA node to track CPU activity by instance. 
The assigned NUMA node can be obtained from sys.dm_os_nodes [update] if you are on SQL 2008 R2 such as:

SELECT memory_node_id FROM sys.dm_os_nodes WHERE cpu_affinity_mask = online_scheduler_mask AND node_state_desc = 'ONLINE'

SQL 2008 Management Tools install fails claiming Express Tools already installed

Quite a few users have had problems installing the SQL 2008 management tools - with an error message saying:

The SQL Server 2005 Express Tools are installed.  To continue, remove the SQL Server 2005 Express Tools.

After lots of playing around, uninstalling/reinstalling, it seems the issue is related to Red Gate tools, such as SQL Prompt and SQL Compare.

First try and uninstall any Red Gate tools you have installed and see if that fixes the issue.

If it still fails after uninstalling them, you may have to search the registry for any orphaned keys relating to the Red Gate tools

It looks like these Red Gate keys go under SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM - so if you still have these keys after uninstalling SQL Prompt, manually deleting these should fix the problem.

SQL 2008 Management Studio shows filename on tab

Another frequent complaint from users moving to the SQL 2008 Management Studio is that the text on the tabs now shows the filename rather than the server and database.

Don't panic!  This can be changed back!!

Simply select the Tools menu, and select "Options..."

Now in the left pane expand the "Text Editor" and select "Editor Tab and Status Bar". 

Finally scroll the right pane down to the bottom so you can see the "Tab Text" heading, and under that you can select what you want included on the tabs.

Cannot change table in SQL 2008 Management Studio

As more users are upgrading to SQL 2008, they are running into some of the small changes in the 2008 management studio.

One of these is an error message that pops up when a user tries to change a table via management studio design view by changing/deleting a column or adding a new column in the middle of the table.  Instead of saving the change, the user gets the following error:

This may fool you or the user into believing there is a permissions issue preventing the table being updated, but it's actually a setting in SSMS - presumably to prevent the application hanging if the table involved was of any significant size.

The fix though is to simply uncheck box in the Options window. 

Select "Tools" from the SSMS menu bar, then "Options..." from the drop down list. 

Expand the "Designers" section, and under the "Table and Database Designers" section, you should find the "Prevent saving changes that require table re-creation" option, which you can disable.

Or you could just learn how to use ALTER TABLE statements in T-SQL!

SQL 2008 - Where did Surface Area Configuration go?!

No sooner had we got used to opening the oddly titled "Surface Area Configuration" tool to change certain options on SQL 2005 servers that used to be on the properties tab in SQL 2000 (e.g. enabling xp_cmdshell), than it has moved again.

SQL 2008 brings us a new description of these items, and we must now call them a "Facet" ... and no, that's not another name for a tap.

However, on the plus side, you can now access these through the 2008 Management Studio.  Simply right click on your server name in the object explorer pane, and select "Facets" from the context menu.

This brings up the "View Facets" display.  From here you can select "Surface Area Configuration" from the Facet drop down menu, and enable or disable your required options directly from here.

The other good thing ... this also works for SQL 2005 servers, whereas alternative methods for doing this (e.g. Policies) don't.