Automated Backup & maintenance plan for databases

Automated Backup & maintenance plan for databases

Sometimes a transaction log file contains unused space that you will not need; you can reclaim the excess space by reducing the size of the transaction log. This process is known as shrinking the log file. 

Shrinking can occur only while the database is online and, also, while at least one virtual log file is free. Shrinking the transaction log reduces its physical size by removing one or more inactive virtual log files. The unit of the size reduction is always the virtual log file.

Typically it is the log file that appears not to shrink. This is usually the result of a log file that has not been truncated. You can truncate the log by setting the database recovery model to SIMPLE.

The backup plan contains several steps in order to reduce the log size and create full backup. Briefly, it will be as follows:

• Change database recovery model to Simple.
• Shrink database’s log file.
• Change database recovery model back to full.
• Take full backup of the database.

Backup Procedures:

1- SQL server agent job will be created for database and will be scheduled. It will include 4 steps:

• Change database recovery model to Simple: in order to be able to shrink the log.
• Shrink database’s log file: in order to shrink the log to reduce its size.
• Change database recovery model back to full: to take the full backup.
• Take full backup of the database.
• Copy the backup file to retained backups. [to keep old backups]
• Append the today date to the backup name.

2- SQL maintenance plan will be created to clean the backups older than 2 days.

Create SQL Backup job

1.      In the Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

2.      Expand SQL Server Agent.

3.      Right-click Jobs and then click New Job.

4.      On the General page, in the Namebox, type a name for the job, for example “DB1 DB backup”.

5.      4 steps will be created in each SQL job.

6.      On the Steps page, click New

7.      In the New Job Step dialog, type a job Step name, for example: DB1 recovery model to Simple.

8.      In the Type list, click Transact-SQL Script (TSQL).

9.      In the Command box, type the Transact-SQL command batch:

USE [master]

GO

ALTER DATABASE [DB1] SET RECOVERY SIMPLE WITH NO_WAIT

GO

10.   Click Parse to check your syntax.

11.   The message "Parse succeeded" is displayed when your syntax is correct. If an error is found, correct the syntax before continuing. Then, Click ok.

12.   On the Steps page, click New to create the next step.

13.   In the New Job Step dialog, type a job Step name, for example: DB1 Shrink DB log.

14.   In the Type list, click Transact-SQL Script (TSQL).

15.   In the Command box, type the Transact-SQL command batch:

USE [DB1]

GO

DBCC SHRINKFILE (N'DB1 _log' , 0, TRUNCATEONLY)

GO

16.   Click Parse to check your syntax.

17.   The message "Parse succeeded" is displayed when your syntax is correct. If an error is found, correct the syntax before continuing. Then, Click ok.

18.   On the Steps page, click New to create the next step.

19.   In the New Job Step dialog, type a job Step name, for example: : DB1 recovery model to Full.

20.   In the Type list, click Transact-SQL Script (TSQL).

21.   In the Command box, type the Transact-SQL command batch:

USE [master]

GO

ALTER DATABASE [DB1] SET RECOVERY FULL WITH NO_WAIT

GO

22.   On the Steps page, click New to create the next step.

23.   In the New Job Step dialog, type a job Step name, for example: : DB1 Full Backup.

24.   In the Type list, click Transact-SQL Script (TSQL).

25.   In the Command box, type the Transact-SQL command batch:

BACKUP DATABASE [DB1] TO  DISK=N'S:\MSSQL10_50.MSSQLSERVER\MSSQL\ScheduledBackups\DB1' WITH  NOFORMAT,

INIT,  NAME = N'DB1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

26.   On the Steps page, click New to create the next step.

27.   In the New Job Step dialog, type a job Step name, for example: Copy DB.

28.   In the Type list, click Transact-SQL Script (TSQL).

29.   In the Command box, type the Transact-SQL command batch:

EXEC xp_cmdshell '

copy "S:\MSSQL10_50.MSSQLSERVER\MSSQL\ScheduledBackups\DB1" "S:\MSSQL10_50.MSSQLSERVER\MSSQL\RetainedBackups"'

GO

30.   On the Steps page, click New to create the next step.

31.   In the New Job Step dialog, type a job Step name, for example: Rename DB.

32.   In the Type list, click Transact-SQL Script (TSQL).

33.   In the Command box, type the Transact-SQL command batch:

EXEC xp_cmdshell '

ren "S:\MSSQL10_50.MSSQLSERVER\MSSQL\RetainedBackups\DB1" DB1-%date:~4,2%%date:~7,2%%date:~10,4%.bak'

GO

34.   On the schedule page, click New to create the next step.

35.   In the New Job Schedule dialog, type a job Schedule name, for example: DB1 Full Backup Schedule.

36.   In the Frequency section, choose Daily,  then click Ok

37.   In the Command box, type the Transact-SQL command batch

38.   In New Job page, click Ok. Now the Job is ready to run every day at 12am.

Create Maintenance clean-up plan

1.        In Object Explorer, expand a server, and then expand Management.

2.        Right-click Maintenance Plans and select Maintenance Plan Wizard.

3.        On the first page click Next.

4.        On the select plan properties, type the plan name, for example: Backups clean-up.

5.        In the Schedule, choose Single Schedule for the entire plan or no Schedule, and click change.

6.        Type the name of the Schedule, for example: clean-up Schedule.

7.        Under the frequency section, in occurs, choose Daily.

8.        Under daily frequency, choose occurs once at, and set the time to 6:00 am, then click Ok.

9.        On the page of select maintenance tasks, choose Maintenance clean-up task, and click next.

10.     On the page of Define Maintenance clean up task,

·        In delete files of the following type, select Backup files.

·        In file location, select Search folder and delete files based on extension. Then    browse to S:\MSSQL10_50.MSSQLSERVER\MSSQL\RetainedBackups. In file extension, type bak.

·        In file age, select delete files bases on the age of the file at task run time, and set it to 2 days.

11.     Then click next and finish.