SQL Server Backup Solution:

SQL Server Backup Solution:

This is the traditional backup solution by running backup script on regular intervals.

SQLServerMaintenanceSolution.sql . This script is used for Sql Server Backup, Integrity Check and Index Optimization.

The Solution is supported on SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2.

To implement this solution run this script once in the SQL Instance with the default Master database. Before you run the script, you have to modify following variables for the SQL backup location.

SET @BackupDirectory = N'C:\Backup' -- <== Change this to your backup directory.

SET @CreateJobs = 'Y' -- <== should jobs be created, 'Y' or 'N'?

This Script creates following Objects in Master Database:

Temporary Table:

1. #Config

Procedures:

1. dbo.CommandExecute

2. dbo.DatabaseBackup

3. dbo.DatabaseIntegrityCheck

4. dbo.IndexOptimize

Functions:

1. dbo.DatabaseSelect

Schedular Jobs:

1. DatabaseBackup - SYSTEM_DATABASES– FULL

2. DatabaseBackup - USER_DATABASES – FULL

3. DatabaseBackup - USER_DATABASES – DIFF

4. DatabaseBackup - USER_DATABASES-LOG

5. DatabaseIntegrityCheck - SYSTEM_DATABASES

6. DatabaseIntegrityCheck - USER_DATABASES

7. IndexOptimize - USER_DATABASES

8. Output File Cleanup

After running above scirpt schedule the Database backup/other job as per company’s backup policy.

That’s it, Job Done!