Here you can get knowledge regarding of configuration and installation of IT infrastructure.

Monday, April 15, 2013

how to Schedule Backup in SQL

Description:

There are three major types of backups that we explore
on this site: Database Backups, Differential Backups, and Transaction Log
backups. In this exercise we are performing a database backup. We're
also putting this database backup on a recurring schedule.

Setup:

We schedule a job in this exercise, so make sure that the SQL Server Agent is running in order
to successfully complete this exercise.

GUI Steps:

1) Right click on “Backup” from the management folder and
choose “Backup a Database”

2) The SQL Server Backup screen comes up. Choose the
Northwind Database and provide a name for your backup. Choose "Database
- complete" from the Backup section. You must then choose a destination
for your backup to go to. Click the 'Add' button to let us choose
from backup devices.

3) The "Select Backup Destination" screen appears.
Click on the Backup device radio button and choose a backup device.
We're choosing the productionbackups device that we created in the
"Create Backup Device" exercise. Click 'OK' to return to
the SQL Server Backup Menu.

4) We're going to put our backup on a schedule. Left
click on the Schedule box, and then click on the ellipses button to open up the
scheduling options.

5) The Edit Schedule screen appears. Provide a name
for this backup schedule. We're going to do this complete database
backup on a recurring schedule. Click on the 'Recurring' radio button,
then click on the 'Change' button.

6) We're going to set up a daily job that runs at 10:15AM. You'll want to perform your complete
database backups when there is little to no activity on the database, probably
sometime in the wee hours of the morning. We chose daily, occurs at
10:15AM, along with a start date and no end date. Then left click
on 'OK'.

7) You are now returned to the edit schedule screen
which shows in the recurring box that our daily backup has been accepted.
Click on 'OK'.

8) You click ok and are returned to the SQL Server Backup
Screen. Note that all of our work so far has been accepted, including
our recurring schedule that we just added. Click on the options tab
so we can verify our backups.

9) You are now on the Options tab. Click on verify backup
upon completion. This will check the integrity of the backup. The other
options shown as clicked below are on by default. Remove inactive entries
from the transaction log will free up space in the transaction log by removing
transactions that have already completed. Clicking on OK from here
will return us to enterprise manager.

10) Now, if we go to SQL Server Agent, and click on "Jobs"
we'll see that we have
a recurring backup job that has been created.

11) After the job is scheduled to be performed,
we can go to the device that should contain our backup. Double click on
the 'Backup' icon in the management folder. The backup devices appear
in the right hand window. In our example, we put our complete backup in
the production backups folder. Double click on the production backups
folder to see if our backup was successfully completed.

12) We are taken to the 'Backup Device Properties' screen.
Click on the "View Contents" button to see if our backup
is present.

13) The view backup media contents shows two
backups, one of which is the Northwind Backup Complete that we set up in this
exercise (it is highlighted). Note that the date and time were the current
date at 10:15 as we specified. You can click on close to leave this screen
and return to the Backup Device Properties screen (from the previous step).
From the Backup Device Properties Screen you can click on 'OK' to return
to Enterprise Manager.