DigitalJargon

RSS
Jul 3

Implementing Standard SQL Maintenance Jobs on SQL Express

One major piece that is missing from SQL Express is SQL Agent and the ability to schedule routine maintenance like database integrity checks, index/statistics maintenance, and backups. I prefer to run Ola Hallengren’s scripts on all of my production SQL instances due to their performance benefits, ease of portability and configuration, and logging options. These scripts are used around the world and use built in SQL objects to perform standard database maintenance. They are easy to configure and well tested. Luckily the same scripts can be used to maintain SQL Express instances with the use of the Windows Task Scheduler.

Getting Started

Before you get started I recommend creating three directories on your server. One named SQLBackups and one named SQLJobLogs. The first will house your database backups and the latter will contain logs for your maintenance jobs. The third directory I create is called SQLScripts and will contain the jobs/batch files for all of my maintenance tasks. 

Now you need to go to http://ola.hallengren.com/ and download the latest version of the scripts. Open the script and you will need to modify three parameters. Change the @CreateJobs parameter to ‘N’ since SQL Express doesn’t support SQL Agent jobs anyway. Next update the @BackupDirectory and @OutputFileDirectory to point to the two directories you created earlier. To install simply execute the script. I prefer to create a database named DBAUtility to house the stored procedures and logging table but any database including master can be used.

At this point all of the framework is in place to perform your maintenance tasks. I usually follow the schedule of weekly index/statistic maintenance, followed by database integrity checks, and then full database backups. I also perform daily differential database backups.

Creating the “Jobs”

Since SQLExpress doesn’t have the ability to create SQL Agent Jobs we will be creating batch files instead. The batch files will be scheduled via Windows Scheduled Tasks.

In my SQLScripts folder I will create a batch file for each of my tasks. The nice thing about using these scripts is they can be suited to fit your maintenance needs. The examples below contain my normal routines but feel free to modify things to suit your needs.

Index Maintenance

I create a batch file named 1-IndexMaint-AllDB.bat. This file will perform index maintenance against all of my databases. It contains the following line:

sqlcmd -E -S SQLExpressInstanceName -d DBAUTILITY -Q “EXECUTE [dbo].[IndexOptimize] @Databases = ‘ALL_DATABASES’, @FragmentationLow = NULL, @FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’, @FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’, @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @UpdateStatistics = ‘ALL’, @OnlyModifiedStatistics = ‘Y’, @LogToTable = ‘Y’” -b -o C:\SQLJobLogs\1-IndexMaint-AllDBs.txt

sqlcmd is a command line utility that can be used to issue transact SQL statements. The -E flag is for windows authentication, -S is the instance name, -d is the database that houses the script, and -Q is my query.

In this example it is calling the IndexOptimize stored procedure that was created in my DBAUtility database. Each index is analyzed for fragmentation and then the specified maintenance is performed on it.If fragmentation is < 5% nothing is done, if it is > 5% and < 30% then a index reorganization is performed. If that is not possible on online rebuild is attempted. If that is not possible an offline rebuild is attempted. If fragmentation is > 30% the online/offline rebuilds are attempted. I also update modified statistics and log to my CommandLog table in DBAUtilitiy and my SQLJobLogs folder. All of these options and more are well documented on http://ola.hallengren.com/.

Database Integrity

For my 2-IntegrityChecks-AllDBs.bat file I use:

sqlcmd -E -S SQLExpressInstanceName  -d DBAUTILITY -Q “EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = ‘ALL_DATABASES’, @CheckCommands = ‘CHECKDB’, @LogToTable = ‘Y’” -b -o C:\SQLJobLogs\2-IntegrityChecks-AllDBs.txt

Full Database Backups

For my 3-FullBackups-AllDBs.bat file I use:

sqlcmd -E -S SQLExpressInstanceName   -d DBAUTILITY -Q “EXECUTE [dbo].[DatabaseBackup] @Databases = ‘ALL_DATABASES’, @Directory = ‘C:\SQLBackups’, @BackupType = ‘FULL’, @CleanupTime = ‘312’, @Verify=’Y’, @CheckSum = ‘Y’, @LogToTable = ‘Y’” -b -o C:\SQLJobLogs\3-FullBackups-AllDBs.txt

This performs a full backup on every database to my SQLBackups folder. The backups are kept for 312 hours before they are removed. I also perform checksum verifications on the backups and log to my table and SQLJobLogs folder.

Cleanup

This takes care of my weekly tasks. In order to keep my logging table clean I create a 4-CleanLogTable.bat file that cleans up logs older than 45 days.

sqlcmd -E -S SQLExpressInstanceName -d DBAUTILITY -Q “DELETE FROM [dbo].[CommandLog] WHERE StartTime < DATEADD(dd,-45,GETDATE())” -b -o C:\SQLJobLogs\4-CleanUpLogTable.txt

You may also want to create a file to keep your SQLJobLogs cleaned up.

Differential Database Backups

I also create a 5-DiffBackups-AllDBs for my daily differential backups.

sqlcmd -E -S SQLExpressInstanceName  -d DBAUTILITY -Q “EXECUTE [dbo].[DatabaseBackup] @Databases = ‘ALL_DATABASES’, @Directory = ‘C:\SQLBackups’, @BackupType = ‘DIFF’, @CleanupTime = ‘168’, @Verify=’Y’, @CheckSum = ‘Y’, @LogToTable = ‘Y’” -b -o C:\SQLJobLogs\6-DiffBackups-AllDBs.txt

Putting it All Together

In order to string things together II create two more batch files. One named WeeklyMaint.bat and one called DailyMaint.bat.

WeeklyMaint.bat:
C:
cd C:\SQLScripts
call 1-IndexMaint-AllDBs.bat
call 2-IntegrityChecks-AllDBs.bat
call 3-FullBackups-AllDBs
call 4-CleanupLogTable.bat

