SQL Server 2005/2008 And Osql Command Line Error: Cannot open backup device ‘%DBNAME%’. Operating system error 5(Access is denied.)

While trying to install a new version of my Antivirus management software I was making a backup of the databse with the command line:

>osql -E -S "SERVER\DBINSTANCE" -n -b -Q "BACKUP DATABASE DBNAME TO DISK = 'D:\BACKUPNAME.BAK'"

It threw the error:

>Cannot open backup device 'D:\BACKUPNAME.BAK'. Operating system error 5(Access
is denied.).

followed by:

>BACKUP DATABASE is terminating abnormally.

Failed

I tried the Microsoft SQL Server Management Studio console, but received the same error:

After some googling I stumbled upon this page where a fellow-countryman  took the trouble to post his solution some 3.5 years ago.

After checking, it seems the SQL Server service runs under the NETWORK SERVICE account.

This account, however, had no file system rights on the partition I was trying to write the backup file to.

After I granted modify permissions to the NETWORK SERVICE account, my issue was solved.

The tricky thing about this exercise, is the fact that I thought I had enough user rights to write the file because I opened the command prompt and Microsoft SQL Server Management Studio console with administrative rights. Obviously this was not the case because the actual process runs in the context of the SQL Server service.

Installing RES Automation Manager 2011 on Windows Server 2008 (R2) x64 on SQL Server 2008 R2 Express With Tools x64

You’ll need an admin account. I used Server 2008, my guess is that the procedure won’t differ much when using Server 2008 R2 although I have not tested this.

Download SQLEXPRWT_X64_ENU.exe and start the install.

Enable all features and I recommend installing on a separate partition or disk. Let me rephrase: DON’T install on your system disk (C:).

Change the Instance root directory to somewhere else as your system disk.

Choose Mixed Mode (mandatory according to the RES install docs). I recommend adding the local machine Administrators group as SQL Server administrators.

Complete the setup.

We now need to create a 32-bit System DSN. Otherwise the (32-bit) RES software will not find the database. Start the ODBC Data Source Administrator (Administrative Tools -> Data Sources (ODBC)). Select the System DSN tab and click Add.

Select the SQL Server Native Client 10.0 and press Finish.

Fill in the values as shown in the screenshot below and press Finish.

Test your connection with the button that appears in the next screen.

Press OK and OK again. Your System DSN is now created.

Now open the SQL Server Configuration Manager from the Start Menu and enable Named Pipes and TCP/IP protocols for SQLEXPRESS.

Now, reboot your server.

Download RES-AM-2011.msi and start the install. I recommend not installing on your system disk. Launch the console at the end of the setup.

Press the Create button to create the new datastore for RES Automation Manager 2011.

If you have not create the 32-bit System DSN or have not rebooted, you will not see <ServerName>\SQLEXPRESS in the pulldown list.

Choose <YourServer>\SQLEXPRESS or fill this in yourself. Use the sa account to create the database.

Choose a logical database name. I recommend using the default name.

Choose the location of the datafile and the transaction log file as well as the initial sizes and growth sizes. If you don’t know what values to use, the defaults will probably be sufficient for your needs.

Choose the user and password to be created to login to the database.

Click Next and Finish. The database will be created.

Your done. When you start RES AM, you can check and verify the settings on the Information option in the tree.

SQL Server 2008 Management Studio Express installation error: Performance counter registry hive consistency (failed)

I tried to install the SQL Server 2008 Management Studio for my SQL Server 2008 Express installation and received the error ‘Performance counter registry hive consistency’ with status ‘Failed’.

Log file:

InstallGlobalRules: SQL Server 2008 Setup configuration checks for rules group ‘InstallGlobalRules’
	PerfMonCounterNotCorruptedCheck	Checks if existing performance counter registry hive is consistent.	Failed	The performance counter registry hive is corrupted. To continue, you must repair the performance counter registry hive. For more information, see http://support.microsoft.com/kb/300956.

So I went to the Microsoft KB article that is supposed to help me out: KB300956. What a nightmare. After I finally removed the values for all FirstCounter, LastCounterer, FirstHelp and LastHelp entries and rebooted my PC… nothing changed. Oh wait, I forgot to run lodctr /R. Hmm, that requires input from the ini files I ‘lodctr-ed’ before the reboot. Hrrmphh. OK, done. Same setup error, grrr.

After some more searching I found a clue that pushed me in the right direction. I fired up Regmon(everyone should have the Sysinternal tools!) and excluded al running processes except setup100.exe. I pressed the Rerun button on the setup screen. I did a search for Perflib and came up with this:

I exported my HKLM\SOFTWARE\Microsoft\WindowsNT\CurrentVersion\Perflib09 registry key. Edited it to point to 013 and inserted it back into the registry. Same with 0413. Pressed rerun again and viola:

This obviously has everything to do with not running a US English Windows version. I can’t believe Microsoft didn’t test this on non-English systems though. Well.. maybe I can =)

I hope this helps out some people who got this annoying error.

Oh and by the way. If you receive the error ‘Restart computer with Status: Failed’, Just rename the PendingFileRenameOperations key in HKLM\SYSTEM\CurrentControlSet\Control\SessionManager to something else and re-create the REG_MULTI_SZ key again, but of course leave it empty =)

This is a generic tip that can help you with many equivalent errors of all kinds of software.