part0103

You should now see the Define maintenance cleanup task screen, which specifies the historical data to delete.

You can specify a shorter time frame to keep the backup and recovery, agent job history, and maintenance place for on the dropdown.

This screen allows you to back up every page in the database, which has been changed since the last full backup.

Select a database you wish to use and click next.

Define the Back Up Database (Transaction Log) Duty

The transaction log backup backs up all the log records since the last backup.

Performing this type of backup is the least resource-intensive backup.

Select a database and storage location and click next.

The SQL Server Agent Job Task deals with jobs that are outside the wizard. For example, it could be to check for nulls, check whether the database meets specified standards etc.

Any jobs that are specified in SQL Server Agent Job Task are listed here.

This screen defines the clean-up action of the maintenance task i.e. to ensure that they are not taking up unnecessary space, so you can specify where to store them.

The next screen covers where you want to store the report of the maintenance plan.

Make a note of where you are going to store it.

You need to have an email set up on SQL Server in order to email it.

The final screen is a complete review of the wizard.

You can review the summary of the plan and which options were selected.

Clicking finish ends the wizard and creates the plan.

You should now see a success screen with the tasks completed.

Once you successfully complete the maintenance wizard, the next step is to run the plan you created.

In order to get the plan to run, you need to have the SQL Server Agent running.

It is visible two down from where Management is on SQL Server Management Studio.

You can left-click SQL Server Agent and then right-click and select Start.

Also, you can press the Windows key + and press the letter r, then type in services.msc and hit return.

Once Services appear, scroll down and look for SQL Server Agent (MSSQLEXPRESS).

You can install SQL Server Express or select the other versions like (MSSQLSERVER) if you installed that.

Left-click it, then right-click it and select Start.

You can go back to SSMS and right-click on the maintenance plan you created under maintenance plans and then select Execute.

On successful completion of the plan, click ok and close the dialogue box.

You can view the reports by right-clicking the maintenance plan you created and selecting View history.

On the left-hand side are all the different plans in SQL Server while on the right are the results of the specific plan.

A lot of DBA’s like to get their database reports via email.

What you need to do is to set up a database mail before you can fire off emails and then set up a Server agent to send the email.

The first step is to right-click Database mail in SSMS and select configure database mail.

A wizard screen will appear, then click next.

Now select the first choice – set up Database Mail and click next.

Enter a profile name optional description of the profile.

This will bring you to an add New Database Mail Account – SMTP.

You need to enter the STMP details for an email account.

Maybe you can set up a new email account for this service.

You can search online for SMTP details, Gmail works quite well (server name: smtp.gmail.com, port number 587, SSL required, tick basic authentication & confirm password).

Click next, click on public (important: so it can be used by the rest of the database).

Set it as the default profile, click next, click next again.

To send off the database email, you need to set up a Server Agent.

Start by right-clicking on SQL Server Agent – New – Operator.

Give the operator a name like Maintenance Plan Operator and enter in the email address you wish to send the report to and click ok.

Now right the maintenance plan that you have successfully executed and selected modify.

The maintenance plan design screen will appear on the right-hand side, where you can see some graphics of the tasks completed in it.

Now click on Reporting and Logging – it is an icon situated on the menu bar of the design plan - to the left of Manage Connections.

The Reporting and Logging window will appear.

Select the tick box – Send report to an email recipient and select the Maintenance plan operator you just created.

The next time you run the plan, an email will be sent to the email address.

The running and maintenance of a database is an important job.

Having the right plan for your database means it will continue to work as originally designed, and you can quickly identify database errors or slowdowns early on and fix them quickly.

The most important task a DBA can perform is to back up the database.

When you create a maintenance plan, it’s important to have it top of the maintenance list in case the job doesn’t get fully completed.

Firstly, it is important to understand the transaction log and why it is important.

Whenever a change is made to the database, be it a transaction or modification, it is stored in the transaction log.

The transaction log is the most important file in a SQL Server database, and everything resolves around either saving it or using it.

Every transaction log can facilitate transaction recovery, recovery of all incomplete transactions, rolling forward a restored file, filegroup or page to the point of failure, transactional replication, disaster recovery.

The first step in backing up a database is choosing a recovery option for the database.

