Archive | IT RSS for this section

How To Schedule Backups of SQL 2012 Express Databases

SQL Express does not have a SQL agent, nor maintenance plans so there is no way to point and click your way to victory. But don’t despair, you can script it quite easily! In this example we’ll back up a SQL database to a date stamped file, and then back it up to a remote server. In my case, I wanted to keep two days worth of backups locally on the server and only ever keep one on the remote server as the remote server backs that file off to tape when it has landed as part of that servers nightly run.

Log onto the SQL 2012 Express server in question and create the following SQL script using notepad:

DECLARE @pathName NVARCHAR(512) 
SET @pathName = 'C:\SQL Backup\NameOfDataBaseBackupFile_' + Convert(varchar(8), GETDATE(), 112) + '.bak' 
BACKUP DATABASE [DatabaseName] TO DISK = @pathName WITH NOFORMAT, NOINIT, NAME = N'db_backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

Save it as whatever you like, in this example I’ll call it NightlyBackup.sql

Note, the script sets your backup directory path, the name of the backup file and then appends the days date to it so you end up with a file called NameOfDataBaseBackupFile_YYYMMDD.bak in your back up folder.

Next, create the following batch file using notepad:

sqlcmd -S servername\INSTANCENAME -U sqluser -P sqluserpassword -i "PathToNightlyBackup.sql"
forfiles /p "C:\SQLBackupLocation" /s /d -2 /c "cmd /c del @file : date >= 2 days >NUL"
del \\RemoteBackupServer\SQLBackupDIR\* /Q
robocopy C:\SQLBackupLocation" \\RemoteBackupServer\SQLBackupDIR\ /maxage:1

Again, save it as something meaningful, in this example SQLbackup.bat

Note, this script performs 4 tasks:

1. It uses the sqlcmd.exe to connect to your SQL server and instance and runs the SQL script created earlier. It’s a good idea to create a SQL user that simply has _backupoperator rights to the database you wish to back up

2. It then deletes any files in the backup directory over 2 days old

3. It deletes anything in the remote backup location path

4. It backs up any files younger than a day to the remote location

Once you’ve tweaked these to you liking, you’ll need to schedule the batch script to run using Task Scheduler. I won’t go into detail on this as it’s covered elsewhere on the net but I created a basic task on a schedule appropriate to my needs, told it to start a program, chose the batch file (remembering to at it’s folder location to the “Start In” field),  set it to run whether the user is logged on or not and ensured it was running as a user that had the ability to run both the bactch script and access the remote location.

How To Fix Non Starting SQL Reporting Services

The SQL Server Reporting Services … service failed to start due to the following error:  The service did not respond to the start or control request in a timely fashion.

Sound familiar? It was a problem I was having with a couple of SQL Server 2008 R2 machines built on VMWare 5.1 hosts. The SQL Server Reporting Services don’t start automatically on reboot and won’t start when manually instigated.

Fortunately it can be easily remedied by increasing the default service time-out:

  • Open Regedit
  • Navigate to: KEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control
  • Create a new DWORD value called ServicesPipeTimeout
  • Modify it and ensure it is set to Decimal  and enter the value 60000
  • Close Regedit and reboot the server

Note:  Incorrect modification of the registry can lead to serious problems, please be careful. For protection it’s worth taking a backup of the registry before hand.

If this does not work then you may have a more serious issue, if this is the first time you have tried to start the service then double check you have met the prerequisites for installation and have it configured correctly.

How To Install Google Drive on Server 2008 R2

Running Microsoft Server 2008 R2 as your home lab but also use it as your desktop? Trying to install Google Drive and getting the following error?

The installer encountered error 1603: Fatal error during installation.

Or

The application has failed to start because its side-by-side configuration is incorrect

It’s because it as a dependency it doesn’t tell you about, you need Microsoft Visual C++ 2008 SP1 Redistributable Package (x64) to install it and then Microsoft Visual C++ 2008 SP1 Redistributable Package (x86) to actually run it! As soon as you’ve installed it you can run the usual googledrivesync.exe installaer and that’s it!

Get the downloads (around 4Mb) from Microsoft below:

Microsoft Visual C++ 2008 SP1 Redistributable Package (x64)

Microsoft Visual C++ 2008 SP1 Redistributable Package (x86)

How To Remove Hiberfil.sys And Free Up Some Space

Trying to spot where all your hard disk space has gone? Tracked it down to a hidden file called hiberfil.sys and wonder what it is?

Do you use System Hibernate ever? No? Good, here’s how to disable it and claim back some space.

Open a command prompt as an administrator and paste in the following command:

powercfg -h off

That’s it! You’ll notice the hiberfil.sys file has now gone too!

Got Windows XP? Then it’s just as easy, simply go to Control Panel –> Power Options, and then find the Hibernate tab and untick the Enable Hibernation pidget and click Ok.

How to Fix Microsoft SQL Server 2008 Reporting Services UAC Problem

“User ‘DOMAIN\user’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.”

Sound familiar?

In my case this was on a Server 2008 R2 server with SQL 2008 installed and patched up to date. I knew the permissions were correct so it had to be UAC, so I turned it off and everything worked, not the solution I wanted but it proved my theory.

Now, as standard Reporting Services should allow anyone who is a local admin on the machine hosting it to access the web page. However, if you have UAC turned on you may not be able to open it without the above error. If this is the case, simply open IE on the server as an admin to bypass UAC. It’s worth noting that you may need to add the site (plus the localhost version) to trusted sites but I found this was not necessary on the default setup I have.

Now on to enabling access for users and groups without turning off UAC.

Firstly:

Once successfully on the Reporting Services page, click on the “Site Settings” button in the top right corner, select the “Security” tab and select “New Role Assignment”. At this point you can add the required users or groups in the “DOMAIN\username” format.

Secondly:

You will need to give people at least basic access to the root “Home” folder for them to gain access to the web portal at all, even if you have made them a site admin in the above step. To do this, return to the “Home” page and click “Folder Settings” from the nav bar, from here you can again click “New Role Assignment” and then assign the desired roles for the folder. These assignments will be inherited by any sub folder underneath, you can adjust the permissions on those in a similar way.

That’s it, headache over!

Plain Text Offenders

Just a quickie, you may or may not know that certain websites will email you your password to you as plain text if you have forgotten it. This is obviously a big no no and against best practice for security and quite concerning that site still do it.

Enter PlainTextOffenders.com, a great little blog that names and shames them, plus allows you to submit new ones.

Take a browse!

How to grant access permission for SSIS

On a basic level, the quickest way to grant permissions to remotely access SSIS to a user that is not an admin of the server is:

  • Open Control Panel – Administrative Tools – Component Services
  • Expand the Component Services node and then Computers – My Computer – DCOM Config
  • Right click on MSDTSServer and select Properties
  • Select the Security tab
  • Select Customize and then add the required user/group to the Launch and Activate Permissions and Access Permissions giving them the required rights
  • Also add the user to the Distibuted COM Users local group through Computer Management – Local Users and Groups
  • Restart the SQL Server Integration Services service

Job done!

%d bloggers like this: