Forcing Access to SQL Server

I recently inherited a few SQL instances that I did not have access to. You can use one of the following methods to add a login and give yourself access.

  • Option 1 (Using PSExec/No Server Restart Required)
    • Download PSExec from Microsoft
    • Remote desktop into the server hosting the instance.
    • Use PsExec to launch SQL Server Management Studio
      • PsExec -s -i “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe” (Update your path based on your setup.)
    • Add a SQL login
    • If you run into errors you may need to disable UAC.
  • Option 2 (Requires SQL Server Restart)
    • Remote desktop into the server hosting the instance.
    • Stop the SQL server service.
    • Open an elevated command prompt. Browse to the SQL Binn folder.
    • Run “sqlservr -m” to start the server in single user mode.
    • Launch another elevated command prompt.Launch sqlcmd (sqlcmd -E)
    • Run commands to add login and add to sysadmin
      • Create login TestUser with password=’Test’
      • Exec sp_addsrvrolemember [TestUser], [sysadmin]
    • Restart the SQL server service.

Recovering the Master Database from Scratch

I ran into a situation where multiple servers lost their data and log drives that housed the user and system databases due to a SAN failure. 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.

  • Copy the SQL installation media to the server
  • Open a command prompt and go to the install directory above and run setup /ACTION=REBUILDDATABASE
    /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS= /SAPWD=

    • Use your account or an AD group for the SQLSYSADMINACCOUNTS flag. This account/group will have sysadmin rights with the new master database.
    • Use a random complex password for the SAPWD flag. It won’t be needed after master is restored from backup.
  • Start server in single user mode (use –m flag in the SQL server startup parameters in configuration manager) and log in with Management Studio
  • Run script to restore database
    • RESTORE DATABASE master FROM DISK = ‘L:\SQLBackups\Master0.bak’ WITH REPLACE;
  • Remove single user flag and restart SQL
  • Restore model and MSDB
  • Restart SQL agent
  • Restore user databases

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.

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