Schedule a full back from Microzost SQL 2008

[Bahram]

We are looking for the best way to schedule a ful backup from all the data in
Microsoft sql server 2008.
We have a server farm, and we want to schedule a backup from all the data in
sql 2008 server.
Based on
http://technet.microsoft.com/en-us/library/cc766537(office.12).aspx I
have to create a back from each data base individually .
We have a lot of databases (we using this sql for SharePoint and project
server at the same time), do we need to get a backup from each data base one
by one? Or there is a way to schedule a full back from all the data base over
in Microsoft SQL server 2008.
Please advice.
Thanks

 

[Gary L. Chefetz]

Bahram:

For SQL backups, you can create a SQL Server Maintenance plan to cover
multiple databases.

 

[Ben Howard [MVP]]

In addition to Gary's brilliant answer, you can schedule SharePoint to do a
farm backup (create a batch file that uses the stsadm command to do a farm
backup) or/and use a commercial backup tool that backs up SQL databases.

 

[Bahram]

Before I get the answer fom you guys I found the Maintenance plan solution,
and it worked fine , thanks for your post though.
Now the questions is can I set it up to delete the old backup. I mean if I
set up a daily backup,can I overwrite the new data on the old backup data? if
yes how.

Ben,
Thanks for your post. Because I use the SQL server 2008 for two diffrent
servers (sharepoint & MS project) I am not sure if I get a farm backup with
sharepoint it will get a full back up of my MS project data. I think using
Maintenance plan can help us on this scenario, what do you think?
Thank you guys.

 

[Vaso Vukovic]

Even if your SQL backups work dandy fine you should still consider getting
some stuff backed up via stsadm for sharepoint sites.. the difference being
in the granularity of the backup and hence the restore/impact on the farm.

If lets say you have a site collection holding 500 project server workspaces
and you need to restore site because a single user on a single site messed up
something .. If you rely on SQL then you have to restore the whole contentDB
which impacts everyone on the farm / site / collection plus potential rework
for the user. If you use stsadm for bakcups, you can script it so that it
creates a backup (epxort) file for every individual site, then backup those
files as per normal.. but then if you need to restore a single site to lets
say yesterdays/last good known state you can via those single site backups
and nobody else but users of that site would get impacted and no outages.

you may know this offcourse but just thought mentioning it..

 

[Bahram]

Can I do that part with setting up Expiration Date?

 

[Bahram]

Thank you for point that out. It is a very god point, and I should consider
that. I still need to get a full backup of the whole data base. The reason
why is we are running virtual machines, and we can get a clone of SharePoint
& project server machine, but SQL server is on another machine and because of
the type of the hard drive we can not clone it, so I am looking for a way to
get a full backup of the data and move it to somewhere else.
If we get daily backup we need to delete the old backup files.
I need to keep the very latest data file. So I was wondering if I use
Maintenance plan solution, how can I set it up to where it can delete the old
data when it gets a new backup files. Does SQL server (Maintenance plan) have
this option? I setup expiration date, but I still can see the old backup data.
Thank you again for your reply. It was helpful.

Thanks