How do I periodically backup up database in SQL 2008


I am running on an SQL2008 Server and I would like to take a differential backup of my databases each night a midnight.

How do I do that?


Chris Schene
Christopher ScheneSystem Engineer/Software EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bhavesh ShahLead AnalysistCommented:
Steve WalesSenior Database AdministratorCommented:
The link above seems to reference a specific backup tool.

In general you're going to be able to set this up for yourself using a Maintenance Plan.

From SQL Server Management Studio, connect to your Database Engine and on the left side, in the tree under Management you cam right click on Maintenance Plan and select New Maintenance Plan.

You can then drag the Backup Database Task over and set it up for differential backups, and select where you want them to go and schedule when you want them to run.

When running backups this way, you should also be sure to schedule the Maintenance Cleanup Task and History Cleanup Task as well in order to clean up the logs that are left on disk and the old backups files.

You should also be sure to periodically (weekly maybe, or more frequently, up to you) make sure you run a full backup as well - then make sure everything from the last full through to the current differential are always available in case of a restore.

If you're running in Full Recovery mode (or Bulk Logged), also make sure you're backing up your transaction log as well - since that file will grow forever until such time as you back it up.

Some relevant links:

Hope that helps get you started.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Christopher ScheneSystem Engineer/Software EngineerAuthor Commented:
sjwales: I did set up a backup as you suggested but it does not seem to be fully functional

It says the back should run, but I don't see the backup file in C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup has not been updated since my last manual backup

I have attached a screen snapshot: any ideas?
Steve WalesSenior Database AdministratorCommented:
See the bottom line on the left that says SQL Server Agent ?

It has a red stop sign over it.

Your SQL Server Agent isn't running (it is what runs the Maintenance Plan jobs).

Right click on SQL Server Agent and select start.

Wait till the top of the next hour and it should run.  To see if the job runs, you can invoke it manually too.  Start the Agent, expand the tree, expand jobs, right click on the job and select "Start Job at Step.."
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.