schedule backup

Is it possible to schedule backup of all the database on a sql server
while the database is up and running?
If yes, what tool should I use?
Is it reliable? What happens to transactions that are happening while backup
is
in progress?
Thank you,
-MeMe wrote:
> Is it possible to schedule backup of all the database on a sql server
> while the database is up and running?
> If yes, what tool should I use?
> Is it reliable? What happens to transactions that are happening while backup
> is
> in progress?
> Thank you,
> -Me
>
Use the native BACKUP command that SQL provides. Set up a job in SQL
Agent to run that command as needed. Transactions that are in progress
are captured by the transaction log, and either rolled back or committed
during a restore. You should read about the transaction log and
recovery modes so that you fully understand what to backup and when.|||First, if you are managing a SQL Server, you really need to read the section
in BOL ( Books On-Line) on backup and recovery of SQL Databases. Backups
are good, but unless you can restore one when you need to, their usefulness
is somewhat limited.
To answer your specific questions; Yes, you can schedule backups of one or
more SQL databases. The maintenance plan wizard is the easiest, but you can
write your own backup scripts and run them in the SQL Agent if you want more
control and flexibility.
SQL Server uses an internal backup and restore mechanism that guarantees
transactional consistancy. It blocks metadata modifications very briefly to
set up the backup but then runs in a non-blocking mode. Again, reading the
system documentation will help a great deal with understanding the process.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Me" <Me@.discussions.microsoft.com> wrote in message
news:1C9728EE-1856-47BB-9CE7-D678DE1B4A4F@.microsoft.com...
> Is it possible to schedule backup of all the database on a sql server
> while the database is up and running?
> If yes, what tool should I use?
> Is it reliable? What happens to transactions that are happening while
> backup
> is
> in progress?
> Thank you,
> -Me
>|||Hi Geoff,
Thanks for the reply! I scheduled for backup in Enterprise Manager.
But I don't see anything scheduled in scheduler, I hope it goes off.
Another question that came up is I have scheduled for backup from one Server
but my database resides on another server, will my backup work as scheduled?
Actually, I have done backups and restores, but it has been a while since
the time I did it, so I wanted to ensure that I don't do anything wrong.
I will go thru' docs too.
Thank you,
-Me
"Geoff N. Hiten" wrote:
> First, if you are managing a SQL Server, you really need to read the section
> in BOL ( Books On-Line) on backup and recovery of SQL Databases. Backups
> are good, but unless you can restore one when you need to, their usefulness
> is somewhat limited.
> To answer your specific questions; Yes, you can schedule backups of one or
> more SQL databases. The maintenance plan wizard is the easiest, but you can
> write your own backup scripts and run them in the SQL Agent if you want more
> control and flexibility.
> SQL Server uses an internal backup and restore mechanism that guarantees
> transactional consistancy. It blocks metadata modifications very briefly to
> set up the backup but then runs in a non-blocking mode. Again, reading the
> system documentation will help a great deal with understanding the process.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Me" <Me@.discussions.microsoft.com> wrote in message
> news:1C9728EE-1856-47BB-9CE7-D678DE1B4A4F@.microsoft.com...
> > Is it possible to schedule backup of all the database on a sql server
> > while the database is up and running?
> >
> > If yes, what tool should I use?
> >
> > Is it reliable? What happens to transactions that are happening while
> > backup
> > is
> > in progress?
> >
> > Thank you,
> > -Me
> >
>
>|||Hi Tracy,
Thanks for your quick response.
I scheduled for backup in Enterprise Manager.
But I don't see anything scheduled in my PC's scheduler, I hope it goes off.
Another question that came up is I have scheduled for backup from one Server
but my database resides on another server, will my backup work as scheduled?
Actually, I have done backups and restores, but it has been a while since
the time I did it, so I wanted to ensure that I don't do anything wrong.
I will go thru' docs too.
Thank you,
-Me
"Tracy McKibben" wrote:
> Me wrote:
> > Is it possible to schedule backup of all the database on a sql server
> > while the database is up and running?
> >
> > If yes, what tool should I use?
> >
> > Is it reliable? What happens to transactions that are happening while backup
> > is
> > in progress?
> >
> > Thank you,
> > -Me
> >
> Use the native BACKUP command that SQL provides. Set up a job in SQL
> Agent to run that command as needed. Transactions that are in progress
> are captured by the transaction log, and either rolled back or committed
> during a restore. You should read about the transaction log and
> recovery modes so that you fully understand what to backup and when.
>|||"Me" <Me@.discussions.microsoft.com> wrote in message
news:95810B4A-15F2-4F30-9B54-0056279660ED@.microsoft.com...
> Hi Geoff,
> Thanks for the reply! I scheduled for backup in Enterprise Manager.
> But I don't see anything scheduled in scheduler, I hope it goes off.
If you mean scheduled tasks, that's because it's handled by the SQL Agent.
> Another question that came up is I have scheduled for backup from one
Server
> but my database resides on another server, will my backup work as
scheduled?
Umm, not sure how you did this. The way you describe is not possible.
> Actually, I have done backups and restores, but it has been a while since
> the time I did it, so I wanted to ensure that I don't do anything wrong.
Practice practice practice... before the s*** hits the fan and the boss is
leaning over your shoulder. :-)
> I will go thru' docs too.
> Thank you,
> -Me
> "Geoff N. Hiten" wrote:
> > First, if you are managing a SQL Server, you really need to read the
section
> > in BOL ( Books On-Line) on backup and recovery of SQL Databases.
Backups
> > are good, but unless you can restore one when you need to, their
usefulness
> > is somewhat limited.
> >
> > To answer your specific questions; Yes, you can schedule backups of one
or
> > more SQL databases. The maintenance plan wizard is the easiest, but you
can
> > write your own backup scripts and run them in the SQL Agent if you want
more
> > control and flexibility.
> >
> > SQL Server uses an internal backup and restore mechanism that guarantees
> > transactional consistancy. It blocks metadata modifications very
briefly to
> > set up the backup but then runs in a non-blocking mode. Again, reading
the
> > system documentation will help a great deal with understanding the
process.
> >
> > --
> > Geoff N. Hiten
> > Senior Database Administrator
> > Microsoft SQL Server MVP
> >
> >
> >
> > "Me" <Me@.discussions.microsoft.com> wrote in message
> > news:1C9728EE-1856-47BB-9CE7-D678DE1B4A4F@.microsoft.com...
> > > Is it possible to schedule backup of all the database on a sql server
> > > while the database is up and running?
> > >
> > > If yes, what tool should I use?
> > >
> > > Is it reliable? What happens to transactions that are happening while
> > > backup
> > > is
> > > in progress?
> > >
> > > Thank you,
> > > -Me
> > >
> >
> >
> >