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