Hi ,
Can someone provide me futher info about Backup Diffrential ? Is it need
to be perform after the full backup ? How the SQL server detect there is the
changes of the tables ?
1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
all the data available in the database
2) If I backup differential on the Pubs mdb also , do I need to use back
the same backup name ? If it is , the C:\Pubs_Backup will only consist the
latest updated data.
The original full data will be lost . It is true ?
Travis Tan
Travis
Have you read examples and articles in the BOL? It has very good examples.
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:8F64EC9D-49AF-4DB7-BDCB-E21F6A5ED700@.microsoft.com...
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it
need
> to be perform after the full backup ? How the SQL server detect there is
the
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use
back
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
> --
> Travis Tan
|||Travis wrote:
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it need
> to be perform after the full backup ? How the SQL server detect there is the
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use back
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
>
look in BooksOnLine (BOL), it's all there
|||Travis,
In order to restore the db, you will need a full backup and the
differential, if there is one, plus all log backups after the diff one, or if
the sequence of log backups have not been broken, you can use the last full
backup and all transactions log backups. But time is precious and
differential backups help you save time.
A diffenrential backup is useful when the full backup of your db take long
time, so if you need to backup your data twice daily and the full backup take
12 hours, you can take one full backup once a week and two diff backups
daily, saving all changes from the last full backup.
As the others members said, BOL has good samples. You can also find some
good articles about this theme in Tibor's web page.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Travis" wrote:
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it need
> to be perform after the full backup ? How the SQL server detect there is the
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use back
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
> --
> Travis Tan
|||Differential Database Backups
A differential database backup records only the data that has changed since
the last database backup. You can make more frequent backups because
differential database backups are smaller and faster than database backups.
Making frequent backups decreases your risk of losing data.
Note If you have created any file backups since the last full database
backup, those files will be scanned by Microsoft? SQL Server? 2000 at the
beginning of a differential database backup. This may cause some degradation
of performance in the differential database backup. For more information, see
Using File Backups.
You use differential database backups to restore the database to the point
at which the differential database backup was completed. To recover to the
exact point of failure, you must use transaction log backups. For more
information, see Transaction Log Backups.
Consider using differential database backups when:
Only a relatively small portion of the data in the database has changed
since the last database backup. Differential database backups are
particularly effective if the same data is modified many times.
You are using the Simple Recovery model and want more frequent backups, but
don't want to do frequent full database backups.
You are using the Full or Bulk-Logged Recovery model and want to minimize
the time it takes to roll forward transaction log backups when restoring a
database.
A recommended process for implementing differential database backups is:
Create regular database backups.
Create a differential database backup periodically between database backups,
such as every four hours or more for highly active systems.
If using Full or Bulk-Logged Recovery, create transaction log backups more
frequently than differential database backups, such as every 30 minutes.
The sequence for restoring differential database backups is:
Restore the most recent database backup.
Restore the last differential database backup.
Apply all transaction log backups created after the last differential
database backup was created if you use Full or Bulk-Logged Recovery.
For more information on differential database backups go here:
http://msdn.microsoft.com/library/de...kprst_7tv7.asp
"Travis" wrote:
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it need
> to be perform after the full backup ? How the SQL server detect there is the
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use back
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
> --
> Travis Tan
Showing posts with label differential. Show all posts
Showing posts with label differential. Show all posts
Thursday, March 22, 2012
Backup Differential
Hi ,
Can someone provide me futher info about Backup Diffrential ? Is it need
to be perform after the full backup ? How the SQL server detect there is the
changes of the tables ?
1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
all the data available in the database
2) If I backup differential on the Pubs mdb also , do I need to use back
the same backup name ? If it is , the C:\Pubs_Backup will only consist the
latest updated data.
The original full data will be lost . It is true ?
Travis TanTravis
Have you read examples and articles in the BOL? It has very good examples.
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:8F64EC9D-49AF-4DB7-BDCB-E21F6A5ED700@.microsoft.com...
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it
need
> to be perform after the full backup ? How the SQL server detect there is
the
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use
back
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
> --
> Travis Tan|||Travis wrote:
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it ne
ed
> to be perform after the full backup ? How the SQL server detect there is t
he
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use bac
k
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
>
look in BooksOnLine (BOL), it's all there|||Travis,
In order to restore the db, you will need a full backup and the
differential, if there is one, plus all log backups after the diff one, or i
f
the sequence of log backups have not been broken, you can use the last full
backup and all transactions log backups. But time is precious and
differential backups help you save time.
A diffenrential backup is useful when the full backup of your db take long
time, so if you need to backup your data twice daily and the full backup tak
e
12 hours, you can take one full backup once a week and two diff backups
daily, saving all changes from the last full backup.
As the others members said, BOL has good samples. You can also find some
good articles about this theme in Tibor's web page.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Travis" wrote:
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it ne
ed
> to be perform after the full backup ? How the SQL server detect there is t
he
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use bac
k
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
> --
> Travis Tan|||Differential Database Backups
A differential database backup records only the data that has changed since
the last database backup. You can make more frequent backups because
differential database backups are smaller and faster than database backups.
Making frequent backups decreases your risk of losing data.
Note If you have created any file backups since the last full database
backup, those files will be scanned by Microsoft? SQL Server? 2000 at the
beginning of a differential database backup. This may cause some degradation
of performance in the differential database backup. For more information, se
e
Using File Backups.
You use differential database backups to restore the database to the point
at which the differential database backup was completed. To recover to the
exact point of failure, you must use transaction log backups. For more
information, see Transaction Log Backups.
Consider using differential database backups when:
Only a relatively small portion of the data in the database has changed
since the last database backup. Differential database backups are
particularly effective if the same data is modified many times.
You are using the Simple Recovery model and want more frequent backups, but
don't want to do frequent full database backups.
You are using the Full or Bulk-Logged Recovery model and want to minimize
the time it takes to roll forward transaction log backups when restoring a
database.
A recommended process for implementing differential database backups is:
Create regular database backups.
Create a differential database backup periodically between database backups,
such as every four hours or more for highly active systems.
If using Full or Bulk-Logged Recovery, create transaction log backups more
frequently than differential database backups, such as every 30 minutes.
The sequence for restoring differential database backups is:
Restore the most recent database backup.
Restore the last differential database backup.
Apply all transaction log backups created after the last differential
database backup was created if you use Full or Bulk-Logged Recovery.
For more information on differential database backups go here:
http://msdn.microsoft.com/library/d... />
t_7tv7.asp
"Travis" wrote:
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it ne
ed
> to be perform after the full backup ? How the SQL server detect there is t
he
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use bac
k
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
> --
> Travis Tan
Can someone provide me futher info about Backup Diffrential ? Is it need
to be perform after the full backup ? How the SQL server detect there is the
changes of the tables ?
1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
all the data available in the database
2) If I backup differential on the Pubs mdb also , do I need to use back
the same backup name ? If it is , the C:\Pubs_Backup will only consist the
latest updated data.
The original full data will be lost . It is true ?
Travis TanTravis
Have you read examples and articles in the BOL? It has very good examples.
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:8F64EC9D-49AF-4DB7-BDCB-E21F6A5ED700@.microsoft.com...
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it
need
> to be perform after the full backup ? How the SQL server detect there is
the
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use
back
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
> --
> Travis Tan|||Travis wrote:
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it ne
ed
> to be perform after the full backup ? How the SQL server detect there is t
he
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use bac
k
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
>
look in BooksOnLine (BOL), it's all there|||Travis,
In order to restore the db, you will need a full backup and the
differential, if there is one, plus all log backups after the diff one, or i
f
the sequence of log backups have not been broken, you can use the last full
backup and all transactions log backups. But time is precious and
differential backups help you save time.
A diffenrential backup is useful when the full backup of your db take long
time, so if you need to backup your data twice daily and the full backup tak
e
12 hours, you can take one full backup once a week and two diff backups
daily, saving all changes from the last full backup.
As the others members said, BOL has good samples. You can also find some
good articles about this theme in Tibor's web page.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Travis" wrote:
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it ne
ed
> to be perform after the full backup ? How the SQL server detect there is t
he
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use bac
k
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
> --
> Travis Tan|||Differential Database Backups
A differential database backup records only the data that has changed since
the last database backup. You can make more frequent backups because
differential database backups are smaller and faster than database backups.
Making frequent backups decreases your risk of losing data.
Note If you have created any file backups since the last full database
backup, those files will be scanned by Microsoft? SQL Server? 2000 at the
beginning of a differential database backup. This may cause some degradation
of performance in the differential database backup. For more information, se
e
Using File Backups.
You use differential database backups to restore the database to the point
at which the differential database backup was completed. To recover to the
exact point of failure, you must use transaction log backups. For more
information, see Transaction Log Backups.
Consider using differential database backups when:
Only a relatively small portion of the data in the database has changed
since the last database backup. Differential database backups are
particularly effective if the same data is modified many times.
You are using the Simple Recovery model and want more frequent backups, but
don't want to do frequent full database backups.
You are using the Full or Bulk-Logged Recovery model and want to minimize
the time it takes to roll forward transaction log backups when restoring a
database.
A recommended process for implementing differential database backups is:
Create regular database backups.
Create a differential database backup periodically between database backups,
such as every four hours or more for highly active systems.
If using Full or Bulk-Logged Recovery, create transaction log backups more
frequently than differential database backups, such as every 30 minutes.
The sequence for restoring differential database backups is:
Restore the most recent database backup.
Restore the last differential database backup.
Apply all transaction log backups created after the last differential
database backup was created if you use Full or Bulk-Logged Recovery.
For more information on differential database backups go here:
http://msdn.microsoft.com/library/d... />
t_7tv7.asp
"Travis" wrote:
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it ne
ed
> to be perform after the full backup ? How the SQL server detect there is t
he
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use bac
k
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
> --
> Travis Tan
Backup differential
Hello everybody,
I need to get a differential backup in a small file but i am getting a
trouble ...
I got a Full backup with size=800MB
Then i got a differential backup with size=775B
It is an OLAP database then a lot of tables got a "DELETE FROM" command then
data is inserted again, it happens every early morning.
I hope it is correct, but i am not sure, i need some help with that.Leandro
Look at this very useful article
http://vyaskn.tripod.com/ sql_serve...r />
.htm#Step1
--administaiting best practices
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#4ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> Hello everybody,
> I need to get a differential backup in a small file but i am getting a
> trouble ...
> I got a Full backup with size=800MB
> Then i got a differential backup with size=775B
> It is an OLAP database then a lot of tables got a "DELETE FROM" command
then
> data is inserted again, it happens every early morning.
> I hope it is correct, but i am not sure, i need some help with that.
>|||I have to say that this database is Recovery Model SIMPLE ...
"Leandro Loureiro dos Santos" <leandro@.email.com> escreveu na mensagem
news:%234ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> Hello everybody,
> I need to get a differential backup in a small file but i am getting a
> trouble ...
> I got a Full backup with size=800MB
> Then i got a differential backup with size=775B
> It is an OLAP database then a lot of tables got a "DELETE FROM" command
then
> data is inserted again, it happens every early morning.
> I hope it is correct, but i am not sure, i need some help with that.
>|||Well, if you delete from and insert into many tables, then you are modifying
many extents, so diff backup is nearly as big as full db backup. Can you
change your data population procedure to incrementaly update the OLAP
database? Or at least to delete only a subset of rows, for example younger
than a month, and then insert only new rows?
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#4ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> Hello everybody,
> I need to get a differential backup in a small file but i am getting a
> trouble ...
> I got a Full backup with size=800MB
> Then i got a differential backup with size=775B
> It is an OLAP database then a lot of tables got a "DELETE FROM" command
then
> data is inserted again, it happens every early morning.
> I hope it is correct, but i am not sure, i need some help with that.
>|||If i keep the ETL this way i will have the same problem with Replication?
Thanks ...
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> escreveu
na mensagem news:udmeT3U9DHA.3380@.tk2msftngp13.phx.gbl...
> Well, if you delete from and insert into many tables, then you are
modifying
> many extents, so diff backup is nearly as big as full db backup. Can you
> change your data population procedure to incrementaly update the OLAP
> database? Or at least to delete only a subset of rows, for example younger
> than a month, and then insert only new rows?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
> news:#4ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> then
>|||> If i keep the ETL this way i will have the same problem with Replication?
> Thanks ...
Sorry, I don't understand - where does replication fit here? Are you
replicating OLAP database? Which type of replication are you using? Anyway,
replication should gain from smaller updates as well.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||Yes, if you replicate (having deleted then re-inserted many rows), the
replication will go very slowly, as it must do the same...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#4ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> Hello everybody,
> I need to get a differential backup in a small file but i am getting a
> trouble ...
> I got a Full backup with size=800MB
> Then i got a differential backup with size=775B
> It is an OLAP database then a lot of tables got a "DELETE FROM" command
then
> data is inserted again, it happens every early morning.
> I hope it is correct, but i am not sure, i need some help with that.
>|||Leandro
With a datawarehouse (OLAP) system where most of the data
is changing on a daily basis, it might be worth looking at
the source of your data. That is where your backup and
recovery should be pursued. Supplying a new input file to
your system looks to be almost as good as restoring a full
backup.
It is not uncommon with datawarehouses that it is as easy
to recover the database from source as a backup. This is
why it is the exception to the rule that all production
databases should be in full recovery mode.
Regards
John
I need to get a differential backup in a small file but i am getting a
trouble ...
I got a Full backup with size=800MB
Then i got a differential backup with size=775B
It is an OLAP database then a lot of tables got a "DELETE FROM" command then
data is inserted again, it happens every early morning.
I hope it is correct, but i am not sure, i need some help with that.Leandro
Look at this very useful article
http://vyaskn.tripod.com/ sql_serve...r />
.htm#Step1
--administaiting best practices
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#4ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> Hello everybody,
> I need to get a differential backup in a small file but i am getting a
> trouble ...
> I got a Full backup with size=800MB
> Then i got a differential backup with size=775B
> It is an OLAP database then a lot of tables got a "DELETE FROM" command
then
> data is inserted again, it happens every early morning.
> I hope it is correct, but i am not sure, i need some help with that.
>|||I have to say that this database is Recovery Model SIMPLE ...
"Leandro Loureiro dos Santos" <leandro@.email.com> escreveu na mensagem
news:%234ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> Hello everybody,
> I need to get a differential backup in a small file but i am getting a
> trouble ...
> I got a Full backup with size=800MB
> Then i got a differential backup with size=775B
> It is an OLAP database then a lot of tables got a "DELETE FROM" command
then
> data is inserted again, it happens every early morning.
> I hope it is correct, but i am not sure, i need some help with that.
>|||Well, if you delete from and insert into many tables, then you are modifying
many extents, so diff backup is nearly as big as full db backup. Can you
change your data population procedure to incrementaly update the OLAP
database? Or at least to delete only a subset of rows, for example younger
than a month, and then insert only new rows?
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#4ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> Hello everybody,
> I need to get a differential backup in a small file but i am getting a
> trouble ...
> I got a Full backup with size=800MB
> Then i got a differential backup with size=775B
> It is an OLAP database then a lot of tables got a "DELETE FROM" command
then
> data is inserted again, it happens every early morning.
> I hope it is correct, but i am not sure, i need some help with that.
>|||If i keep the ETL this way i will have the same problem with Replication?
Thanks ...
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> escreveu
na mensagem news:udmeT3U9DHA.3380@.tk2msftngp13.phx.gbl...
> Well, if you delete from and insert into many tables, then you are
modifying
> many extents, so diff backup is nearly as big as full db backup. Can you
> change your data population procedure to incrementaly update the OLAP
> database? Or at least to delete only a subset of rows, for example younger
> than a month, and then insert only new rows?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
> news:#4ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> then
>|||> If i keep the ETL this way i will have the same problem with Replication?
> Thanks ...
Sorry, I don't understand - where does replication fit here? Are you
replicating OLAP database? Which type of replication are you using? Anyway,
replication should gain from smaller updates as well.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||Yes, if you replicate (having deleted then re-inserted many rows), the
replication will go very slowly, as it must do the same...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#4ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> Hello everybody,
> I need to get a differential backup in a small file but i am getting a
> trouble ...
> I got a Full backup with size=800MB
> Then i got a differential backup with size=775B
> It is an OLAP database then a lot of tables got a "DELETE FROM" command
then
> data is inserted again, it happens every early morning.
> I hope it is correct, but i am not sure, i need some help with that.
>|||Leandro
With a datawarehouse (OLAP) system where most of the data
is changing on a daily basis, it might be worth looking at
the source of your data. That is where your backup and
recovery should be pursued. Supplying a new input file to
your system looks to be almost as good as restoring a full
backup.
It is not uncommon with datawarehouses that it is as easy
to recover the database from source as a backup. This is
why it is the exception to the rule that all production
databases should be in full recovery mode.
Regards
John
Backup Differential
Hi ,
Can someone provide me futher info about Backup Diffrential ? Is it need
to be perform after the full backup ? How the SQL server detect there is the
changes of the tables ?
1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
all the data available in the database
2) If I backup differential on the Pubs mdb also , do I need to use back
the same backup name ? If it is , the C:\Pubs_Backup will only consist the
latest updated data.
The original full data will be lost . It is true ?
--
Travis TanTravis
Have you read examples and articles in the BOL? It has very good examples.
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:8F64EC9D-49AF-4DB7-BDCB-E21F6A5ED700@.microsoft.com...
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it
need
> to be perform after the full backup ? How the SQL server detect there is
the
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use
back
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
> --
> Travis Tan|||Travis wrote:
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it need
> to be perform after the full backup ? How the SQL server detect there is the
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use back
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
>
look in BooksOnLine (BOL), it's all there|||Travis,
In order to restore the db, you will need a full backup and the
differential, if there is one, plus all log backups after the diff one, or if
the sequence of log backups have not been broken, you can use the last full
backup and all transactions log backups. But time is precious and
differential backups help you save time.
A diffenrential backup is useful when the full backup of your db take long
time, so if you need to backup your data twice daily and the full backup take
12 hours, you can take one full backup once a week and two diff backups
daily, saving all changes from the last full backup.
As the others members said, BOL has good samples. You can also find some
good articles about this theme in Tibor's web page.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Travis" wrote:
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it need
> to be perform after the full backup ? How the SQL server detect there is the
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use back
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
> --
> Travis Tan|||Differential Database Backups
A differential database backup records only the data that has changed since
the last database backup. You can make more frequent backups because
differential database backups are smaller and faster than database backups.
Making frequent backups decreases your risk of losing data.
Note If you have created any file backups since the last full database
backup, those files will be scanned by Microsoft® SQL Server� 2000 at the
beginning of a differential database backup. This may cause some degradation
of performance in the differential database backup. For more information, see
Using File Backups.
You use differential database backups to restore the database to the point
at which the differential database backup was completed. To recover to the
exact point of failure, you must use transaction log backups. For more
information, see Transaction Log Backups.
Consider using differential database backups when:
Only a relatively small portion of the data in the database has changed
since the last database backup. Differential database backups are
particularly effective if the same data is modified many times.
You are using the Simple Recovery model and want more frequent backups, but
don't want to do frequent full database backups.
You are using the Full or Bulk-Logged Recovery model and want to minimize
the time it takes to roll forward transaction log backups when restoring a
database.
A recommended process for implementing differential database backups is:
Create regular database backups.
Create a differential database backup periodically between database backups,
such as every four hours or more for highly active systems.
If using Full or Bulk-Logged Recovery, create transaction log backups more
frequently than differential database backups, such as every 30 minutes.
The sequence for restoring differential database backups is:
Restore the most recent database backup.
Restore the last differential database backup.
Apply all transaction log backups created after the last differential
database backup was created if you use Full or Bulk-Logged Recovery.
For more information on differential database backups go here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_7tv7.asp
"Travis" wrote:
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it need
> to be perform after the full backup ? How the SQL server detect there is the
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use back
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
> --
> Travis Tan
Can someone provide me futher info about Backup Diffrential ? Is it need
to be perform after the full backup ? How the SQL server detect there is the
changes of the tables ?
1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
all the data available in the database
2) If I backup differential on the Pubs mdb also , do I need to use back
the same backup name ? If it is , the C:\Pubs_Backup will only consist the
latest updated data.
The original full data will be lost . It is true ?
--
Travis TanTravis
Have you read examples and articles in the BOL? It has very good examples.
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:8F64EC9D-49AF-4DB7-BDCB-E21F6A5ED700@.microsoft.com...
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it
need
> to be perform after the full backup ? How the SQL server detect there is
the
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use
back
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
> --
> Travis Tan|||Travis wrote:
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it need
> to be perform after the full backup ? How the SQL server detect there is the
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use back
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
>
look in BooksOnLine (BOL), it's all there|||Travis,
In order to restore the db, you will need a full backup and the
differential, if there is one, plus all log backups after the diff one, or if
the sequence of log backups have not been broken, you can use the last full
backup and all transactions log backups. But time is precious and
differential backups help you save time.
A diffenrential backup is useful when the full backup of your db take long
time, so if you need to backup your data twice daily and the full backup take
12 hours, you can take one full backup once a week and two diff backups
daily, saving all changes from the last full backup.
As the others members said, BOL has good samples. You can also find some
good articles about this theme in Tibor's web page.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Travis" wrote:
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it need
> to be perform after the full backup ? How the SQL server detect there is the
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use back
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
> --
> Travis Tan|||Differential Database Backups
A differential database backup records only the data that has changed since
the last database backup. You can make more frequent backups because
differential database backups are smaller and faster than database backups.
Making frequent backups decreases your risk of losing data.
Note If you have created any file backups since the last full database
backup, those files will be scanned by Microsoft® SQL Server� 2000 at the
beginning of a differential database backup. This may cause some degradation
of performance in the differential database backup. For more information, see
Using File Backups.
You use differential database backups to restore the database to the point
at which the differential database backup was completed. To recover to the
exact point of failure, you must use transaction log backups. For more
information, see Transaction Log Backups.
Consider using differential database backups when:
Only a relatively small portion of the data in the database has changed
since the last database backup. Differential database backups are
particularly effective if the same data is modified many times.
You are using the Simple Recovery model and want more frequent backups, but
don't want to do frequent full database backups.
You are using the Full or Bulk-Logged Recovery model and want to minimize
the time it takes to roll forward transaction log backups when restoring a
database.
A recommended process for implementing differential database backups is:
Create regular database backups.
Create a differential database backup periodically between database backups,
such as every four hours or more for highly active systems.
If using Full or Bulk-Logged Recovery, create transaction log backups more
frequently than differential database backups, such as every 30 minutes.
The sequence for restoring differential database backups is:
Restore the most recent database backup.
Restore the last differential database backup.
Apply all transaction log backups created after the last differential
database backup was created if you use Full or Bulk-Logged Recovery.
For more information on differential database backups go here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_7tv7.asp
"Travis" wrote:
> Hi ,
> Can someone provide me futher info about Backup Diffrential ? Is it need
> to be perform after the full backup ? How the SQL server detect there is the
> changes of the tables ?
> 1) If I perform a full backup of Pubs mdb in C:\Pubs_Backup . It store
> all the data available in the database
> 2) If I backup differential on the Pubs mdb also , do I need to use back
> the same backup name ? If it is , the C:\Pubs_Backup will only consist the
> latest updated data.
> The original full data will be lost . It is true ?
>
> --
> Travis Tan
Backup differential
Hello everybody,
I need to get a differential backup in a small file but i am getting a
trouble ...
I got a Full backup with size=800MB
Then i got a differential backup with size=775B
It is an OLAP database then a lot of tables got a "DELETE FROM" command then
data is inserted again, it happens every early morning.
I hope it is correct, but i am not sure, i need some help with that.Leandro
Look at this very useful article
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
--administaiting best practices
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#4ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> Hello everybody,
> I need to get a differential backup in a small file but i am getting a
> trouble ...
> I got a Full backup with size=800MB
> Then i got a differential backup with size=775B
> It is an OLAP database then a lot of tables got a "DELETE FROM" command
then
> data is inserted again, it happens every early morning.
> I hope it is correct, but i am not sure, i need some help with that.
>|||I have to say that this database is Recovery Model SIMPLE ...
"Leandro Loureiro dos Santos" <leandro@.email.com> escreveu na mensagem
news:%234ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> Hello everybody,
> I need to get a differential backup in a small file but i am getting a
> trouble ...
> I got a Full backup with size=800MB
> Then i got a differential backup with size=775B
> It is an OLAP database then a lot of tables got a "DELETE FROM" command
then
> data is inserted again, it happens every early morning.
> I hope it is correct, but i am not sure, i need some help with that.
>|||Well, if you delete from and insert into many tables, then you are modifying
many extents, so diff backup is nearly as big as full db backup. Can you
change your data population procedure to incrementaly update the OLAP
database? Or at least to delete only a subset of rows, for example younger
than a month, and then insert only new rows?
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#4ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> Hello everybody,
> I need to get a differential backup in a small file but i am getting a
> trouble ...
> I got a Full backup with size=800MB
> Then i got a differential backup with size=775B
> It is an OLAP database then a lot of tables got a "DELETE FROM" command
then
> data is inserted again, it happens every early morning.
> I hope it is correct, but i am not sure, i need some help with that.
>|||If i keep the ETL this way i will have the same problem with Replication?
Thanks ...
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> escreveu
na mensagem news:udmeT3U9DHA.3380@.tk2msftngp13.phx.gbl...
> Well, if you delete from and insert into many tables, then you are
modifying
> many extents, so diff backup is nearly as big as full db backup. Can you
> change your data population procedure to incrementaly update the OLAP
> database? Or at least to delete only a subset of rows, for example younger
> than a month, and then insert only new rows?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
> news:#4ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> > Hello everybody,
> >
> > I need to get a differential backup in a small file but i am getting a
> > trouble ...
> >
> > I got a Full backup with size=800MB
> > Then i got a differential backup with size=775B
> > It is an OLAP database then a lot of tables got a "DELETE FROM" command
> then
> > data is inserted again, it happens every early morning.
> >
> > I hope it is correct, but i am not sure, i need some help with that.
> >
> >
>|||> If i keep the ETL this way i will have the same problem with Replication?
> Thanks ...
Sorry, I don't understand - where does replication fit here? Are you
replicating OLAP database? Which type of replication are you using? Anyway,
replication should gain from smaller updates as well.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||Yes, if you replicate (having deleted then re-inserted many rows), the
replication will go very slowly, as it must do the same...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#4ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> Hello everybody,
> I need to get a differential backup in a small file but i am getting a
> trouble ...
> I got a Full backup with size=800MB
> Then i got a differential backup with size=775B
> It is an OLAP database then a lot of tables got a "DELETE FROM" command
then
> data is inserted again, it happens every early morning.
> I hope it is correct, but i am not sure, i need some help with that.
>|||Leandro
With a datawarehouse (OLAP) system where most of the data
is changing on a daily basis, it might be worth looking at
the source of your data. That is where your backup and
recovery should be pursued. Supplying a new input file to
your system looks to be almost as good as restoring a full
backup.
It is not uncommon with datawarehouses that it is as easy
to recover the database from source as a backup. This is
why it is the exception to the rule that all production
databases should be in full recovery mode.
Regards
Johnsql
I need to get a differential backup in a small file but i am getting a
trouble ...
I got a Full backup with size=800MB
Then i got a differential backup with size=775B
It is an OLAP database then a lot of tables got a "DELETE FROM" command then
data is inserted again, it happens every early morning.
I hope it is correct, but i am not sure, i need some help with that.Leandro
Look at this very useful article
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
--administaiting best practices
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#4ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> Hello everybody,
> I need to get a differential backup in a small file but i am getting a
> trouble ...
> I got a Full backup with size=800MB
> Then i got a differential backup with size=775B
> It is an OLAP database then a lot of tables got a "DELETE FROM" command
then
> data is inserted again, it happens every early morning.
> I hope it is correct, but i am not sure, i need some help with that.
>|||I have to say that this database is Recovery Model SIMPLE ...
"Leandro Loureiro dos Santos" <leandro@.email.com> escreveu na mensagem
news:%234ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> Hello everybody,
> I need to get a differential backup in a small file but i am getting a
> trouble ...
> I got a Full backup with size=800MB
> Then i got a differential backup with size=775B
> It is an OLAP database then a lot of tables got a "DELETE FROM" command
then
> data is inserted again, it happens every early morning.
> I hope it is correct, but i am not sure, i need some help with that.
>|||Well, if you delete from and insert into many tables, then you are modifying
many extents, so diff backup is nearly as big as full db backup. Can you
change your data population procedure to incrementaly update the OLAP
database? Or at least to delete only a subset of rows, for example younger
than a month, and then insert only new rows?
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#4ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> Hello everybody,
> I need to get a differential backup in a small file but i am getting a
> trouble ...
> I got a Full backup with size=800MB
> Then i got a differential backup with size=775B
> It is an OLAP database then a lot of tables got a "DELETE FROM" command
then
> data is inserted again, it happens every early morning.
> I hope it is correct, but i am not sure, i need some help with that.
>|||If i keep the ETL this way i will have the same problem with Replication?
Thanks ...
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> escreveu
na mensagem news:udmeT3U9DHA.3380@.tk2msftngp13.phx.gbl...
> Well, if you delete from and insert into many tables, then you are
modifying
> many extents, so diff backup is nearly as big as full db backup. Can you
> change your data population procedure to incrementaly update the OLAP
> database? Or at least to delete only a subset of rows, for example younger
> than a month, and then insert only new rows?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
> news:#4ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> > Hello everybody,
> >
> > I need to get a differential backup in a small file but i am getting a
> > trouble ...
> >
> > I got a Full backup with size=800MB
> > Then i got a differential backup with size=775B
> > It is an OLAP database then a lot of tables got a "DELETE FROM" command
> then
> > data is inserted again, it happens every early morning.
> >
> > I hope it is correct, but i am not sure, i need some help with that.
> >
> >
>|||> If i keep the ETL this way i will have the same problem with Replication?
> Thanks ...
Sorry, I don't understand - where does replication fit here? Are you
replicating OLAP database? Which type of replication are you using? Anyway,
replication should gain from smaller updates as well.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||Yes, if you replicate (having deleted then re-inserted many rows), the
replication will go very slowly, as it must do the same...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#4ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> Hello everybody,
> I need to get a differential backup in a small file but i am getting a
> trouble ...
> I got a Full backup with size=800MB
> Then i got a differential backup with size=775B
> It is an OLAP database then a lot of tables got a "DELETE FROM" command
then
> data is inserted again, it happens every early morning.
> I hope it is correct, but i am not sure, i need some help with that.
>|||Leandro
With a datawarehouse (OLAP) system where most of the data
is changing on a daily basis, it might be worth looking at
the source of your data. That is where your backup and
recovery should be pursued. Supplying a new input file to
your system looks to be almost as good as restoring a full
backup.
It is not uncommon with datawarehouses that it is as easy
to recover the database from source as a backup. This is
why it is the exception to the rule that all production
databases should be in full recovery mode.
Regards
Johnsql
backup devices
hey all,
i'm trying to do a full backup on my database. there is a disk device
currently assigned to do a differential backup on the db. how do i get my
backup without messing up the current device. because there have been a
couple of times where i ran the backup using osq and it still ended up
changing the device and i didn't want it to do that.
thanks,
rodchar
Hi,
Use Maintenance Plan in Enterprise manager / SQL Server Management Studio to
do the full database backup. This will automatically create file names based
on Database name
and date and time stamp. Maintenance plan have an option of archival
also...s
Thanks
Hari
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:2DC3B373-0006-4C19-BECC-A277913BC48D@.microsoft.com...
> hey all,
> i'm trying to do a full backup on my database. there is a disk device
> currently assigned to do a differential backup on the db. how do i get my
> backup without messing up the current device. because there have been a
> couple of times where i ran the backup using osq and it still ended up
> changing the device and i didn't want it to do that.
> thanks,
> rodchar
>
|||thank you for your help. rod.
"Hari Prasad" wrote:
> Hi,
> Use Maintenance Plan in Enterprise manager / SQL Server Management Studio to
> do the full database backup. This will automatically create file names based
> on Database name
> and date and time stamp. Maintenance plan have an option of archival
> also...s
> Thanks
> Hari
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:2DC3B373-0006-4C19-BECC-A277913BC48D@.microsoft.com...
>
>
sql
i'm trying to do a full backup on my database. there is a disk device
currently assigned to do a differential backup on the db. how do i get my
backup without messing up the current device. because there have been a
couple of times where i ran the backup using osq and it still ended up
changing the device and i didn't want it to do that.
thanks,
rodchar
Hi,
Use Maintenance Plan in Enterprise manager / SQL Server Management Studio to
do the full database backup. This will automatically create file names based
on Database name
and date and time stamp. Maintenance plan have an option of archival
also...s
Thanks
Hari
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:2DC3B373-0006-4C19-BECC-A277913BC48D@.microsoft.com...
> hey all,
> i'm trying to do a full backup on my database. there is a disk device
> currently assigned to do a differential backup on the db. how do i get my
> backup without messing up the current device. because there have been a
> couple of times where i ran the backup using osq and it still ended up
> changing the device and i didn't want it to do that.
> thanks,
> rodchar
>
|||thank you for your help. rod.
"Hari Prasad" wrote:
> Hi,
> Use Maintenance Plan in Enterprise manager / SQL Server Management Studio to
> do the full database backup. This will automatically create file names based
> on Database name
> and date and time stamp. Maintenance plan have an option of archival
> also...s
> Thanks
> Hari
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:2DC3B373-0006-4C19-BECC-A277913BC48D@.microsoft.com...
>
>
sql
backup devices
hey all,
i'm trying to do a full backup on my database. there is a disk device
currently assigned to do a differential backup on the db. how do i get my
backup without messing up the current device. because there have been a
couple of times where i ran the backup using osq and it still ended up
changing the device and i didn't want it to do that.
thanks,
rodcharHi,
Use Maintenance Plan in Enterprise manager / SQL Server Management Studio to
do the full database backup. This will automatically create file names based
on Database name
and date and time stamp. Maintenance plan have an option of archival
also...s
Thanks
Hari
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:2DC3B373-0006-4C19-BECC-A277913BC48D@.microsoft.com...
> hey all,
> i'm trying to do a full backup on my database. there is a disk device
> currently assigned to do a differential backup on the db. how do i get my
> backup without messing up the current device. because there have been a
> couple of times where i ran the backup using osq and it still ended up
> changing the device and i didn't want it to do that.
> thanks,
> rodchar
>|||thank you for your help. rod.
"Hari Prasad" wrote:
> Hi,
> Use Maintenance Plan in Enterprise manager / SQL Server Management Studio to
> do the full database backup. This will automatically create file names based
> on Database name
> and date and time stamp. Maintenance plan have an option of archival
> also...s
> Thanks
> Hari
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:2DC3B373-0006-4C19-BECC-A277913BC48D@.microsoft.com...
> > hey all,
> > i'm trying to do a full backup on my database. there is a disk device
> > currently assigned to do a differential backup on the db. how do i get my
> > backup without messing up the current device. because there have been a
> > couple of times where i ran the backup using osq and it still ended up
> > changing the device and i didn't want it to do that.
> >
> > thanks,
> > rodchar
> >
>
>
i'm trying to do a full backup on my database. there is a disk device
currently assigned to do a differential backup on the db. how do i get my
backup without messing up the current device. because there have been a
couple of times where i ran the backup using osq and it still ended up
changing the device and i didn't want it to do that.
thanks,
rodcharHi,
Use Maintenance Plan in Enterprise manager / SQL Server Management Studio to
do the full database backup. This will automatically create file names based
on Database name
and date and time stamp. Maintenance plan have an option of archival
also...s
Thanks
Hari
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:2DC3B373-0006-4C19-BECC-A277913BC48D@.microsoft.com...
> hey all,
> i'm trying to do a full backup on my database. there is a disk device
> currently assigned to do a differential backup on the db. how do i get my
> backup without messing up the current device. because there have been a
> couple of times where i ran the backup using osq and it still ended up
> changing the device and i didn't want it to do that.
> thanks,
> rodchar
>|||thank you for your help. rod.
"Hari Prasad" wrote:
> Hi,
> Use Maintenance Plan in Enterprise manager / SQL Server Management Studio to
> do the full database backup. This will automatically create file names based
> on Database name
> and date and time stamp. Maintenance plan have an option of archival
> also...s
> Thanks
> Hari
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:2DC3B373-0006-4C19-BECC-A277913BC48D@.microsoft.com...
> > hey all,
> > i'm trying to do a full backup on my database. there is a disk device
> > currently assigned to do a differential backup on the db. how do i get my
> > backup without messing up the current device. because there have been a
> > couple of times where i ran the backup using osq and it still ended up
> > changing the device and i didn't want it to do that.
> >
> > thanks,
> > rodchar
> >
>
>
backup devices
hey all,
i'm trying to do a full backup on my database. there is a disk device
currently assigned to do a differential backup on the db. how do i get my
backup without messing up the current device. because there have been a
couple of times where i ran the backup using osq and it still ended up
changing the device and i didn't want it to do that.
thanks,
rodcharHi,
Use Maintenance Plan in Enterprise manager / SQL Server Management Studio to
do the full database backup. This will automatically create file names based
on Database name
and date and time stamp. Maintenance plan have an option of archival
also...s
Thanks
Hari
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:2DC3B373-0006-4C19-BECC-A277913BC48D@.microsoft.com...
> hey all,
> i'm trying to do a full backup on my database. there is a disk device
> currently assigned to do a differential backup on the db. how do i get my
> backup without messing up the current device. because there have been a
> couple of times where i ran the backup using osq and it still ended up
> changing the device and i didn't want it to do that.
> thanks,
> rodchar
>|||thank you for your help. rod.
"Hari Prasad" wrote:
> Hi,
> Use Maintenance Plan in Enterprise manager / SQL Server Management Studio
to
> do the full database backup. This will automatically create file names bas
ed
> on Database name
> and date and time stamp. Maintenance plan have an option of archival
> also...s
> Thanks
> Hari
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:2DC3B373-0006-4C19-BECC-A277913BC48D@.microsoft.com...
>
>
i'm trying to do a full backup on my database. there is a disk device
currently assigned to do a differential backup on the db. how do i get my
backup without messing up the current device. because there have been a
couple of times where i ran the backup using osq and it still ended up
changing the device and i didn't want it to do that.
thanks,
rodcharHi,
Use Maintenance Plan in Enterprise manager / SQL Server Management Studio to
do the full database backup. This will automatically create file names based
on Database name
and date and time stamp. Maintenance plan have an option of archival
also...s
Thanks
Hari
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:2DC3B373-0006-4C19-BECC-A277913BC48D@.microsoft.com...
> hey all,
> i'm trying to do a full backup on my database. there is a disk device
> currently assigned to do a differential backup on the db. how do i get my
> backup without messing up the current device. because there have been a
> couple of times where i ran the backup using osq and it still ended up
> changing the device and i didn't want it to do that.
> thanks,
> rodchar
>|||thank you for your help. rod.
"Hari Prasad" wrote:
> Hi,
> Use Maintenance Plan in Enterprise manager / SQL Server Management Studio
to
> do the full database backup. This will automatically create file names bas
ed
> on Database name
> and date and time stamp. Maintenance plan have an option of archival
> also...s
> Thanks
> Hari
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:2DC3B373-0006-4C19-BECC-A277913BC48D@.microsoft.com...
>
>
Saturday, February 25, 2012
Backup and Restore
Hi All,
I may have a basic question in backup and restore (sql server 2000).
Am taking full backup on a daily basis, differential backup every 4 hours and transaction log backup every 30 mins. Lets assume the series is like this (am placing backup on a different machine);
8:00 AM -- Full backup
12:00 PM -- Differential backup
12:30 PM -- Transaction log backup
And at 12:55 PM the system crashes, after which i will not have access to the online transaction logs.
My question is,
Is there a way to recover 25 mins of data (from 12:30 - 12:55 PM), for which I haven't taken any backup?
Thanks in advance
GreneIf you don't have access to the transaction log, you won't be able to
perform a BACKUP with NO_TRUNCATE to backup the currently active transaction
log. Restore to the point of failure is therefore not possible. You can only
recover to 12:30 with your last transaction log backup.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Grene" <anonymous@.discussions.microsoft.com> wrote in message
news:C217F59C-8A1D-4150-9608-27B84DCBAC66@.microsoft.com...
> Hi All,
> I may have a basic question in backup and restore (sql server 2000).
> Am taking full backup on a daily basis, differential backup every 4 hours
and transaction log backup every 30 mins. Lets assume the series is like
this (am placing backup on a different machine);
> 8:00 AM -- Full backup
> 12:00 PM -- Differential backup
> 12:30 PM -- Transaction log backup
> And at 12:55 PM the system crashes, after which i will not have access to
the online transaction logs.
> My question is,
> Is there a way to recover 25 mins of data (from 12:30 - 12:55 PM), for
which I haven't taken any backup?
> Thanks in advance
> Grene
>|||It might be worth mentioning that if the SQL Server install is toast, but the ldf file(s) are there,
then the files can be transferred to another machine where SQL Server is installed and the
NO_TRUNCATE backup ban be ran on this machine. There's a KB on this scenario, btw.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:uLgo2KZrDHA.2440@.TK2MSFTNGP10.phx.gbl...
> If you don't have access to the transaction log, you won't be able to
> perform a BACKUP with NO_TRUNCATE to backup the currently active transaction
> log. Restore to the point of failure is therefore not possible. You can only
> recover to 12:30 with your last transaction log backup.
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "Grene" <anonymous@.discussions.microsoft.com> wrote in message
> news:C217F59C-8A1D-4150-9608-27B84DCBAC66@.microsoft.com...
> > Hi All,
> >
> > I may have a basic question in backup and restore (sql server 2000).
> >
> > Am taking full backup on a daily basis, differential backup every 4 hours
> and transaction log backup every 30 mins. Lets assume the series is like
> this (am placing backup on a different machine);
> > 8:00 AM -- Full backup
> > 12:00 PM -- Differential backup
> > 12:30 PM -- Transaction log backup
> > And at 12:55 PM the system crashes, after which i will not have access to
> the online transaction logs.
> >
> > My question is,
> > Is there a way to recover 25 mins of data (from 12:30 - 12:55 PM), for
> which I haven't taken any backup?
> >
> > Thanks in advance
> > Grene
> >
> >
>
I may have a basic question in backup and restore (sql server 2000).
Am taking full backup on a daily basis, differential backup every 4 hours and transaction log backup every 30 mins. Lets assume the series is like this (am placing backup on a different machine);
8:00 AM -- Full backup
12:00 PM -- Differential backup
12:30 PM -- Transaction log backup
And at 12:55 PM the system crashes, after which i will not have access to the online transaction logs.
My question is,
Is there a way to recover 25 mins of data (from 12:30 - 12:55 PM), for which I haven't taken any backup?
Thanks in advance
GreneIf you don't have access to the transaction log, you won't be able to
perform a BACKUP with NO_TRUNCATE to backup the currently active transaction
log. Restore to the point of failure is therefore not possible. You can only
recover to 12:30 with your last transaction log backup.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Grene" <anonymous@.discussions.microsoft.com> wrote in message
news:C217F59C-8A1D-4150-9608-27B84DCBAC66@.microsoft.com...
> Hi All,
> I may have a basic question in backup and restore (sql server 2000).
> Am taking full backup on a daily basis, differential backup every 4 hours
and transaction log backup every 30 mins. Lets assume the series is like
this (am placing backup on a different machine);
> 8:00 AM -- Full backup
> 12:00 PM -- Differential backup
> 12:30 PM -- Transaction log backup
> And at 12:55 PM the system crashes, after which i will not have access to
the online transaction logs.
> My question is,
> Is there a way to recover 25 mins of data (from 12:30 - 12:55 PM), for
which I haven't taken any backup?
> Thanks in advance
> Grene
>|||It might be worth mentioning that if the SQL Server install is toast, but the ldf file(s) are there,
then the files can be transferred to another machine where SQL Server is installed and the
NO_TRUNCATE backup ban be ran on this machine. There's a KB on this scenario, btw.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:uLgo2KZrDHA.2440@.TK2MSFTNGP10.phx.gbl...
> If you don't have access to the transaction log, you won't be able to
> perform a BACKUP with NO_TRUNCATE to backup the currently active transaction
> log. Restore to the point of failure is therefore not possible. You can only
> recover to 12:30 with your last transaction log backup.
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "Grene" <anonymous@.discussions.microsoft.com> wrote in message
> news:C217F59C-8A1D-4150-9608-27B84DCBAC66@.microsoft.com...
> > Hi All,
> >
> > I may have a basic question in backup and restore (sql server 2000).
> >
> > Am taking full backup on a daily basis, differential backup every 4 hours
> and transaction log backup every 30 mins. Lets assume the series is like
> this (am placing backup on a different machine);
> > 8:00 AM -- Full backup
> > 12:00 PM -- Differential backup
> > 12:30 PM -- Transaction log backup
> > And at 12:55 PM the system crashes, after which i will not have access to
> the online transaction logs.
> >
> > My question is,
> > Is there a way to recover 25 mins of data (from 12:30 - 12:55 PM), for
> which I haven't taken any backup?
> >
> > Thanks in advance
> > Grene
> >
> >
>
Sunday, February 19, 2012
Backup & Restore Problem
Dear All,
I've problem when restoring differential & log backup.
How I can restore differential backup and log backup?
Note : I backup sql server data to files.
ThanksFirst restore last full backup of your database. Specifiy (in EM) 'able to
receive additional tran logs'
Then restore last differential backup of your database
then restore sequentially transaction logs from the last diff backup to the
point you need.
MC
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:eqh$nek%23FHA.272@.tk2msftngp13.phx.gbl...
> Dear All,
> I've problem when restoring differential & log backup.
> How I can restore differential backup and log backup?
> Note : I backup sql server data to files.
> Thanks|||First you need full backup.
Regards
I've problem when restoring differential & log backup.
How I can restore differential backup and log backup?
Note : I backup sql server data to files.
ThanksFirst restore last full backup of your database. Specifiy (in EM) 'able to
receive additional tran logs'
Then restore last differential backup of your database
then restore sequentially transaction logs from the last diff backup to the
point you need.
MC
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:eqh$nek%23FHA.272@.tk2msftngp13.phx.gbl...
> Dear All,
> I've problem when restoring differential & log backup.
> How I can restore differential backup and log backup?
> Note : I backup sql server data to files.
> Thanks|||First you need full backup.
Regards
Thursday, February 16, 2012
Backup & Restore Problem
Dear All,
I've problem when restoring differential & log backup.
How I can restore differential backup and log backup?
Note : I backup sql server data to files.
Thanks
First restore last full backup of your database. Specifiy (in EM) 'able to
receive additional tran logs'
Then restore last differential backup of your database
then restore sequentially transaction logs from the last diff backup to the
point you need.
MC
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:eqh$nek%23FHA.272@.tk2msftngp13.phx.gbl...
> Dear All,
> I've problem when restoring differential & log backup.
> How I can restore differential backup and log backup?
> Note : I backup sql server data to files.
> Thanks
|||First you need full backup.
Regards
I've problem when restoring differential & log backup.
How I can restore differential backup and log backup?
Note : I backup sql server data to files.
Thanks
First restore last full backup of your database. Specifiy (in EM) 'able to
receive additional tran logs'
Then restore last differential backup of your database
then restore sequentially transaction logs from the last diff backup to the
point you need.
MC
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:eqh$nek%23FHA.272@.tk2msftngp13.phx.gbl...
> Dear All,
> I've problem when restoring differential & log backup.
> How I can restore differential backup and log backup?
> Note : I backup sql server data to files.
> Thanks
|||First you need full backup.
Regards
Backup & Restore Problem
Dear All,
I've problem when restoring differential & log backup.
How I can restore differential backup and log backup?
Note : I backup sql server data to files.
ThanksFirst restore last full backup of your database. Specifiy (in EM) 'able to
receive additional tran logs'
Then restore last differential backup of your database
then restore sequentially transaction logs from the last diff backup to the
point you need.
MC
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:eqh$nek%23FHA.272@.tk2msftngp13.phx.gbl...
> Dear All,
> I've problem when restoring differential & log backup.
> How I can restore differential backup and log backup?
> Note : I backup sql server data to files.
> Thanks|||First you need full backup.
Regards
I've problem when restoring differential & log backup.
How I can restore differential backup and log backup?
Note : I backup sql server data to files.
ThanksFirst restore last full backup of your database. Specifiy (in EM) 'able to
receive additional tran logs'
Then restore last differential backup of your database
then restore sequentially transaction logs from the last diff backup to the
point you need.
MC
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:eqh$nek%23FHA.272@.tk2msftngp13.phx.gbl...
> Dear All,
> I've problem when restoring differential & log backup.
> How I can restore differential backup and log backup?
> Note : I backup sql server data to files.
> Thanks|||First you need full backup.
Regards
Subscribe to:
Posts (Atom)