You can perform the three types of backups when SQL Server is online and even while users are making requests from the database.

When you backup and restore in SQL Server, you do so in the context of the recovery model, which are models designed to control the maintenance of the transactional log.

The recovery model is a database property that controls how transactions are logged.

There are three different recovery options: Simple, Full, and Bulk Logged.

You cannot back up the transactional log when utilizing the simple recovery model.

Usually, this model is used where updates are infrequent.

Transactions are logged to a minimum, and the log will be truncated.

In the full recovery model, the transaction log backup must be taken.

Only when the backup process begins will the transactional log be truncated.

However, you also need the full chain of log files to restore the database to the nearest time possible.

This model is designed to be utilized for short term use when you use a bulk import operation.

You use it along with the full recovery model whenever you don’t need a certain point in time recovery.

It has performance gains and also doesn’t fill up the transaction log.

To change the recovery model, you can right-click on a database in SQL Server Management Studio and selecting properties, then select options and then select the recovery mode from the drop-down box.

ALTER DATABASE SQLEbook SET RECOVERY BULK_LOGGED

There are three types of backup: full, differential, and transaction log:

When you create a full backup, SQL Server creates a CHECKPOINT, which ensures than any dirty page that exists is written to disk.

Then SQL Server backs up each and every page on the database.

It then backs up the majority of the transaction log to ensure there is transactional consistency.

What all of this means is that you are able to restore your database to a most recent point and have all the transactions, including those right up to the very beginning of the backup.

The differential backup, as its name suggests, backs up every page in the database which has since been modified since the last backup.

SQL Server keeps track of all the different pages that have been modified via flags and DIFF pages.

With the log backup, SQL Server backs up the data in the transaction log only, i.e. only the transactions that were recently committed to the database.

The transaction log is not as resource hungry and is considered important because it can perform backups more often without having an impact on database performance

When Database Administrator sets out a backup plan, they base their plan on two measures: RTO - Recovery Time Objective and RPO - Recovery Point Objective.

The RTO reflects the period taken to recover after notification of a disruption in the business process.

RPO measures the timeframe that might pass during a disruption before the data size that has been lost exceeds the maximum limit of the business process.

If there was an RPO of 60 minutes, you couldn’t achieve this goal if the backup was set to every 24 hours.

You need to set your backup plan based on these two measures.

Exercising this alone is the least flexible option.

Essentially your only able to restore your database back to one point of time, which is the last full back up.

So, if the database went corrupt two hours from midnight (and you backup at midnight), your RPO would be twenty-two hours.

Also, if a user truncated a table two hours from midnight, you would have the same twenty-two-hour loss of business transactions.

If you have selected Full Recovery mode, you can run both full backups and transactional log file backups.

You can run more frequent backups since running Transaction Log backup takes fewer resources.

This is a very good choice if your database is updated throughout the day.

When you are scheduling transactional log backups, it is best to follow the RPO.

Thus, if you have an RPO of 60 minutes, then set the log file backups to 60 minutes.

However, you must check the RTO for such a backup.

If you had an RPO of 60 minutes and are only performing a full back up once a week, you might not be able to restore all 330 backups in the allotted time.

To get around the problem mentioned above, you can add differential backups to the plan.

A differential backup is cumulative, which means a serious reduction in the number of backups you would have to restore to recover your database to the point just before failure.

To back up a database, right-click the database in SSMS then select Tasks-> Backup.

You can select what kind of backup (full, differential, or transaction log) to perform and when to perform a backup.

The copy-only backup allows you to perform a backup, which doesn’t affect the restore sequence.

When you want to restore a database in SSMS, right-click the database, then select Tasks -> Restore -> Database.

You can select the database from the drop-down, and thus, the rest of the tabs will be populated.

If you click on Timeline, you will see a graphical diagram of when the last backup was created, which shows how much data was lost.

You can recover to the end of the log or a specific date and time.

The Verify Backup Timeline media button enables you to verify the backup media before you actually restore it.

If you want to change where you are going to store the backup, you can click on the Files to select a different location.

You can specify the restore options that you are going to use in the Options page.

Either overwrite the existing database or keep it.

The recovery state either brings the database online or allows further backups to be applied.

Once you click OK on the bottom, the database will be Restored.