schedule backup

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 back
up
> 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 secti
on
> 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 usefulnes
s
> is somewhat limited.
> To answer your specific questions; Yes, you can schedule backups of one o
r
> more SQL databases. The maintenance plan wizard is the easiest, but you c
an
> write your own backup scripts and run them in the SQL Agent if you want mo
re
> 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 th
e
> 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...
>
>|||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:
> 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.
>|||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|||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 back
up
> 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 secti
on
> 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 usefulnes
s
> is somewhat limited.
> To answer your specific questions; Yes, you can schedule backups of one o
r
> more SQL databases. The maintenance plan wizard is the easiest, but you c
an
> write your own backup scripts and run them in the SQL Agent if you want mo
re
> 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 th
e
> 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...
>
>|||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:
> 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. :-)
[vbcol=seagreen]
> I will go thru' docs too.
> Thank you,
> -Me
> "Geoff N. Hiten" wrote:
>
section[vbcol=seagreen]
Backups[vbcol=seagreen]
usefulness[vbcol=seagreen]
or[vbcol=seagreen]
can[vbcol=seagreen]
more[vbcol=seagreen]
briefly to[vbcol=seagreen]
the[vbcol=seagreen]
process.[vbcol=seagreen]