When
using SQL Server to maintain and manage a database, the integrity, accuracy and
availability of that data if often critical to the success of a business.
After the
appropriate menu is available, from the All Tasks menu item select the Backup
Database sub menu item. This will initiate the SQL Server Backup Database
operation so that the parameters needed to perform the backup can be entered
into the database.
This will bring up the following screen to begin the entry of the information needed to perform the backup of the database:
The first option in the screen is to specify the database that is to be backed up and the name of the backup as well as a description of the backup.
A name and description are necessary in order to help identify saved SQL Server backup plans in the database. After this information is entered into the window, the backup type must be selected in order to specify what type of backup is to be performed.
There are four backup operations that can occur within the database. This can be a complete database backup, a differential database backup, a transaction log or file/filegroup backup.
A complete database backup will back the entire database structure up to the device specified.
It is a complete copy of the database selected and will include everything in the database including the data and the objects.
A differential backup is another option. It deals with only backing up the database and its’ changes since the last full database backup was performed. A transaction log backup will backup the current transaction log so it can be restored at a later date.
The transaction log is a log of the transactions that have taken place in the database. A file or file group backup deals with making a SQL Server backup of a specific part of the files or file groups involved with the make up of the database.
Once the desired backup type is entered, a destination for the database backup must be input into the equation. This will inform the database where the backup is to be saved. The backup can be saved to a disk or to a tape backup device.
To enter a backup device to save the backup to, click the Add button to open a dialog that will allow for the selection of the backup location and the device to hold the backup.
After finalizing the selection of the backup destination, the choice must be made whether to append to the existing media or overwrite the existing media with the result of the backup.
Once all of these options have been entered, the database backup can be scheduled in order to make this a process that occurs on a regular basis. Scheduling a backup procedure in SQL Server puts the workload on the server to execute the backup on routine basis and does not force a database administrator to perform the backups.
The only job that someone has concerning the SQL Server backups are to either routinely check them for success or configure the server to send alerts to users when specific actions occur on the server.
To schedule the backup of the database, select the checkbox and then click the command button to bring up the scheduling dialog window.
The following screen shows this operation:
The
options on this screen deal with the timing of the SQL Server backup and the
conditions that need to be met before the server is to run the database backup.
The schedule can be given a name to help distinguish it within the database. It can also be enabled or disabled using the Enabled checkbox option.
There are four different scheduling options that can be used within the system to determine when to run a job within SQL Server.
These four options are to start when agent starts, start when the CPU is idle, a one time or to
Create a Recurring Job:
The first option will start the job whenever SQL Server Agent starts. SQL Server Agent is a service that runs specific tasks within the database management system.
The second option is to start the job when the CPU becomes idle.
The third option is to create a job that occurs once at a specific date and time.
The final option is to create a SQL Server backup job that occurs on a routine basis that can be scheduled to occur as often and whenever it is necessary.
The following screen shows the job scheduling options available to handling recurring jobs
If
data becomes unavailable or the database becomes inaccessible to the users of a
system, a business can be in a lot of trouble.
SQL
Server has the ability to make backups of databases so that the data can be
restored in case something tragic happens to the database. A SQL Server backup
will play a critical part to any disaster recovery plan in case an emergency
does occur.
In
order to backup a SQL Server database using Enterprise Manager, right click on
the database that you want to back up. The following illustration shows the
menu options needed to navigate to the necessary option.
This will bring up the following screen to begin the entry of the information needed to perform the backup of the database:
The first option in the screen is to specify the database that is to be backed up and the name of the backup as well as a description of the backup.
A name and description are necessary in order to help identify saved SQL Server backup plans in the database. After this information is entered into the window, the backup type must be selected in order to specify what type of backup is to be performed.
There are four backup operations that can occur within the database. This can be a complete database backup, a differential database backup, a transaction log or file/filegroup backup.
A complete database backup will back the entire database structure up to the device specified.
It is a complete copy of the database selected and will include everything in the database including the data and the objects.
A differential backup is another option. It deals with only backing up the database and its’ changes since the last full database backup was performed. A transaction log backup will backup the current transaction log so it can be restored at a later date.
The transaction log is a log of the transactions that have taken place in the database. A file or file group backup deals with making a SQL Server backup of a specific part of the files or file groups involved with the make up of the database.
Once the desired backup type is entered, a destination for the database backup must be input into the equation. This will inform the database where the backup is to be saved. The backup can be saved to a disk or to a tape backup device.
To enter a backup device to save the backup to, click the Add button to open a dialog that will allow for the selection of the backup location and the device to hold the backup.
After finalizing the selection of the backup destination, the choice must be made whether to append to the existing media or overwrite the existing media with the result of the backup.
Once all of these options have been entered, the database backup can be scheduled in order to make this a process that occurs on a regular basis. Scheduling a backup procedure in SQL Server puts the workload on the server to execute the backup on routine basis and does not force a database administrator to perform the backups.
The only job that someone has concerning the SQL Server backups are to either routinely check them for success or configure the server to send alerts to users when specific actions occur on the server.
To schedule the backup of the database, select the checkbox and then click the command button to bring up the scheduling dialog window.
The following screen shows this operation:
The schedule can be given a name to help distinguish it within the database. It can also be enabled or disabled using the Enabled checkbox option.
There are four different scheduling options that can be used within the system to determine when to run a job within SQL Server.
These four options are to start when agent starts, start when the CPU is idle, a one time or to
Create a Recurring Job:
The first option will start the job whenever SQL Server Agent starts. SQL Server Agent is a service that runs specific tasks within the database management system.
The second option is to start the job when the CPU becomes idle.
The third option is to create a job that occurs once at a specific date and time.
The final option is to create a SQL Server backup job that occurs on a routine basis that can be scheduled to occur as often and whenever it is necessary.
The following screen shows the job scheduling options available to handling recurring jobs
Once
the job schedule is created, clicking OK will accept the changes and create a
job within the database.
This
job is responsible for the backup and is run by SQL Server Agent whenever the
scheduled time occurs. When the scheduled time occurs on the server, SQL Server
Agent takes over and runs the job and the steps associated with it.
While
running the job, the status of the SQL Server backup is updated to alert the
user of the database what is occurring with the job. Each step and operation is
logged in the database and updated in the job history tables stored in the
database.
No comments:
Post a Comment