A complete maintenance plan for SQL Server 2008
Optimising Databases
This section is dedicated to keeping databases optimised. In this example, we are going to set up the code to do the following (see a rough diagram of how the servers are connected);
- Backup database from live server to a shared backup folder
- Re-Index Database
- Kill all connections and restore the database on reporting server.
- Linked Servers will be in a separate article soon
- Log Shipping will be in a separate article soon
When doing it cross server, you will need to allow the account that SQL Server is running from access to the target servers file system.
All SQL code is run from a utilities database, and schema set to "maint"
There are a host of other articles we have written below that may be of interest to you.
Complete Maintenance Plan
Now we can manage all of these separate code segments with another procedure that will call them in the correct order, this will be put in the live server. The code runs as follows;
- Work out the time
- If it is evening re-index the database
- Run a backup of the database (add where you need)
- If it is evening back up the other databases (add where you need)
- Kill connections and restore database (add where you need)
This code has been tried and tested and ran for periods of months without any issues.
Please note that the shrinking of log files and database files should be kept to a minimum, by running a backup you are emptying the log (although it will retain the space used). If it is needed when it should be run outside of normal working hours.
SQL Code
Use [utilities]GOCREATE PROC [maint].MaintenancePlan AS BEGINDECLARE @BackupType VARCHAR(1)='E'IF DATEPART(HOUR,GETDATE()) BETWEEN 5 AND 21 BEGINSET @BackupType='D'END--EXEC ('USE TempDb; DBCC SHRINKFILE(templog, 0)');--This is only needed when space is at a premium!--Re-index LiveIF @BackupType='E' EXEC [maint].DatabaseReIndex 'dbname'--Create BackupBACKUP DATABASE TO DISK=N'{backuplocation}{dbname}.bak'WITH NOFORMAT, INIT, NAME =N'{dbname}', SKIP, NOREWIND, NOUNLOAD, STATS= 10;--EXEC ('USE ; DBCC SHRINKFILE(_log, 0)');--This is only needed when space is at a premium!--Backup Other Files at NightIF @BackupType='E' BEGIN EXEC [maint].DatabaseReIndex 'dbname' --Backup Others BACKUP DATABASE [databasename] TO DISK=N'{backuplocation}{dbname2}.bak' WITH FORMAT,INIT, NAME =N'{dbname2}',SKIP, NOREWIND, NOUNLOAD, STATS= 10END--Restore Backups on other serverEXEC [server].[utilities].[maint].KillConnections 'dbname';EXEC [server].[utilities].[maint].RestoreDatabase_{dbname};--Restore Backups on other server for db_2 etcIF @BackupType='E' BEGIN EXEC [server].[utilities].[maint].KillConnections 'dbname2'; EXEC [server].[utilities].[maint].RestoreDatabase_{dbname2};ENDENDGOBackup Database
To get the code to backup your database, it is easiest to script the code from SSMS.
Follow the process you would normally use, and then select "Script Action to New Query Window".
Copy this code into the maintenance plan.
Re-Index Database
Next we can add some code to re-index our database, this again is a share stored procedure where you only need to tell the system the name.
To avoid code duplication, you can get read about it on the link below.
Kill Connections
When performing a restore on the database, you can only have one connection to it (the process performing the restore), therefore we can create a stored procedure to close all connections apart from the current process. We have again created a separate article for this.
Restore Database
This code can also be scripted from SQL Server Management Studio. If you add this code to a stored procedure, you can call it from other processes and even other servers really easily. We've moved this into a separate article to cover more options.
