Backup Strategy

Exercise 1: Backup Strategy – Design

Purpose:

Creating a maintenance plan.

scenario

The NorthWind administrator has asked you to build a recovery strategy for the NorthWind database. The strategy must meet the following conditions:

The strategy should use reusable backup devices

Log Log files must be copied (back up). Full daily backups should be intertwined with differential backups

The main points of the exercise:

In this exercise we will use SSMS to create a maintenance plan. The main tasks for solving this exercise are:

1. Make sure SQL Server Agent Service is ON (running)

2. Design a Backup strategy.

Solution

Task 1: Make sure SQL Server Agent Service is ON (running)

1. Open the SQL Server Configuration window: Start -> Microsoft SQL Server -> Configuration Tools -> SQL Server Configuration Manager

2. In the window that opens on the left click on SQL Server Service

3. Make sure the SQL Server Agent service for MSSQLSERVER is started (Running). If you close the window. If not, then click on this service with the right mouse button on it and select Start.

4. Finally close the window.

Task 2: Design a Backup strategy

1. Open SSMS. Start -> Microsoft SQL Server Tools -> SQL Server Management Studio.

2. In the window that appears, connect to the server by setting your credentials and click on the Connect button.

3. In Object Explorer, open the Databases folder and right-click on the Northwind database by selecting the Properties option.

4. In the window that opens, in the Select a page panel, select Options.

5. Select the Recovery Model = Full and then click OK.

6. In Object Explorer, open the Management folder.

7. Right-click on the Maintenance Plans node, and then click New Maintenance Plan.

8. In the window that opens, in the Name field, enter the name of the maintenance plan: NW_FullBack. Then click OK.

9. Drag & Drop a Back Up Database Task from the Tasks Toolbox Maintenance Plan.

10. Change the task name to Full NorthWind Database Backup.

11. Double-click on the new task and in the new window, check the features of the plan, which should be as follows:

Follow Table

12. Click the Add button, part of the Backup up databases accros one or more files. In the window that will open, if the device:

C: \ Program Files \ Microsoft SQL Server \ MSSQL14.MSSQLSERVER \ MSSQL \ Backup \ Northwind.bak exists, then select it. Otherwise in the FileName field, enter the name Northwind.bak.

13. Then close the open windows by clicking the OK button.

14. Then click the Subplan Schedule button in the top panel of the Maintenance Plan, and specify the following planning features, and then click the OK button.

Create a new Back Up Database Task task, naming it Full NorthWind Log Backup.

16. Click on this task, and connect via the green arrow to the Full NorthWind Database Backup task.

17. Realize once again the points 11-14, for the new task created. In point 11, select as type bakcup, type: Transaction Log.

18. Finally, close NW_FullBack [Desing] by saving the changes made.

19. In Object Explorer, in the Management folder, right-click on the Maintenance Plans node, and then click New Maintenance Plan.

20. In the window that opens, in the Name field, enter the name of the maintenance plan: NW_DiffBack. Then click OK.

21. Create a new task for me by naming it Diff NorthWind Database Backup.

22. Then open the Properties window, setting the backup specifications according to the following table:

Er table

23. Then click on the Subplan Schedule button in the top panel of the Maintenance Plan, and specify the following features for planning and then click the OK button.

24. Create a new task, labeled Diff Northwind Log Backup. Connect it to the Diff NorthWind Database Backup task.

25. Then do points 22 and 24 for this new task.

26. Finally close the window to create NW_DiffBack Maintenance Plan.

27. Finally close the SSMS.

Exercise 2: Bakcup Strategy – Implementation

Purpose:

Creating a NorthWind database backup strategy.

scenario

The NorthWind administrator has asked you to build a disaster recovery strategy for the NorthWind database.

The main points of the exercise:

In this exercise you will create the backup device and then perform the backup of the NorthWind database. You will first perform a complete database and transaction backup, followed by a differential backup and finally a transaction backup. The main tasks of this exercise are:

1. Creating an SQL Server Management Studio scripts project.

2. We prepare basic Northwind data for backup

3. Backup the Northwind database.

Solution

Task 1: Creating an SQL Server Management Studio scripts project

1. Open SSMS. Start -> Microsoft SQL Server Tools -> SQL Server Management Studio.

2. In the window that appears, connect to the server by setting your credentials and click on the Connect button.

3. Select File-> New -> Project. In the window that opens, specify the following values ​​and then click OK.

4. Select Project -> New Query from the menu. In some cases, the login window may reappear, where you need to reset your credentials and log in.

5. The Solution Explorer panel should appear on the right side of the window. If you do not see it, then you can select it from the View menu -> Solution Explorer.

6. Right-click on the SQLQuery1.sql file and select Rename, placing the new name Backups.sql.

Task 2: Prepare Northwind database for backup

1. In Object Explorer, open the Databases folder and right-click on the Northwind database by selecting the Properties option.

2. In the window that opens, in the Select a page panel, select Options.

3. Select the Recovery Model = Simple and then click OK.

4. In Object Explorer, open the Databases folder and right-click on the Northwind database by selecting the Properties option.

5. In the window that opens, in the Select a page panel, select Options.

6. Select the Recovery Model = Full.

7. We generate the script (instructions) for this change. Select the Script Action to New Query Window option in the Script button.

8. Then click the OK button of the Properties window, again changing the recovery model to Full Recovery.

9. In the new Query open window, where the script generated from point 7. is located. Copy this script then to the backups.sql file and then save the changes.

10. In the C:. Directory create a new NWBackups folder.

11. Return to SSMS, in the Object Explorer panel open the Server Objects folder.

12. Right-click on Backup Devices and select New Backup Device.

13. In the new window that appears, fill in the fields / features:

a. Device name => BackupFull

b. Destination File => C: \ NWBackups \ BackupFull.bak

14. We generate the scripting of this configuration. Following the same steps we took in steps 7-9.

15. Points 11-13, to be realized once again for the creation of bakcup devices for the following backups:

o BackupLogs – File – C: \ NWBackups \ BackupLogs.bak

o BackupDiff – File – C: \ NWBackups \ BackupDiff.bak

16. Check if backup devices have been successfully created by opening the Backup Devices folder in the Object Explorer panel.

Posted in C++

admin

Leave a Reply

Your email address will not be published. Required fields are marked *

Next Post

Northwind database

Sun May 31 , 2020
We backup the Northwind database 1. In the Object Explorer panel, right-click on the Backup Devices folder and select Back up a Database. 2. In the window that will open, select the Northwin database in the Database option. 3. Make sure the backup type is FULL. 4. In the Destination […]

You May Like