DailyMaint.bat:
C:
cd C:\SQLScripts
call 6-DiffBackups-AllDBs.bat
call 5-CopytoBackupServer.bat

The last step is to add a Windows Scheduled Task that runs WeeklyMaint.bat once a week and DailyMaint.bat nightly.

As these run the results of the jobs will be logged in your CommandLog table and SQLJobLogs folder. 

Copying Backups Off Server

I also recommended adding another step in the process to copy your backups to a 2nd location in case of a drive failure. I use a simple RobyCopy script for this.

@echo off

set RoboCopyPath=”C:\SQLScripts\robocopy.exe”
set Source=”C:\SQLBackups\YourSQLInstanceName”
set Dest=”\DestinationSharedDrive\SQLBackups\YourSQLInstanceName”
set LogFolder=”C:\SQLJobLogs”

%RoboCopyPath% %Source% %Dest% /mir /zb /r:30 /v /fp /log:%LogFOlder%\RoboCopyLog.txt

if errorlevel 3 echo OKCOPY + XTRA & goto end

:end

Recovering the Master Database from Scratch on SQL 2005 and SQL 2008

I recently ran into a situation where multiple servers lost their data  and log drives that housed the user and system databases. The servers still had valid SQL installs since the system drive was still available. Instead of re-installing SQL Server and service packs/hotfixes from scratch I used the steps below to create a new master database, restore the backed up system databases, and finally restore the backed up user databases. This method was much quicker and easier than uninstalling/re-installing SQL from scratch.

SQL 2008

  1. Copy the SQL 2008 installation media to the server
  2. Open a command prompt and go to the install directory above and run
    1. setup /ACTION=REBUILDDATABASE
      /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS= /SAPWD=
      1. Use your account or an AD group for the SQLSYSADMINACCOUNTS flag. This account/group will have sysadmin rights with the new master database.
      2. Use a random complex password for the SAPWD flag. It won’t be needed after master is restored from backup.
  3. Start server in single user mode (use –m flag in the SQL server startup parameters in configuration manager) and log in with Management Studio
  4. Run script to restore database
    1. RESTORE DATABASE master FROM DISK = ‘L:\SQLBackups\Master0.bak’ WITH REPLACE;
  5. Remove single user flag and restart SQL
  6. Restore model and MSDB like a normal database
  7. Restart SQL agent
  8. Restore user dbs

SQL 2005

  1. Find a SQLSetup*.log file from the original install and open it. Find the original install location path and copy the install media to that same path
    1. The process below will fail if you run it from a different directory than the original install.
  2. Open a command prompt and go to the install directory and run
  3. start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine
    REBUILDDATABASE=1 SAPWD= REINSTALLMODE=omu

Use a random complex password for the SAPWD flag. It won’t be needed after master is restored from backup. The REINSTALLMODE=omu is needed if a service pack is installed. If this flag is left out the installer will fail because it tries to restore the original version of the master databas Open a command prompt and go to the SQL binary directory (D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn) and start SQL in single user mode (sqlservr.exe –m) then log into Management Studio Run script to restore database

  1. RESTORE DATABASE master FROM DISK = ‘L:\SQLBackups\Master0.bak’ WITH REPLACE;

Restart SQL server Restore model and MSDB like a normal database Restart SQL agent Restore user dbs

SQL Reporting Services Fails with “The Extension is not available to users.”

If you are unable to access your SQL Reporting web site and you receive the error below in the Windows Application Event Log simply restart IIS to resolve.

The Windows extension failed to read its confiuration settings from the RSReportServer.config file. The extension is not available to users.

How to Resolve “The Remote name could not be resolved:” When Connecting to Local SQL Reporting Services

If you have modified your machine name you may receive the error “Cannot connect to XXXXX”. “The remote name could not be resolved: ‘oldcomputername’ (Microsoft SqlServer.Management UI RSClient)”.

To resolve you need to edit reportserver.config. By default this file is at  C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer. Do a search for the old computer name and replace with with the current computer name or “localhost”.

Apr 6

Backing Up SQL 2005 Express Databases

Two of the features missing from SQL 2005 Express is support for SSIS and SQL Agent. Without these two features you are unable to setup maintenance plans to backup your SQL databases to disk. Luckily a user at webhostautomation.com developed a script that you can call from a batch file.

The script can be viewed herehttp://forums.webhostautomation.com/showthread.php?t=17198 .

Save the script as a .sql file and then use Windows Task Scheduler to call a batch file with the following text:

sqlcmd -S . -i “C:\Backup_All_Databases.sql”

Creating a Linked Server from SQL 2K5 64-bit to SQL 2K5 32-bit

I ran into an issue creating a linked server on SQL 2005 64-bit. I was trying to connect to a 2000 32-bit server. The error I received was:

OLE DB provider “SQLNCLI” for linked server “servername” returned message “Unspecified error”.

OLE DB provider “SQLNCLI” for linked server “servername” returned message “The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.”.

Msg 7311, Level 16, State 2, Line 1

Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI” for linked server “servername”. The provider supports the interface, but returns a failure code when it is used.

To fix this error you need to run the instcat.sql file on the 2000 instance. This file is in SP4. This file adds a missing stored procedure to the 2000 instance.

Renaming a Stand Alone SQL 2005 Server

The other week I ran into an issue where I had to rename the server that a SQL 2005 instance was installed on. It was a pretty simple process. First rename the server and reboot. As far as SQL goes all you have to do is update the sysservers table by running the stored procedures below.

For a renamed default instance, run the following procedures:
sp_dropserver
GO
sp_addserver, local
GO

For a renamed named instance, run the following procedures:
sp_dropserver
GO
sp_addserver, local
GO