How to create a Maintenance Plan and backup to multiple locations

Hi all,
Currently, I have a maintenance plan and it creates a complete backup to the
default backup directory on the local machine.
What should I do if I also want to back up the database to another
machine/share drive on the same domain? Do I need to create a new
maintenance plan just for that? Could this create unnecessary performance
hit on the SQL Server?
Or could I go directly to the SQL job and modify the script so that it can
back up to multiple locations? I donâ't know how to do this or whether it
will work at all.
Sure I can create a NT backup to copy the database to another location. But
I really like the "Remove file older than..." feature on the maintenance
plan. Our database is pretty big and the currently backup mechanism faces
space issue without this "Remove file older than..." feature.
Any advice? And thanks in advance.
Abel ChanYou can send the backup file anywhere you want as long as the account that
SQL Server runs under has permissions to that share or folder. But with
SQL2000 you can not backup to two locations at once. SQL2005 gives you more
options in that area. It is rarely a good idea to backup to the default
directory since if that machine dies you loose botht he db and the most
current backup. You might want to think about backing up to a remote share.
Just use a UNC for the path in the MP wizard (or edit the job) and you
should be all set.
--
Andrew J. Kelly SQL MVP
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:341680AD-9EF2-4574-9BF6-CE4E391DF2D8@.microsoft.com...
> Hi all,
> Currently, I have a maintenance plan and it creates a complete backup to
> the
> default backup directory on the local machine.
> What should I do if I also want to back up the database to another
> machine/share drive on the same domain? Do I need to create a new
> maintenance plan just for that? Could this create unnecessary performance
> hit on the SQL Server?
> Or could I go directly to the SQL job and modify the script so that it can
> back up to multiple locations? I don't know how to do this or whether it
> will work at all.
> Sure I can create a NT backup to copy the database to another location.
> But
> I really like the "Remove file older than..." feature on the maintenance
> plan. Our database is pretty big and the currently backup mechanism faces
> space issue without this "Remove file older than..." feature.
> Any advice? And thanks in advance.
> Abel Chan
>|||Hi Andrew,
Thanks to your response. I took your suggestion and back up the database to
another media. :> I also found that I need to run the SQL server under a
domain account instead of Local System in order to use UNC path. Thanks
again.
Abel Chan
"Andrew J. Kelly" wrote:
> You can send the backup file anywhere you want as long as the account that
> SQL Server runs under has permissions to that share or folder. But with
> SQL2000 you can not backup to two locations at once. SQL2005 gives you more
> options in that area. It is rarely a good idea to backup to the default
> directory since if that machine dies you loose botht he db and the most
> current backup. You might want to think about backing up to a remote share.
> Just use a UNC for the path in the MP wizard (or edit the job) and you
> should be all set.
> --
> Andrew J. Kelly SQL MVP
>
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:341680AD-9EF2-4574-9BF6-CE4E391DF2D8@.microsoft.com...
> > Hi all,
> >
> > Currently, I have a maintenance plan and it creates a complete backup to
> > the
> > default backup directory on the local machine.
> >
> > What should I do if I also want to back up the database to another
> > machine/share drive on the same domain? Do I need to create a new
> > maintenance plan just for that? Could this create unnecessary performance
> > hit on the SQL Server?
> >
> > Or could I go directly to the SQL job and modify the script so that it can
> > back up to multiple locations? I don't know how to do this or whether it
> > will work at all.
> >
> > Sure I can create a NT backup to copy the database to another location.
> > But
> > I really like the "Remove file older than..." feature on the maintenance
> > plan. Our database is pretty big and the currently backup mechanism faces
> > space issue without this "Remove file older than..." feature.
> >
> > Any advice? And thanks in advance.
> >
> > Abel Chan
> >
>
>