Tag Archive | Microsoft

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.

Advertisements

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!

How To Clean Up C:\Windows\Winsxs

So you’ve been taking a look at what’s eating your hard drive space with WinDirStat or similar and have spotted the C:\Windows\Winsxs folder.

Winsxs stands for Windows Side by Side and is basically where Windows keeps multiple versions of the same .dll’s to allow multiple applications to run without any compatibility problems. If you browse it you’ll see what looks like a lot of duplicate .dll files. I’m not going to go into the in’s and out’s of it here as there are plenty of good run through pages on the web.

What I will do is give you the easy and safe way to clear it down.

From the C:\Windows\System32 folder run:

DISM /online /Cleanup-Image /SpSuperseded

That’s it! Easy!

How To Enable Extra Object Info In Active Directory Using AcctInfo.dll

Quick heads up, it’s an oldie but goodie.

Ever wanted to see all that extra user info in AD such as password last change/next expiry without having to go into ADSIedit or resorting to the command line? Well you can!

Download and install the Windows Server 2003 Resource Kit from here

Next register the AcctInfo.dll found in C:\Program Files\Windows Resource Kits\Tools\ you can do this using the following command:

regsvr32 “C:\Program Files\Windows Resource Kits\Tools\Acctinfo.dll”

Open open the AD Users and Computers MMC and open a user, then check out the new Additional Account Info tab, sweet!

Note: I’m using Windows 7 x64 Professional and only get the extra tab when browsing to the user in it’s OU rather than running a Find, haven’t tested if this is just Win 7 – AD 2003 compatibility issue but it’s worth noting.

%d bloggers like this: