I have some confusion about backing up databases and transaction logs.
When I do a complete database backup it backs up the db and the log, but
when I have it set to clear out inactive entries in the log it does not.
When I do a log backup and set it to clear out inactive entries it does do
that. My question is then if I do complete database backups all the time and
the log is not cleared for a long time and I do a restore and then play back
the log from a long time ago will I corrupt the database? How should I do
backups. Should I backup the log first clear it then backup the full
database or db first then log? I am confused. Thanks.
Tom,
> How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
- Full db backup (every night or once a week, depend the size of your db and
the time window you have for doing it)
- Differential backup (one daily or two, depend the activity. Let us say at
noon. this will save time restoring the db because you do not have to restore
all transaction log backups from the last full one, instead you restore the
full backup, the last diff and the trans log backups from the last diff
including a backup of the active one)
- Log backups between full / diff (from full to diff and from diff to full,
or from diff to diff if there is not a full between them, or between full to
full if not diff), as many as you need to avoid it from growing and be save
in case somthing bad happen and we can not backup active one.
Here you will find some good articles about backup and restore.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Tom Reis" wrote:
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time and
> the log is not cleared for a long time and I do a restore and then play back
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>
>
|||I forgot to mention that full and diff backups do not truncate the
transaction log, just transaction log backup does this when recovery model is
bulk-logged or full. The procedure for simple recovery model is different.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Tom,
>
> - Full db backup (every night or once a week, depend the size of your db and
> the time window you have for doing it)
> - Differential backup (one daily or two, depend the activity. Let us say at
> noon. this will save time restoring the db because you do not have to restore
> all transaction log backups from the last full one, instead you restore the
> full backup, the last diff and the trans log backups from the last diff
> including a backup of the active one)
> - Log backups between full / diff (from full to diff and from diff to full,
> or from diff to diff if there is not a full between them, or between full to
> full if not diff), as many as you need to avoid it from growing and be save
> in case somthing bad happen and we can not backup active one.
> Here you will find some good articles about backup and restore.
> http://www.karaszi.com/SQLServer/articles.asp
> AMB
>
> "Tom Reis" wrote:
|||> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
The "clear entries..." is a terribly bad designed way to expose the NO_TRUNCATE option for the
BACKUP LOG command. It has no meaning for BACKUP DATABASE. The checkbox should be disabled for
BACKUP DATABASE. Run a profiler trace and you will se that EM generates the exact same BACKUP
DATABASE command whether or not the checkbox is checked.
> When I do a log backup and set it to clear out inactive entries it does do
> that.
Yes, this is a normal log backup. Checking this option should *only* be done of you have a corrupt
database and want to back the log. Read about the NO_TRUNCATE option and also
http://www.karaszi.com/SQLServer/inf..._truncate.asp.
> My question is then if I do complete database backups all the time and
> the log is not cleared for a long time
Why would you want to do that? A database backup is a snapshot in time. If you don't do log backups,
then run in simple recovery mode.
> How should I do
> backups.
Either to db backup say every day and log backups say every hour (or every ten minutes).
Or if you don't want to do log backups, put the db in simple recovery mode and only do db backups.
It is not very complicated. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:OKGvIFMqFHA.820@.TK2MSFTNGP09.phx.gbl...
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time and
> the log is not cleared for a long time and I do a restore and then play back
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>
Showing posts with label backing. Show all posts
Showing posts with label backing. Show all posts
Sunday, March 25, 2012
Database backups
I have some confusion about backing up databases and transaction logs.
When I do a complete database backup it backs up the db and the log, but
when I have it set to clear out inactive entries in the log it does not.
When I do a log backup and set it to clear out inactive entries it does do
that. My question is then if I do complete database backups all the time and
the log is not cleared for a long time and I do a restore and then play back
the log from a long time ago will I corrupt the database? How should I do
backups. Should I backup the log first clear it then backup the full
database or db first then log? I am confused. Thanks.Tom,
> How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
- Full db backup (every night or once a week, depend the size of your db and
the time window you have for doing it)
- Differential backup (one daily or two, depend the activity. Let us say at
noon. this will save time restoring the db because you do not have to restor
e
all transaction log backups from the last full one, instead you restore the
full backup, the last diff and the trans log backups from the last diff
including a backup of the active one)
- Log backups between full / diff (from full to diff and from diff to full,
or from diff to diff if there is not a full between them, or between full to
full if not diff), as many as you need to avoid it from growing and be save
in case somthing bad happen and we can not backup active one.
Here you will find some good articles about backup and restore.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Tom Reis" wrote:
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time a
nd
> the log is not cleared for a long time and I do a restore and then play ba
ck
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>
>|||I forgot to mention that full and diff backups do not truncate the
transaction log, just transaction log backup does this when recovery model i
s
bulk-logged or full. The procedure for simple recovery model is different.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Tom,
>
> - Full db backup (every night or once a week, depend the size of your db a
nd
> the time window you have for doing it)
> - Differential backup (one daily or two, depend the activity. Let us say a
t
> noon. this will save time restoring the db because you do not have to rest
ore
> all transaction log backups from the last full one, instead you restore th
e
> full backup, the last diff and the trans log backups from the last diff
> including a backup of the active one)
> - Log backups between full / diff (from full to diff and from diff to full
,
> or from diff to diff if there is not a full between them, or between full
to
> full if not diff), as many as you need to avoid it from growing and be sav
e
> in case somthing bad happen and we can not backup active one.
> Here you will find some good articles about backup and restore.
> http://www.karaszi.com/SQLServer/articles.asp
> AMB
>
> "Tom Reis" wrote:
>|||> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
The "clear entries..." is a terribly bad designed way to expose the NO_TRUNC
ATE option for the
BACKUP LOG command. It has no meaning for BACKUP DATABASE. The checkbox shou
ld be disabled for
BACKUP DATABASE. Run a profiler trace and you will se that EM generates the
exact same BACKUP
DATABASE command whether or not the checkbox is checked.
> When I do a log backup and set it to clear out inactive entries it does do
> that.
Yes, this is a normal log backup. Checking this option should *only* be done
of you have a corrupt
database and want to back the log. Read about the NO_TRUNCATE option and als
o
http://www.karaszi.com/SQLServer/in...o_truncate.asp.
> My question is then if I do complete database backups all the time and
> the log is not cleared for a long time
Why would you want to do that? A database backup is a snapshot in time. If y
ou don't do log backups,
then run in simple recovery mode.
> How should I do
> backups.
Either to db backup say every day and log backups say every hour (or every t
en minutes).
Or if you don't want to do log backups, put the db in simple recovery mode a
nd only do db backups.
It is not very complicated. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:OKGvIFMqFHA.820@.TK2MSFTNGP09.phx.gb
l...
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time a
nd
> the log is not cleared for a long time and I do a restore and then play ba
ck
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>sql
When I do a complete database backup it backs up the db and the log, but
when I have it set to clear out inactive entries in the log it does not.
When I do a log backup and set it to clear out inactive entries it does do
that. My question is then if I do complete database backups all the time and
the log is not cleared for a long time and I do a restore and then play back
the log from a long time ago will I corrupt the database? How should I do
backups. Should I backup the log first clear it then backup the full
database or db first then log? I am confused. Thanks.Tom,
> How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
- Full db backup (every night or once a week, depend the size of your db and
the time window you have for doing it)
- Differential backup (one daily or two, depend the activity. Let us say at
noon. this will save time restoring the db because you do not have to restor
e
all transaction log backups from the last full one, instead you restore the
full backup, the last diff and the trans log backups from the last diff
including a backup of the active one)
- Log backups between full / diff (from full to diff and from diff to full,
or from diff to diff if there is not a full between them, or between full to
full if not diff), as many as you need to avoid it from growing and be save
in case somthing bad happen and we can not backup active one.
Here you will find some good articles about backup and restore.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Tom Reis" wrote:
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time a
nd
> the log is not cleared for a long time and I do a restore and then play ba
ck
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>
>|||I forgot to mention that full and diff backups do not truncate the
transaction log, just transaction log backup does this when recovery model i
s
bulk-logged or full. The procedure for simple recovery model is different.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Tom,
>
> - Full db backup (every night or once a week, depend the size of your db a
nd
> the time window you have for doing it)
> - Differential backup (one daily or two, depend the activity. Let us say a
t
> noon. this will save time restoring the db because you do not have to rest
ore
> all transaction log backups from the last full one, instead you restore th
e
> full backup, the last diff and the trans log backups from the last diff
> including a backup of the active one)
> - Log backups between full / diff (from full to diff and from diff to full
,
> or from diff to diff if there is not a full between them, or between full
to
> full if not diff), as many as you need to avoid it from growing and be sav
e
> in case somthing bad happen and we can not backup active one.
> Here you will find some good articles about backup and restore.
> http://www.karaszi.com/SQLServer/articles.asp
> AMB
>
> "Tom Reis" wrote:
>|||> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
The "clear entries..." is a terribly bad designed way to expose the NO_TRUNC
ATE option for the
BACKUP LOG command. It has no meaning for BACKUP DATABASE. The checkbox shou
ld be disabled for
BACKUP DATABASE. Run a profiler trace and you will se that EM generates the
exact same BACKUP
DATABASE command whether or not the checkbox is checked.
> When I do a log backup and set it to clear out inactive entries it does do
> that.
Yes, this is a normal log backup. Checking this option should *only* be done
of you have a corrupt
database and want to back the log. Read about the NO_TRUNCATE option and als
o
http://www.karaszi.com/SQLServer/in...o_truncate.asp.
> My question is then if I do complete database backups all the time and
> the log is not cleared for a long time
Why would you want to do that? A database backup is a snapshot in time. If y
ou don't do log backups,
then run in simple recovery mode.
> How should I do
> backups.
Either to db backup say every day and log backups say every hour (or every t
en minutes).
Or if you don't want to do log backups, put the db in simple recovery mode a
nd only do db backups.
It is not very complicated. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:OKGvIFMqFHA.820@.TK2MSFTNGP09.phx.gb
l...
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time a
nd
> the log is not cleared for a long time and I do a restore and then play ba
ck
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>sql
Database backups
I have some confusion about backing up databases and transaction logs.
When I do a complete database backup it backs up the db and the log, but
when I have it set to clear out inactive entries in the log it does not.
When I do a log backup and set it to clear out inactive entries it does do
that. My question is then if I do complete database backups all the time and
the log is not cleared for a long time and I do a restore and then play back
the log from a long time ago will I corrupt the database? How should I do
backups. Should I backup the log first clear it then backup the full
database or db first then log? I am confused. Thanks.Tom,
> How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
- Full db backup (every night or once a week, depend the size of your db and
the time window you have for doing it)
- Differential backup (one daily or two, depend the activity. Let us say at
noon. this will save time restoring the db because you do not have to restore
all transaction log backups from the last full one, instead you restore the
full backup, the last diff and the trans log backups from the last diff
including a backup of the active one)
- Log backups between full / diff (from full to diff and from diff to full,
or from diff to diff if there is not a full between them, or between full to
full if not diff), as many as you need to avoid it from growing and be save
in case somthing bad happen and we can not backup active one.
Here you will find some good articles about backup and restore.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Tom Reis" wrote:
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time and
> the log is not cleared for a long time and I do a restore and then play back
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>
>|||I forgot to mention that full and diff backups do not truncate the
transaction log, just transaction log backup does this when recovery model is
bulk-logged or full. The procedure for simple recovery model is different.
AMB
"Alejandro Mesa" wrote:
> Tom,
> > How should I do
> > backups. Should I backup the log first clear it then backup the full
> > database or db first then log? I am confused. Thanks.
> - Full db backup (every night or once a week, depend the size of your db and
> the time window you have for doing it)
> - Differential backup (one daily or two, depend the activity. Let us say at
> noon. this will save time restoring the db because you do not have to restore
> all transaction log backups from the last full one, instead you restore the
> full backup, the last diff and the trans log backups from the last diff
> including a backup of the active one)
> - Log backups between full / diff (from full to diff and from diff to full,
> or from diff to diff if there is not a full between them, or between full to
> full if not diff), as many as you need to avoid it from growing and be save
> in case somthing bad happen and we can not backup active one.
> Here you will find some good articles about backup and restore.
> http://www.karaszi.com/SQLServer/articles.asp
> AMB
>
> "Tom Reis" wrote:
> > I have some confusion about backing up databases and transaction logs.
> > When I do a complete database backup it backs up the db and the log, but
> > when I have it set to clear out inactive entries in the log it does not.
> > When I do a log backup and set it to clear out inactive entries it does do
> > that. My question is then if I do complete database backups all the time and
> > the log is not cleared for a long time and I do a restore and then play back
> > the log from a long time ago will I corrupt the database? How should I do
> > backups. Should I backup the log first clear it then backup the full
> > database or db first then log? I am confused. Thanks.
> >
> >
> >|||> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
The "clear entries..." is a terribly bad designed way to expose the NO_TRUNCATE option for the
BACKUP LOG command. It has no meaning for BACKUP DATABASE. The checkbox should be disabled for
BACKUP DATABASE. Run a profiler trace and you will se that EM generates the exact same BACKUP
DATABASE command whether or not the checkbox is checked.
> When I do a log backup and set it to clear out inactive entries it does do
> that.
Yes, this is a normal log backup. Checking this option should *only* be done of you have a corrupt
database and want to back the log. Read about the NO_TRUNCATE option and also
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp.
> My question is then if I do complete database backups all the time and
> the log is not cleared for a long time
Why would you want to do that? A database backup is a snapshot in time. If you don't do log backups,
then run in simple recovery mode.
> How should I do
> backups.
Either to db backup say every day and log backups say every hour (or every ten minutes).
Or if you don't want to do log backups, put the db in simple recovery mode and only do db backups.
It is not very complicated. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:OKGvIFMqFHA.820@.TK2MSFTNGP09.phx.gbl...
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time and
> the log is not cleared for a long time and I do a restore and then play back
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>
When I do a complete database backup it backs up the db and the log, but
when I have it set to clear out inactive entries in the log it does not.
When I do a log backup and set it to clear out inactive entries it does do
that. My question is then if I do complete database backups all the time and
the log is not cleared for a long time and I do a restore and then play back
the log from a long time ago will I corrupt the database? How should I do
backups. Should I backup the log first clear it then backup the full
database or db first then log? I am confused. Thanks.Tom,
> How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
- Full db backup (every night or once a week, depend the size of your db and
the time window you have for doing it)
- Differential backup (one daily or two, depend the activity. Let us say at
noon. this will save time restoring the db because you do not have to restore
all transaction log backups from the last full one, instead you restore the
full backup, the last diff and the trans log backups from the last diff
including a backup of the active one)
- Log backups between full / diff (from full to diff and from diff to full,
or from diff to diff if there is not a full between them, or between full to
full if not diff), as many as you need to avoid it from growing and be save
in case somthing bad happen and we can not backup active one.
Here you will find some good articles about backup and restore.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Tom Reis" wrote:
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time and
> the log is not cleared for a long time and I do a restore and then play back
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>
>|||I forgot to mention that full and diff backups do not truncate the
transaction log, just transaction log backup does this when recovery model is
bulk-logged or full. The procedure for simple recovery model is different.
AMB
"Alejandro Mesa" wrote:
> Tom,
> > How should I do
> > backups. Should I backup the log first clear it then backup the full
> > database or db first then log? I am confused. Thanks.
> - Full db backup (every night or once a week, depend the size of your db and
> the time window you have for doing it)
> - Differential backup (one daily or two, depend the activity. Let us say at
> noon. this will save time restoring the db because you do not have to restore
> all transaction log backups from the last full one, instead you restore the
> full backup, the last diff and the trans log backups from the last diff
> including a backup of the active one)
> - Log backups between full / diff (from full to diff and from diff to full,
> or from diff to diff if there is not a full between them, or between full to
> full if not diff), as many as you need to avoid it from growing and be save
> in case somthing bad happen and we can not backup active one.
> Here you will find some good articles about backup and restore.
> http://www.karaszi.com/SQLServer/articles.asp
> AMB
>
> "Tom Reis" wrote:
> > I have some confusion about backing up databases and transaction logs.
> > When I do a complete database backup it backs up the db and the log, but
> > when I have it set to clear out inactive entries in the log it does not.
> > When I do a log backup and set it to clear out inactive entries it does do
> > that. My question is then if I do complete database backups all the time and
> > the log is not cleared for a long time and I do a restore and then play back
> > the log from a long time ago will I corrupt the database? How should I do
> > backups. Should I backup the log first clear it then backup the full
> > database or db first then log? I am confused. Thanks.
> >
> >
> >|||> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
The "clear entries..." is a terribly bad designed way to expose the NO_TRUNCATE option for the
BACKUP LOG command. It has no meaning for BACKUP DATABASE. The checkbox should be disabled for
BACKUP DATABASE. Run a profiler trace and you will se that EM generates the exact same BACKUP
DATABASE command whether or not the checkbox is checked.
> When I do a log backup and set it to clear out inactive entries it does do
> that.
Yes, this is a normal log backup. Checking this option should *only* be done of you have a corrupt
database and want to back the log. Read about the NO_TRUNCATE option and also
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp.
> My question is then if I do complete database backups all the time and
> the log is not cleared for a long time
Why would you want to do that? A database backup is a snapshot in time. If you don't do log backups,
then run in simple recovery mode.
> How should I do
> backups.
Either to db backup say every day and log backups say every hour (or every ten minutes).
Or if you don't want to do log backups, put the db in simple recovery mode and only do db backups.
It is not very complicated. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:OKGvIFMqFHA.820@.TK2MSFTNGP09.phx.gbl...
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time and
> the log is not cleared for a long time and I do a restore and then play back
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>
Database backup using T-Sql
Can anyone help me out here..I am experimenting with backing up a db using Transact-Sql Backup.
I want to backup the database to a local drive say c:\databaseBk and a remote Server.
I tried
BACKUP DATABASE Mydb TO AlternativeServer but am getting errors here.
I know there is more to it and I also tried as below...
Even backup to remote for now would be perfect...if anyone has the code to do this locally that would be great also...
I got this from BooksOnline.....
USE Mydb
EXEC sp_addumpdevice 'disk', AlternativeServer,
DISK ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Mydb.dat'
-- Back up
BACKUP DATABASE MyNwind TO MyNwind_1I've got another one for your collection:
DUMP DATABASE dbname TO device WITH NOUNLOAD , INIT , NOSKIP
Where is the problem lays?
Regards,
OBRP|||As far as I know you can not backup DB on the remote server - only on a local device (tape or drive).|||You can backup on a remote computer by :
backup database dbname to disk='\\remote\server\path\filename.dbk' with init,noskip,NOUNLOAD
This should help you to backup it up to a remote computer without any problem.
Thanks.|||Make sure yours sqlserver runs on domain wide account, or account with write rights on remore compute.
HTH,
OBRP|||Thanks for that...
I want to backup the database to a local drive say c:\databaseBk and a remote Server.
I tried
BACKUP DATABASE Mydb TO AlternativeServer but am getting errors here.
I know there is more to it and I also tried as below...
Even backup to remote for now would be perfect...if anyone has the code to do this locally that would be great also...
I got this from BooksOnline.....
USE Mydb
EXEC sp_addumpdevice 'disk', AlternativeServer,
DISK ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Mydb.dat'
-- Back up
BACKUP DATABASE MyNwind TO MyNwind_1I've got another one for your collection:
DUMP DATABASE dbname TO device WITH NOUNLOAD , INIT , NOSKIP
Where is the problem lays?
Regards,
OBRP|||As far as I know you can not backup DB on the remote server - only on a local device (tape or drive).|||You can backup on a remote computer by :
backup database dbname to disk='\\remote\server\path\filename.dbk' with init,noskip,NOUNLOAD
This should help you to backup it up to a remote computer without any problem.
Thanks.|||Make sure yours sqlserver runs on domain wide account, or account with write rights on remore compute.
HTH,
OBRP|||Thanks for that...
Wednesday, March 21, 2012
Database Backup Encrypted.
Quick question, I am backing up SQL Server Database every night, I want to
encrypt the database backup file?
Is there any way through scheduler I can take the backup as well as encrypt
it.
Thanks
Noor
Your best bet is to use one of the 3rd party backup tools (red-gate, Quest
etc) to do the backup, compress it and encrypt it all in one.
Andrew J. Kelly SQL MVP
"Rogers" <naissani@.hotmail.com> wrote in message
news:OTqpLGcjHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Quick question, I am backing up SQL Server Database every night, I want to
> encrypt the database backup file?
> Is there any way through scheduler I can take the backup as well as
> encrypt it.
> Thanks
> Noor
>
>
|||If you are using SQL 2000, you can backup to named pipes and use a
tool that can read from pipe and write the encrypted output to a file.
I used this technique with the Sure Crypt tool
http://www.softpedia.com/get/Security/Encrypting/SureCrypt.shtml and
these commands:
--Backup database to a pipe
backup database pubs to pipe ='\\.\pipe\pubs_pipe'
--At the command prompt type
sc dsckey.dat e \\.\pipe\PUBS_pipe pubs.bkp.enc
where sc is the ecncryption tool reading from the pipe and writing to
pubs.bkp.enc and dsckey.dat is your key file.
However, since SQL 2005 doesn't support named pipes as backup device,
I wrote a simple tool (zipvdi) that makes all the work in a single
step and compatible with SQL 2k and 2k5
Give ZipVDI a try from http://jcarlossaez.blogspot.com/2007/01/backup-and-restore-tool-for-sql-server.html
Regards
encrypt the database backup file?
Is there any way through scheduler I can take the backup as well as encrypt
it.
Thanks
Noor
Your best bet is to use one of the 3rd party backup tools (red-gate, Quest
etc) to do the backup, compress it and encrypt it all in one.
Andrew J. Kelly SQL MVP
"Rogers" <naissani@.hotmail.com> wrote in message
news:OTqpLGcjHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Quick question, I am backing up SQL Server Database every night, I want to
> encrypt the database backup file?
> Is there any way through scheduler I can take the backup as well as
> encrypt it.
> Thanks
> Noor
>
>
|||If you are using SQL 2000, you can backup to named pipes and use a
tool that can read from pipe and write the encrypted output to a file.
I used this technique with the Sure Crypt tool
http://www.softpedia.com/get/Security/Encrypting/SureCrypt.shtml and
these commands:
--Backup database to a pipe
backup database pubs to pipe ='\\.\pipe\pubs_pipe'
--At the command prompt type
sc dsckey.dat e \\.\pipe\PUBS_pipe pubs.bkp.enc
where sc is the ecncryption tool reading from the pipe and writing to
pubs.bkp.enc and dsckey.dat is your key file.
However, since SQL 2005 doesn't support named pipes as backup device,
I wrote a simple tool (zipvdi) that makes all the work in a single
step and compatible with SQL 2k and 2k5
Give ZipVDI a try from http://jcarlossaez.blogspot.com/2007/01/backup-and-restore-tool-for-sql-server.html
Regards
Sunday, March 11, 2012
database and log backup
We are using SQL 2K with sp4. I like someone to verify what I am doing is
correct. and there is no potential problem.
I am backing up my main database from one server (A) and restoring it to two
different servers (B and C). Here is the process.
#1. On server A, I do a full DB backup once a day and transaction log backup
once an hour. Before the next daily full backup occurs, the transaction log
file will be copied to a different folder and renamed to log2. Then the
tranaction log will be reinitialized.
#2. On server B, I do the database full restore and each transaction log
restore as soon as the backup is complete in server A. For example, if the
full backup happens at 2:00 am, the restore will occur as soon as it is done.
The transaction backup is happening hourlty so the restore will happen
hourly.
#3. On server C, I will only do the restore of the full backup once. Then I
will restore the transaction log from the log2 that I copied in step 1
above.
I do a simple test, it seems to be working fine. My 'unclear' part is on
#3. The source database is having a full backup daily. In my step 3, once
the initial full restore is done, I only rely on the transaction log (log2)
to keep server C data up to date. There will be no full database restore on
server C unless it is necessary. I believe it is ok unless someone can tell
me there is a hole somewhere in my thinking.
Any help on this is very much appreciated.
Wingman
Let me re-phrase your question, so you can see if I understand it:
On one of the restore server, you only want to restore the database backup once, and then skip the
database backups performed on the production server and only restore the log backups performed on
the production server?
Yes, that is fine. A database backup doesn't break the chain of log backups. Just be careful with
setting the production database to simple recovery or explicitly empty the log without backing up,
as that will break the log backups. This will of course affect #2 as well as #3, but since you are
periodically restoring the database backups on #2 it will self-repair within 24 hours.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:E3376109-4065-4705-AB71-A6EDA5104B64@.microsoft.com...
> We are using SQL 2K with sp4. I like someone to verify what I am doing is
> correct. and there is no potential problem.
> I am backing up my main database from one server (A) and restoring it to two
> different servers (B and C). Here is the process.
> #1. On server A, I do a full DB backup once a day and transaction log backup
> once an hour. Before the next daily full backup occurs, the transaction log
> file will be copied to a different folder and renamed to log2. Then the
> tranaction log will be reinitialized.
> #2. On server B, I do the database full restore and each transaction log
> restore as soon as the backup is complete in server A. For example, if the
> full backup happens at 2:00 am, the restore will occur as soon as it is done.
> The transaction backup is happening hourlty so the restore will happen
> hourly.
> #3. On server C, I will only do the restore of the full backup once. Then I
> will restore the transaction log from the log2 that I copied in step 1
> above.
> I do a simple test, it seems to be working fine. My 'unclear' part is on
> #3. The source database is having a full backup daily. In my step 3, once
> the initial full restore is done, I only rely on the transaction log (log2)
> to keep server C data up to date. There will be no full database restore on
> server C unless it is necessary. I believe it is ok unless someone can tell
> me there is a hole somewhere in my thinking.
> Any help on this is very much appreciated.
> Wingman
|||Yes, that is exactly what I want to ask. You read my mind. And your answer
is exactly what I thought it would be. Thank man!!
Wingman
"Wingman" wrote:
> We are using SQL 2K with sp4. I like someone to verify what I am doing is
> correct. and there is no potential problem.
> I am backing up my main database from one server (A) and restoring it to two
> different servers (B and C). Here is the process.
> #1. On server A, I do a full DB backup once a day and transaction log backup
> once an hour. Before the next daily full backup occurs, the transaction log
> file will be copied to a different folder and renamed to log2. Then the
> tranaction log will be reinitialized.
> #2. On server B, I do the database full restore and each transaction log
> restore as soon as the backup is complete in server A. For example, if the
> full backup happens at 2:00 am, the restore will occur as soon as it is done.
> The transaction backup is happening hourlty so the restore will happen
> hourly.
> #3. On server C, I will only do the restore of the full backup once. Then I
> will restore the transaction log from the log2 that I copied in step 1
> above.
> I do a simple test, it seems to be working fine. My 'unclear' part is on
> #3. The source database is having a full backup daily. In my step 3, once
> the initial full restore is done, I only rely on the transaction log (log2)
> to keep server C data up to date. There will be no full database restore on
> server C unless it is necessary. I believe it is ok unless someone can tell
> me there is a hole somewhere in my thinking.
> Any help on this is very much appreciated.
> Wingman
correct. and there is no potential problem.
I am backing up my main database from one server (A) and restoring it to two
different servers (B and C). Here is the process.
#1. On server A, I do a full DB backup once a day and transaction log backup
once an hour. Before the next daily full backup occurs, the transaction log
file will be copied to a different folder and renamed to log2. Then the
tranaction log will be reinitialized.
#2. On server B, I do the database full restore and each transaction log
restore as soon as the backup is complete in server A. For example, if the
full backup happens at 2:00 am, the restore will occur as soon as it is done.
The transaction backup is happening hourlty so the restore will happen
hourly.
#3. On server C, I will only do the restore of the full backup once. Then I
will restore the transaction log from the log2 that I copied in step 1
above.
I do a simple test, it seems to be working fine. My 'unclear' part is on
#3. The source database is having a full backup daily. In my step 3, once
the initial full restore is done, I only rely on the transaction log (log2)
to keep server C data up to date. There will be no full database restore on
server C unless it is necessary. I believe it is ok unless someone can tell
me there is a hole somewhere in my thinking.
Any help on this is very much appreciated.
Wingman
Let me re-phrase your question, so you can see if I understand it:
On one of the restore server, you only want to restore the database backup once, and then skip the
database backups performed on the production server and only restore the log backups performed on
the production server?
Yes, that is fine. A database backup doesn't break the chain of log backups. Just be careful with
setting the production database to simple recovery or explicitly empty the log without backing up,
as that will break the log backups. This will of course affect #2 as well as #3, but since you are
periodically restoring the database backups on #2 it will self-repair within 24 hours.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:E3376109-4065-4705-AB71-A6EDA5104B64@.microsoft.com...
> We are using SQL 2K with sp4. I like someone to verify what I am doing is
> correct. and there is no potential problem.
> I am backing up my main database from one server (A) and restoring it to two
> different servers (B and C). Here is the process.
> #1. On server A, I do a full DB backup once a day and transaction log backup
> once an hour. Before the next daily full backup occurs, the transaction log
> file will be copied to a different folder and renamed to log2. Then the
> tranaction log will be reinitialized.
> #2. On server B, I do the database full restore and each transaction log
> restore as soon as the backup is complete in server A. For example, if the
> full backup happens at 2:00 am, the restore will occur as soon as it is done.
> The transaction backup is happening hourlty so the restore will happen
> hourly.
> #3. On server C, I will only do the restore of the full backup once. Then I
> will restore the transaction log from the log2 that I copied in step 1
> above.
> I do a simple test, it seems to be working fine. My 'unclear' part is on
> #3. The source database is having a full backup daily. In my step 3, once
> the initial full restore is done, I only rely on the transaction log (log2)
> to keep server C data up to date. There will be no full database restore on
> server C unless it is necessary. I believe it is ok unless someone can tell
> me there is a hole somewhere in my thinking.
> Any help on this is very much appreciated.
> Wingman
|||Yes, that is exactly what I want to ask. You read my mind. And your answer
is exactly what I thought it would be. Thank man!!
Wingman
"Wingman" wrote:
> We are using SQL 2K with sp4. I like someone to verify what I am doing is
> correct. and there is no potential problem.
> I am backing up my main database from one server (A) and restoring it to two
> different servers (B and C). Here is the process.
> #1. On server A, I do a full DB backup once a day and transaction log backup
> once an hour. Before the next daily full backup occurs, the transaction log
> file will be copied to a different folder and renamed to log2. Then the
> tranaction log will be reinitialized.
> #2. On server B, I do the database full restore and each transaction log
> restore as soon as the backup is complete in server A. For example, if the
> full backup happens at 2:00 am, the restore will occur as soon as it is done.
> The transaction backup is happening hourlty so the restore will happen
> hourly.
> #3. On server C, I will only do the restore of the full backup once. Then I
> will restore the transaction log from the log2 that I copied in step 1
> above.
> I do a simple test, it seems to be working fine. My 'unclear' part is on
> #3. The source database is having a full backup daily. In my step 3, once
> the initial full restore is done, I only rely on the transaction log (log2)
> to keep server C data up to date. There will be no full database restore on
> server C unless it is necessary. I believe it is ok unless someone can tell
> me there is a hole somewhere in my thinking.
> Any help on this is very much appreciated.
> Wingman
database and log backup
We are using SQL 2K with sp4. I like someone to verify what I am doing is
correct. and there is no potential problem.
I am backing up my main database from one server (A) and restoring it to two
different servers (B and C). Here is the process.
#1. On server A, I do a full DB backup once a day and transaction log backup
once an hour. Before the next daily full backup occurs, the transaction log
file will be copied to a different folder and renamed to log2. Then the
tranaction log will be reinitialized.
#2. On server B, I do the database full restore and each transaction log
restore as soon as the backup is complete in server A. For example, if the
full backup happens at 2:00 am, the restore will occur as soon as it is done
.
The transaction backup is happening hourlty so the restore will happen
hourly.
#3. On server C, I will only do the restore of the full backup once. Then I
will restore the transaction log from the log2 that I copied in step 1
above.
I do a simple test, it seems to be working fine. My 'unclear' part is on
#3. The source database is having a full backup daily. In my step 3, once
the initial full restore is done, I only rely on the transaction log (log2)
to keep server C data up to date. There will be no full database restore o
n
server C unless it is necessary. I believe it is ok unless someone can tell
me there is a hole somewhere in my thinking.
Any help on this is very much appreciated.
WingmanLet me re-phrase your question, so you can see if I understand it:
On one of the restore server, you only want to restore the database backup o
nce, and then skip the
database backups performed on the production server and only restore the log
backups performed on
the production server?
Yes, that is fine. A database backup doesn't break the chain of log backups.
Just be careful with
setting the production database to simple recovery or explicitly empty the l
og without backing up,
as that will break the log backups. This will of course affect #2 as well as
#3, but since you are
periodically restoring the database backups on #2 it will self-repair within
24 hours.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:E3376109-4065-4705-AB71-A6EDA5104B64@.microsoft.com...
> We are using SQL 2K with sp4. I like someone to verify what I am doing is
> correct. and there is no potential problem.
> I am backing up my main database from one server (A) and restoring it to t
wo
> different servers (B and C). Here is the process.
> #1. On server A, I do a full DB backup once a day and transaction log back
up
> once an hour. Before the next daily full backup occurs, the transaction l
og
> file will be copied to a different folder and renamed to log2. Then the
> tranaction log will be reinitialized.
> #2. On server B, I do the database full restore and each transaction log
> restore as soon as the backup is complete in server A. For example, if th
e
> full backup happens at 2:00 am, the restore will occur as soon as it is do
ne.
> The transaction backup is happening hourlty so the restore will happen
> hourly.
> #3. On server C, I will only do the restore of the full backup once. Then
I
> will restore the transaction log from the log2 that I copied in step 1
> above.
> I do a simple test, it seems to be working fine. My 'unclear' part is on
> #3. The source database is having a full backup daily. In my step 3, onc
e
> the initial full restore is done, I only rely on the transaction log (log2
)
> to keep server C data up to date. There will be no full database restore
on
> server C unless it is necessary. I believe it is ok unless someone can te
ll
> me there is a hole somewhere in my thinking.
> Any help on this is very much appreciated.
> Wingman|||Yes, that is exactly what I want to ask. You read my mind. And your answer
is exactly what I thought it would be. Thank man!!
Wingman
"Wingman" wrote:
> We are using SQL 2K with sp4. I like someone to verify what I am doing is
> correct. and there is no potential problem.
> I am backing up my main database from one server (A) and restoring it to t
wo
> different servers (B and C). Here is the process.
> #1. On server A, I do a full DB backup once a day and transaction log back
up
> once an hour. Before the next daily full backup occurs, the transaction l
og
> file will be copied to a different folder and renamed to log2. Then the
> tranaction log will be reinitialized.
> #2. On server B, I do the database full restore and each transaction log
> restore as soon as the backup is complete in server A. For example, if th
e
> full backup happens at 2:00 am, the restore will occur as soon as it is do
ne.
> The transaction backup is happening hourlty so the restore will happen
> hourly.
> #3. On server C, I will only do the restore of the full backup once. Then
I
> will restore the transaction log from the log2 that I copied in step 1
> above.
> I do a simple test, it seems to be working fine. My 'unclear' part is on
> #3. The source database is having a full backup daily. In my step 3, onc
e
> the initial full restore is done, I only rely on the transaction log (log2
)
> to keep server C data up to date. There will be no full database restore
on
> server C unless it is necessary. I believe it is ok unless someone can te
ll
> me there is a hole somewhere in my thinking.
> Any help on this is very much appreciated.
> Wingman
correct. and there is no potential problem.
I am backing up my main database from one server (A) and restoring it to two
different servers (B and C). Here is the process.
#1. On server A, I do a full DB backup once a day and transaction log backup
once an hour. Before the next daily full backup occurs, the transaction log
file will be copied to a different folder and renamed to log2. Then the
tranaction log will be reinitialized.
#2. On server B, I do the database full restore and each transaction log
restore as soon as the backup is complete in server A. For example, if the
full backup happens at 2:00 am, the restore will occur as soon as it is done
.
The transaction backup is happening hourlty so the restore will happen
hourly.
#3. On server C, I will only do the restore of the full backup once. Then I
will restore the transaction log from the log2 that I copied in step 1
above.
I do a simple test, it seems to be working fine. My 'unclear' part is on
#3. The source database is having a full backup daily. In my step 3, once
the initial full restore is done, I only rely on the transaction log (log2)
to keep server C data up to date. There will be no full database restore o
n
server C unless it is necessary. I believe it is ok unless someone can tell
me there is a hole somewhere in my thinking.
Any help on this is very much appreciated.
WingmanLet me re-phrase your question, so you can see if I understand it:
On one of the restore server, you only want to restore the database backup o
nce, and then skip the
database backups performed on the production server and only restore the log
backups performed on
the production server?
Yes, that is fine. A database backup doesn't break the chain of log backups.
Just be careful with
setting the production database to simple recovery or explicitly empty the l
og without backing up,
as that will break the log backups. This will of course affect #2 as well as
#3, but since you are
periodically restoring the database backups on #2 it will self-repair within
24 hours.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:E3376109-4065-4705-AB71-A6EDA5104B64@.microsoft.com...
> We are using SQL 2K with sp4. I like someone to verify what I am doing is
> correct. and there is no potential problem.
> I am backing up my main database from one server (A) and restoring it to t
wo
> different servers (B and C). Here is the process.
> #1. On server A, I do a full DB backup once a day and transaction log back
up
> once an hour. Before the next daily full backup occurs, the transaction l
og
> file will be copied to a different folder and renamed to log2. Then the
> tranaction log will be reinitialized.
> #2. On server B, I do the database full restore and each transaction log
> restore as soon as the backup is complete in server A. For example, if th
e
> full backup happens at 2:00 am, the restore will occur as soon as it is do
ne.
> The transaction backup is happening hourlty so the restore will happen
> hourly.
> #3. On server C, I will only do the restore of the full backup once. Then
I
> will restore the transaction log from the log2 that I copied in step 1
> above.
> I do a simple test, it seems to be working fine. My 'unclear' part is on
> #3. The source database is having a full backup daily. In my step 3, onc
e
> the initial full restore is done, I only rely on the transaction log (log2
)
> to keep server C data up to date. There will be no full database restore
on
> server C unless it is necessary. I believe it is ok unless someone can te
ll
> me there is a hole somewhere in my thinking.
> Any help on this is very much appreciated.
> Wingman|||Yes, that is exactly what I want to ask. You read my mind. And your answer
is exactly what I thought it would be. Thank man!!
Wingman
"Wingman" wrote:
> We are using SQL 2K with sp4. I like someone to verify what I am doing is
> correct. and there is no potential problem.
> I am backing up my main database from one server (A) and restoring it to t
wo
> different servers (B and C). Here is the process.
> #1. On server A, I do a full DB backup once a day and transaction log back
up
> once an hour. Before the next daily full backup occurs, the transaction l
og
> file will be copied to a different folder and renamed to log2. Then the
> tranaction log will be reinitialized.
> #2. On server B, I do the database full restore and each transaction log
> restore as soon as the backup is complete in server A. For example, if th
e
> full backup happens at 2:00 am, the restore will occur as soon as it is do
ne.
> The transaction backup is happening hourlty so the restore will happen
> hourly.
> #3. On server C, I will only do the restore of the full backup once. Then
I
> will restore the transaction log from the log2 that I copied in step 1
> above.
> I do a simple test, it seems to be working fine. My 'unclear' part is on
> #3. The source database is having a full backup daily. In my step 3, onc
e
> the initial full restore is done, I only rely on the transaction log (log2
)
> to keep server C data up to date. There will be no full database restore
on
> server C unless it is necessary. I believe it is ok unless someone can te
ll
> me there is a hole somewhere in my thinking.
> Any help on this is very much appreciated.
> Wingman
database and log backup
We are using SQL 2K with sp4. I like someone to verify what I am doing is
correct. and there is no potential problem.
I am backing up my main database from one server (A) and restoring it to two
different servers (B and C). Here is the process.
#1. On server A, I do a full DB backup once a day and transaction log backup
once an hour. Before the next daily full backup occurs, the transaction log
file will be copied to a different folder and renamed to log2. Then the
tranaction log will be reinitialized.
#2. On server B, I do the database full restore and each transaction log
restore as soon as the backup is complete in server A. For example, if the
full backup happens at 2:00 am, the restore will occur as soon as it is done.
The transaction backup is happening hourlty so the restore will happen
hourly.
#3. On server C, I will only do the restore of the full backup once. Then I
will restore the transaction log from the log2 that I copied in step 1
above.
I do a simple test, it seems to be working fine. My 'unclear' part is on
#3. The source database is having a full backup daily. In my step 3, once
the initial full restore is done, I only rely on the transaction log (log2)
to keep server C data up to date. There will be no full database restore on
server C unless it is necessary. I believe it is ok unless someone can tell
me there is a hole somewhere in my thinking.
Any help on this is very much appreciated.
WingmanLet me re-phrase your question, so you can see if I understand it:
On one of the restore server, you only want to restore the database backup once, and then skip the
database backups performed on the production server and only restore the log backups performed on
the production server?
Yes, that is fine. A database backup doesn't break the chain of log backups. Just be careful with
setting the production database to simple recovery or explicitly empty the log without backing up,
as that will break the log backups. This will of course affect #2 as well as #3, but since you are
periodically restoring the database backups on #2 it will self-repair within 24 hours.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:E3376109-4065-4705-AB71-A6EDA5104B64@.microsoft.com...
> We are using SQL 2K with sp4. I like someone to verify what I am doing is
> correct. and there is no potential problem.
> I am backing up my main database from one server (A) and restoring it to two
> different servers (B and C). Here is the process.
> #1. On server A, I do a full DB backup once a day and transaction log backup
> once an hour. Before the next daily full backup occurs, the transaction log
> file will be copied to a different folder and renamed to log2. Then the
> tranaction log will be reinitialized.
> #2. On server B, I do the database full restore and each transaction log
> restore as soon as the backup is complete in server A. For example, if the
> full backup happens at 2:00 am, the restore will occur as soon as it is done.
> The transaction backup is happening hourlty so the restore will happen
> hourly.
> #3. On server C, I will only do the restore of the full backup once. Then I
> will restore the transaction log from the log2 that I copied in step 1
> above.
> I do a simple test, it seems to be working fine. My 'unclear' part is on
> #3. The source database is having a full backup daily. In my step 3, once
> the initial full restore is done, I only rely on the transaction log (log2)
> to keep server C data up to date. There will be no full database restore on
> server C unless it is necessary. I believe it is ok unless someone can tell
> me there is a hole somewhere in my thinking.
> Any help on this is very much appreciated.
> Wingman|||what you are doing is known as log shipping but you are doing it
manually , if you are using Enterprise Edition you can configure it as
automated process using Wizard.
I feel there is no need of task of Step #2 of daily restoring full
backup.
Books online has much more information reagrding logshipping.
Regards
Amish Shah|||Yes, that is exactly what I want to ask. You read my mind. And your answer
is exactly what I thought it would be. Thank man!!
Wingman
"Wingman" wrote:
> We are using SQL 2K with sp4. I like someone to verify what I am doing is
> correct. and there is no potential problem.
> I am backing up my main database from one server (A) and restoring it to two
> different servers (B and C). Here is the process.
> #1. On server A, I do a full DB backup once a day and transaction log backup
> once an hour. Before the next daily full backup occurs, the transaction log
> file will be copied to a different folder and renamed to log2. Then the
> tranaction log will be reinitialized.
> #2. On server B, I do the database full restore and each transaction log
> restore as soon as the backup is complete in server A. For example, if the
> full backup happens at 2:00 am, the restore will occur as soon as it is done.
> The transaction backup is happening hourlty so the restore will happen
> hourly.
> #3. On server C, I will only do the restore of the full backup once. Then I
> will restore the transaction log from the log2 that I copied in step 1
> above.
> I do a simple test, it seems to be working fine. My 'unclear' part is on
> #3. The source database is having a full backup daily. In my step 3, once
> the initial full restore is done, I only rely on the transaction log (log2)
> to keep server C data up to date. There will be no full database restore on
> server C unless it is necessary. I believe it is ok unless someone can tell
> me there is a hole somewhere in my thinking.
> Any help on this is very much appreciated.
> Wingman
correct. and there is no potential problem.
I am backing up my main database from one server (A) and restoring it to two
different servers (B and C). Here is the process.
#1. On server A, I do a full DB backup once a day and transaction log backup
once an hour. Before the next daily full backup occurs, the transaction log
file will be copied to a different folder and renamed to log2. Then the
tranaction log will be reinitialized.
#2. On server B, I do the database full restore and each transaction log
restore as soon as the backup is complete in server A. For example, if the
full backup happens at 2:00 am, the restore will occur as soon as it is done.
The transaction backup is happening hourlty so the restore will happen
hourly.
#3. On server C, I will only do the restore of the full backup once. Then I
will restore the transaction log from the log2 that I copied in step 1
above.
I do a simple test, it seems to be working fine. My 'unclear' part is on
#3. The source database is having a full backup daily. In my step 3, once
the initial full restore is done, I only rely on the transaction log (log2)
to keep server C data up to date. There will be no full database restore on
server C unless it is necessary. I believe it is ok unless someone can tell
me there is a hole somewhere in my thinking.
Any help on this is very much appreciated.
WingmanLet me re-phrase your question, so you can see if I understand it:
On one of the restore server, you only want to restore the database backup once, and then skip the
database backups performed on the production server and only restore the log backups performed on
the production server?
Yes, that is fine. A database backup doesn't break the chain of log backups. Just be careful with
setting the production database to simple recovery or explicitly empty the log without backing up,
as that will break the log backups. This will of course affect #2 as well as #3, but since you are
periodically restoring the database backups on #2 it will self-repair within 24 hours.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:E3376109-4065-4705-AB71-A6EDA5104B64@.microsoft.com...
> We are using SQL 2K with sp4. I like someone to verify what I am doing is
> correct. and there is no potential problem.
> I am backing up my main database from one server (A) and restoring it to two
> different servers (B and C). Here is the process.
> #1. On server A, I do a full DB backup once a day and transaction log backup
> once an hour. Before the next daily full backup occurs, the transaction log
> file will be copied to a different folder and renamed to log2. Then the
> tranaction log will be reinitialized.
> #2. On server B, I do the database full restore and each transaction log
> restore as soon as the backup is complete in server A. For example, if the
> full backup happens at 2:00 am, the restore will occur as soon as it is done.
> The transaction backup is happening hourlty so the restore will happen
> hourly.
> #3. On server C, I will only do the restore of the full backup once. Then I
> will restore the transaction log from the log2 that I copied in step 1
> above.
> I do a simple test, it seems to be working fine. My 'unclear' part is on
> #3. The source database is having a full backup daily. In my step 3, once
> the initial full restore is done, I only rely on the transaction log (log2)
> to keep server C data up to date. There will be no full database restore on
> server C unless it is necessary. I believe it is ok unless someone can tell
> me there is a hole somewhere in my thinking.
> Any help on this is very much appreciated.
> Wingman|||what you are doing is known as log shipping but you are doing it
manually , if you are using Enterprise Edition you can configure it as
automated process using Wizard.
I feel there is no need of task of Step #2 of daily restoring full
backup.
Books online has much more information reagrding logshipping.
Regards
Amish Shah|||Yes, that is exactly what I want to ask. You read my mind. And your answer
is exactly what I thought it would be. Thank man!!
Wingman
"Wingman" wrote:
> We are using SQL 2K with sp4. I like someone to verify what I am doing is
> correct. and there is no potential problem.
> I am backing up my main database from one server (A) and restoring it to two
> different servers (B and C). Here is the process.
> #1. On server A, I do a full DB backup once a day and transaction log backup
> once an hour. Before the next daily full backup occurs, the transaction log
> file will be copied to a different folder and renamed to log2. Then the
> tranaction log will be reinitialized.
> #2. On server B, I do the database full restore and each transaction log
> restore as soon as the backup is complete in server A. For example, if the
> full backup happens at 2:00 am, the restore will occur as soon as it is done.
> The transaction backup is happening hourlty so the restore will happen
> hourly.
> #3. On server C, I will only do the restore of the full backup once. Then I
> will restore the transaction log from the log2 that I copied in step 1
> above.
> I do a simple test, it seems to be working fine. My 'unclear' part is on
> #3. The source database is having a full backup daily. In my step 3, once
> the initial full restore is done, I only rely on the transaction log (log2)
> to keep server C data up to date. There will be no full database restore on
> server C unless it is necessary. I believe it is ok unless someone can tell
> me there is a hole somewhere in my thinking.
> Any help on this is very much appreciated.
> Wingman
Thursday, March 8, 2012
Database access by machines running SQL Servers 2005 32-bit and 64-bit
Hello
We have 1 machine running SQL Server 2005 x64 (64 bit). The other machine is backing this SQL Server up. It is a 32-bit machine, so it requires (?) a 32-bit version of SQL Server 2005.
Would this back-up machine work correctly on a database previously managed by a 64-bit machine and vice versa.
Thank you!
Yes, in terms of connection and administration you won′t feel any difference. But you only can install 32bit software on a 32bit system.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
If my DEV box is 32 bit and my PROD box is 64 bit:
can I deploy (or migrate) from DEV to PROD. The DEV box would have the 32bit SQL Server and the PROD would have the 64 bit SQL server. But if I deploy what I wrote and created there on 32 bit will it run on the 64 bit?
CLR stored procedures?|||No, the things that were developed on the DEv machine can be deployed without any problem on the production machine as from your description. On the Prod system the 64bit framework will take care of the assemblies.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Subscribe to:
Posts (Atom)