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?!!

SSRS 2008 R2 - Reports are blank on Chrome

There is a frustrating issue with SQL 2008 R2 Reporting Services when rendering on Chrome (and Safari) - in that they simply display a completely blank screen.

After eventually convincing the users in question that it wasn't SQL's fault, or a problem with the report definition, since they displayed fine on internet explorer, I set about tracking down a fix.

There doesn't seem to be a lot of documentation out there on this problem at the moment, and the only place I found anything helpful was here: StackOverflow

Basically though, you need to update the ReportingServices.js file on the report server, as Chrome & Safari render a certain type of element in a slightly different way to IE.

FIX

Connect to your Reporting Services server, and find the relevant ReportingServices.js file - this will probably be somewhere like "C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\js\ReportingServices.js", or on a 64 bit machine, "C:\Program Files (x86)\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\js\ReportingServices.js"

First up, take a copy of this file - let's not be silly here and assume this is a 100% foolproof solution!

Next, edit this file in notepad. Do a quick search for PageLoad, to ensure no-one else has already hacked applied this fix, then jump to the bottom of the existing text.

Simply append the following code:

function pageLoad() { var element = document.getElementById("ctl31_ctl10"); if (element) { element.style.overflow = "visible"; } }

Save the file, and restart Reporting Services - you should now find that Chrome displays your reports again.

ADDITIONAL - if you have a problem getting reports to show up on IE 10 on Windows 8, it may be this fix you need instead:  SSRS Reports blank on Windows 8

UPDATE: the following combination of ctl codes seems to work on later versions :

function pageLoad() { var element = document.getElementById("ctl32_ctl09"); if (element) { element.style.overflow = "visible"; } }

Changing SSRS subscription owner

When someone creates a subscription in reporting services, that user is given ownership of the subscription, even though they may have nothing to do with that subscription.

This can cause a problem if that person leaves and their account is disabled, as the recipients of the subscription stop receiving their e-mail report, despite the SQL agent job completing successfully.  Looking at the subscription details of the relevant reports, you may see a message that:

Failure sending mail: The permissions granted to user 'Grumpydomain\Old.DBA' are insufficient for performing this operation.Mail will not be resent.

To fix this, we need to change the owner of the subscription.  Easy, you think - let's just select "change owner" on the Subscriptions tab and ... hmm ... well let's just edit the subscription and over-type the owner field ... ahh ... OK, well it must be in the reporting services config app under the email settings ... Rats!

Give in?

Well, there are probably other ways of doing it, but I found the easiest fix was simply to change the data directly in the ReportServer database.

The table you need to change is Subscriptions, wherever the OwnerID value matches the relevant UserID field from the Users table.

Once you've identified an alternate UserID to use, just update the Subscriptions table

UPDATE dbo.Subscriptions  SET OwnerID = '12345678-ABCD-1234-5678-0123456789AB'  -- new owner ID WHERE OwnerID = 'ABCDEFGH-4321-WXYZ-9876-ABCDEFGHIJKL'  -- old owner ID

You should now find the subscription runs (see this other post for working out which job you need to run though to test this!)

More deciphering of waitresource

While continuing to look at what is causing blocks, you come across another format of information in the waitresource column in sysprocesses.  This time you see:

PAG: 7:1:11223344

Again - thanks for the help SQL - object_ID too much trouble for you I guess??  Anyway, gives a DBA something to do I suppose.
This time it's not a hobt_id, but a page id, and is read as "DB_id, file_id, page_id"

To get this one into text, we need to do a couple of things.
First Run:
DBCC TraceOn (3604)


 - this just tells DBCC commands to output to the screen rather than errorlog, and just lasts for your current connection, otherwise the next command just tells you "DBCC execution completed" which is even less helpful than "PAG 7:1:11223344"!

Then run
DBCC PAGE (7, 1, 11223344)

(Note you could run this adding WITH TABLERESULTS for an even tidier output)

From the result set returned, you should be able to spot a few useful Id's, such as

Metadata: IndexId
Metadata: ObjectId

From here, with OBJECT_NAME() or just scanning sys.indexes, you should be able to ascertain what table and index you are being blocked by.

Decipher blocked process waitresource

When attempting to identify blocking issues, you may find yourself looking in sysprocesses at the waitresource column, and trying to decipher the meaning of something along the lines of:

KEY: 32:72057594097827840 (f5593d0e605b)

Thanks - you couldn't just tell me the actual object could you?!

But help is at hand - apparently this key consists of the database id, followed by a a hobt_id - no, not a small person with large feet from New Zealand, but a "Heap or B-Tree" id.

This id value can be found in the sys.partitions table, so a quick join from that to sys.indexes, and back to sys.obejcts finally reveals the index we are blocked on.
E.g.

SELECT o.name, i.name
FROM sys.partitions p
JOIN sys.objects o ON p.object_id = o.object_id
JOIN sys.indexes i ON p.object_id = i.object_id
AND p.index_id = i.index_id
WHERE p.hobt_id = 72057594097827840

Service Broker GUID does not match the one in sys.databases

Sometimes you can get a problem when trying to configure Service Broker on a database that has been restored / copied from another source (e.g. to a DR server), or even when trying to enable Database Mail on an msdb database that has been copied from elsewhere.

The error is something along the lines of:

Alter failed for database 'xxx'

Cannot enable the Service Broker in database "xxx" because the Service Broker GUID in the database (guid number) does not match the one in sys.databases (different guid number)
ALTER DATABASE statement failed.

This is because the master database also keeps track of service broker for each database, so this is just saying the master database thinks service broker has last processed a different record to the the one the database believes.

In the situation above, where we know this is the case because the database has come from another source, the quickest fix is just to reset the service broker as follow:

ALTER DATABASE XXX SET NEW_BROKER WITH ROLLBACK IMMEDIATE

This will drop and recreate all service broker settings and queues - so please only use this if you really do want to reset everything, don't do it if you have queues with statements in that you want processing - but for a quick fix on a DR database it can save a lot of headscratching.

Use a user data type (UDT) in temp table

Every now and then someone wants to use a user-defined data type (UDT) from a database in a temp table.

Initially trying it gives the error:
CREATE TABLE #Test (TestField GrumpyDBA)
Msg 2715, Level 16, State 7, Line 1
Column, parameter, or variable #1: Cannot find data type GrumpyDBA

To enable the datatypes in temp tables, create them in TEMPDB ... and assuming it's not just for a one off script, also in MODEL (so they are rebuilt in tempdb on each startup).

CREATE TYPE GrumpyDBA FROM INT NOT NULL

So far, so obvious, but the issue that now crops up is the following error when a user tries to create the table again:

Msg 15247, Level 16, State 4, Line 2
User does not have permission to perform this action.

It's quite easy to give up at this point, but it's actually an easy update to fix this as well, just run this in TEMPDB (and Model)

GRANT REFERENCES ON TYPE::dbo.GrumpyDBA TO public

And you should now find you can use the UDT in temp tables from the original database.

Net Send becomes MSG

OK, so it's not news, but NET SEND no longer is the favoured way to send messages to machines, and MSG is the new method - unfortunately it's not as easy to use, and by default doesn't even work!

NET SEND is built into SQL which is a problem when that's used as an alternative to email for getting alerts.  Getting it to use MSG instead can raise other problems too.

Basic syntax to use MSG to send a message to a user / workstation :- the main issue here is you need to know what machine the user is on, but unlike netsend, you can at least configure it to auto close, so you don't come in at 8am and find 5000 popups to click close on.

MSG * /SERVER:workstation001 /TIME:60 Hello I'm testing

Next issue is you are likely to get this error:

Error opening Terminal server Workstation001
Error [1722]:The RPC server is unavailable.

It seems that the latest versions of Windows, by default, do not allow the relevant RPC call

To fix, you need to open regedit, navigate to
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TerminalServer

Open the AllowRemoteRPC registry entry, and change the value to 1

You should now be able to use the MSG command.

You can use this to get SQL alerts as follows:
Create a file (e.g. msg.cmd file) with entries for each persons machine you want notified, such as:
MSG * /SERVER:workstation001 /TIME:60 %1
MSG * /SERVER:workstation002 /TIME:60 %1

Create a SQL agent job that calls that file via a CmdExec type step, along with [A-MSG], such as
\\network\DBA\msg.cmd "$(ESCAPE_NONE(A-MSG)) FROM $(ESCAPE_NONE(SRVR)) "

I've then configured alerts (e.g. backupfailed, replication issues etc.) on failure response to execute this job.

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.