When using the Database Maintenance Plan WIzard to setup log shipping I have
the following problem:
While Adding a Destination Database, usually when you select a server from
the server list, the Transaction Log Desination Directory is automatically
filled in with a default.
When I select one particular server from the list, this does not get
automatically filled in and the server name that I selected from the list
does not show up in the Server Name: field, even though it appeared in the
drop down list. Other server do not exhibit this behavior but function
correctly.
From what I understand, the server will not show up in the list if it is not
SQL Entreprise Edition, so I don't think that should be the problem.
Any ideas?
Thanks,
Matthew Bando
Matthew.Bando@.REMOVE-csctgi.com
Easy solution. Evidently, having the destination (secondary) server
registered in EM is not enough. I actually had to open an active connectino
in EM to the secondary server before startnig the Database Maintenance Plan
Wizard.
Matthew
"Matthew Bando" wrote:
> When using the Database Maintenance Plan WIzard to setup log shipping I have
> the following problem:
> While Adding a Destination Database, usually when you select a server from
> the server list, the Transaction Log Desination Directory is automatically
> filled in with a default.
> When I select one particular server from the list, this does not get
> automatically filled in and the server name that I selected from the list
> does not show up in the Server Name: field, even though it appeared in the
> drop down list. Other server do not exhibit this behavior but function
> correctly.
> From what I understand, the server will not show up in the list if it is not
> SQL Entreprise Edition, so I don't think that should be the problem.
> Any ideas?
> Thanks,
> Matthew Bando
> Matthew.Bando@.REMOVE-csctgi.com
Showing posts with label plan. Show all posts
Showing posts with label plan. Show all posts
Wednesday, March 28, 2012
Log Shipping Setup Problem
When using the Database Maintenance Plan WIzard to setup log shipping I have
the following problem:
While Adding a Destination Database, usually when you select a server from
the server list, the Transaction Log Desination Directory is automatically
filled in with a default.
When I select one particular server from the list, this does not get
automatically filled in and the server name that I selected from the list
does not show up in the Server Name: field, even though it appeared in the
drop down list. Other server do not exhibit this behavior but function
correctly.
From what I understand, the server will not show up in the list if it is not
SQL Entreprise Edition, so I don't think that should be the problem.
Any ideas?
Thanks,
Matthew Bando
Matthew.Bando@.REMOVE-csctgi.comEasy solution. Evidently, having the destination (secondary) server
registered in EM is not enough. I actually had to open an active connectino
in EM to the secondary server before startnig the Database Maintenance Plan
Wizard.
Matthew
"Matthew Bando" wrote:
> When using the Database Maintenance Plan WIzard to setup log shipping I have
> the following problem:
> While Adding a Destination Database, usually when you select a server from
> the server list, the Transaction Log Desination Directory is automatically
> filled in with a default.
> When I select one particular server from the list, this does not get
> automatically filled in and the server name that I selected from the list
> does not show up in the Server Name: field, even though it appeared in the
> drop down list. Other server do not exhibit this behavior but function
> correctly.
> From what I understand, the server will not show up in the list if it is not
> SQL Entreprise Edition, so I don't think that should be the problem.
> Any ideas?
> Thanks,
> Matthew Bando
> Matthew.Bando@.REMOVE-csctgi.com
the following problem:
While Adding a Destination Database, usually when you select a server from
the server list, the Transaction Log Desination Directory is automatically
filled in with a default.
When I select one particular server from the list, this does not get
automatically filled in and the server name that I selected from the list
does not show up in the Server Name: field, even though it appeared in the
drop down list. Other server do not exhibit this behavior but function
correctly.
From what I understand, the server will not show up in the list if it is not
SQL Entreprise Edition, so I don't think that should be the problem.
Any ideas?
Thanks,
Matthew Bando
Matthew.Bando@.REMOVE-csctgi.comEasy solution. Evidently, having the destination (secondary) server
registered in EM is not enough. I actually had to open an active connectino
in EM to the secondary server before startnig the Database Maintenance Plan
Wizard.
Matthew
"Matthew Bando" wrote:
> When using the Database Maintenance Plan WIzard to setup log shipping I have
> the following problem:
> While Adding a Destination Database, usually when you select a server from
> the server list, the Transaction Log Desination Directory is automatically
> filled in with a default.
> When I select one particular server from the list, this does not get
> automatically filled in and the server name that I selected from the list
> does not show up in the Server Name: field, even though it appeared in the
> drop down list. Other server do not exhibit this behavior but function
> correctly.
> From what I understand, the server will not show up in the list if it is not
> SQL Entreprise Edition, so I don't think that should be the problem.
> Any ideas?
> Thanks,
> Matthew Bando
> Matthew.Bando@.REMOVE-csctgi.com
Log Shipping Setup Problem
When using the Database Maintenance Plan WIzard to setup log shipping I have
the following problem:
While Adding a Destination Database, usually when you select a server from
the server list, the Transaction Log Desination Directory is automatically
filled in with a default.
When I select one particular server from the list, this does not get
automatically filled in and the server name that I selected from the list
does not show up in the Server Name: field, even though it appeared in the
drop down list. Other server do not exhibit this behavior but function
correctly.
From what I understand, the server will not show up in the list if it is not
SQL Entreprise Edition, so I don't think that should be the problem.
Any ideas?
Thanks,
Matthew Bando
Matthew.Bando@.REMOVE-csctgi.comEasy solution. Evidently, having the destination (secondary) server
registered in EM is not enough. I actually had to open an active connectino
in EM to the secondary server before startnig the Database Maintenance Plan
Wizard.
Matthew
"Matthew Bando" wrote:
> When using the Database Maintenance Plan WIzard to setup log shipping I ha
ve
> the following problem:
> While Adding a Destination Database, usually when you select a server from
> the server list, the Transaction Log Desination Directory is automatically
> filled in with a default.
> When I select one particular server from the list, this does not get
> automatically filled in and the server name that I selected from the list
> does not show up in the Server Name: field, even though it appeared in the
> drop down list. Other server do not exhibit this behavior but function
> correctly.
> From what I understand, the server will not show up in the list if it is n
ot
> SQL Entreprise Edition, so I don't think that should be the problem.
> Any ideas?
> Thanks,
> Matthew Bando
> Matthew.Bando@.REMOVE-csctgi.com
the following problem:
While Adding a Destination Database, usually when you select a server from
the server list, the Transaction Log Desination Directory is automatically
filled in with a default.
When I select one particular server from the list, this does not get
automatically filled in and the server name that I selected from the list
does not show up in the Server Name: field, even though it appeared in the
drop down list. Other server do not exhibit this behavior but function
correctly.
From what I understand, the server will not show up in the list if it is not
SQL Entreprise Edition, so I don't think that should be the problem.
Any ideas?
Thanks,
Matthew Bando
Matthew.Bando@.REMOVE-csctgi.comEasy solution. Evidently, having the destination (secondary) server
registered in EM is not enough. I actually had to open an active connectino
in EM to the secondary server before startnig the Database Maintenance Plan
Wizard.
Matthew
"Matthew Bando" wrote:
> When using the Database Maintenance Plan WIzard to setup log shipping I ha
ve
> the following problem:
> While Adding a Destination Database, usually when you select a server from
> the server list, the Transaction Log Desination Directory is automatically
> filled in with a default.
> When I select one particular server from the list, this does not get
> automatically filled in and the server name that I selected from the list
> does not show up in the Server Name: field, even though it appeared in the
> drop down list. Other server do not exhibit this behavior but function
> correctly.
> From what I understand, the server will not show up in the list if it is n
ot
> SQL Entreprise Edition, so I don't think that should be the problem.
> Any ideas?
> Thanks,
> Matthew Bando
> Matthew.Bando@.REMOVE-csctgi.com
Friday, March 23, 2012
Log Shipping question
Hi!
I managed to make a plan of log shipping and have some questions.
In the first trn back up I recieved an error
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'D:\logshipping\Pergam2_tlog_200312250945.TRN'. Device error or device
off-line. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
abnormally.
All other back ups are fine and databases seeens to be identical. But Log
shipping monitor shows out of sync ?!!!
What it can be?
if backup/restore or restore fails what will be with databases in the next
step when all will work fine?
Thanks
DmitryI have seen messages like this sometimes on our log
shipping installation too. I believe Sql Server sometimes
starts reading the log files even before they have been
completely transferred. This seems to be best ignored. Set
your monitor to a higher out of sync value.
If the databases get out of sync, the best way is to back
up and restore them and restart log shipping.
>--Original Message--
>Hi!
>I managed to make a plan of log shipping and have some
questions.
>In the first trn back up I recieved an error
>[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201:
[Microsoft][ODBC SQL
>Server Driver][SQL Server]Cannot open backup device
>'D:\logshipping\Pergam2_tlog_200312250945.TRN'. Device
error or device
>off-line. See the SQL Server error log for more details.
>[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE
LOG is terminating
>abnormally.
>All other back ups are fine and databases seeens to be
identical. But Log
>shipping monitor shows out of sync ?!!!
>What it can be?
>if backup/restore or restore fails what will be with
databases in the next
>step when all will work fine?
>Thanks
>Dmitry
>
>.
>
I managed to make a plan of log shipping and have some questions.
In the first trn back up I recieved an error
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'D:\logshipping\Pergam2_tlog_200312250945.TRN'. Device error or device
off-line. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
abnormally.
All other back ups are fine and databases seeens to be identical. But Log
shipping monitor shows out of sync ?!!!
What it can be?
if backup/restore or restore fails what will be with databases in the next
step when all will work fine?
Thanks
DmitryI have seen messages like this sometimes on our log
shipping installation too. I believe Sql Server sometimes
starts reading the log files even before they have been
completely transferred. This seems to be best ignored. Set
your monitor to a higher out of sync value.
If the databases get out of sync, the best way is to back
up and restore them and restart log shipping.
>--Original Message--
>Hi!
>I managed to make a plan of log shipping and have some
questions.
>In the first trn back up I recieved an error
>[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201:
[Microsoft][ODBC SQL
>Server Driver][SQL Server]Cannot open backup device
>'D:\logshipping\Pergam2_tlog_200312250945.TRN'. Device
error or device
>off-line. See the SQL Server error log for more details.
>[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE
LOG is terminating
>abnormally.
>All other back ups are fine and databases seeens to be
identical. But Log
>shipping monitor shows out of sync ?!!!
>What it can be?
>if backup/restore or restore fails what will be with
databases in the next
>step when all will work fine?
>Thanks
>Dmitry
>
>.
>
Wednesday, March 21, 2012
Log Shipping Plan fails (event: 208)
Hi,
We've been using logshipping successfully for over a year,
but recently we are experiencing more and more failures
relating to the Transaction Log Backup Job. We are
currently log shipping 30 databases from a W2K(SP4)
SQL2000(SP3) server to a W2K3 Server (SQL2000SP3).
The backup jobs aren't consistently failing, which is
making this harder to diagnose. If I manually run the jobs
from Enterprise Manager they work fine, and the only error
messages that i'm seeing are like the following:
EventID: 208
SQL Server Scheduled Job 'Transaction Log Backup Job for DB
Maintenance Plan 'Unity_Server1 Log Shipping''
(0x318A0055279BA840AABD24DA1BA5B440) - Status: Failed -
Invoked on: 2004-09-20 17:15:10 - Message: The job failed.
The Job was invoked by Schedule 59 (Schedule 1). The last
step to run was step 1 (Step 1).
There aren't any issues with disk space, and I don't think
this is a security issue, as the jobs aren't consistently
failing.
Hoping that someone has come across this issue before.
Thanks,
Martin
Martin,
I'm not too sure where this message originates from, but if it is the job,
then in the maintenance plan can you enable logging to the textfile and then
examine the resulting messages. I often just receive sqlmaint.exe failed
messages in the job, but in the textfile find out the real cause of the
problem.
Regards,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul Ibison wrote:
> Martin,
> I'm not too sure where this message originates from, but if it is the job,
> then in the maintenance plan can you enable logging to the textfile and then
> examine the resulting messages.
This is interesting - when I examine the folder containing the
log-shipping logs - whenever a job fails there is no log created.
However, successful log-shipping jobs do create a log file. So not only
is the job failing to complete, but it's also failing to create a log of
the failure.
Looking at the job history shows a complete list of all the log-shipping
jobs with corresponding results (failed or successful).
Thanks
Martin
|||Martin,
in that case you could use profiler. Some things are
incompatible with backups - eg file management operations
such as the ALTER DATABASE statement with either the ADD
FILE or REMOVE FILE options; shrink database or shrink
file - this includes autoshrink operations.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
We've been using logshipping successfully for over a year,
but recently we are experiencing more and more failures
relating to the Transaction Log Backup Job. We are
currently log shipping 30 databases from a W2K(SP4)
SQL2000(SP3) server to a W2K3 Server (SQL2000SP3).
The backup jobs aren't consistently failing, which is
making this harder to diagnose. If I manually run the jobs
from Enterprise Manager they work fine, and the only error
messages that i'm seeing are like the following:
EventID: 208
SQL Server Scheduled Job 'Transaction Log Backup Job for DB
Maintenance Plan 'Unity_Server1 Log Shipping''
(0x318A0055279BA840AABD24DA1BA5B440) - Status: Failed -
Invoked on: 2004-09-20 17:15:10 - Message: The job failed.
The Job was invoked by Schedule 59 (Schedule 1). The last
step to run was step 1 (Step 1).
There aren't any issues with disk space, and I don't think
this is a security issue, as the jobs aren't consistently
failing.
Hoping that someone has come across this issue before.
Thanks,
Martin
Martin,
I'm not too sure where this message originates from, but if it is the job,
then in the maintenance plan can you enable logging to the textfile and then
examine the resulting messages. I often just receive sqlmaint.exe failed
messages in the job, but in the textfile find out the real cause of the
problem.
Regards,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul Ibison wrote:
> Martin,
> I'm not too sure where this message originates from, but if it is the job,
> then in the maintenance plan can you enable logging to the textfile and then
> examine the resulting messages.
This is interesting - when I examine the folder containing the
log-shipping logs - whenever a job fails there is no log created.
However, successful log-shipping jobs do create a log file. So not only
is the job failing to complete, but it's also failing to create a log of
the failure.
Looking at the job history shows a complete list of all the log-shipping
jobs with corresponding results (failed or successful).
Thanks
Martin
|||Martin,
in that case you could use profiler. Some things are
incompatible with backups - eg file management operations
such as the ALTER DATABASE statement with either the ADD
FILE or REMOVE FILE options; shrink database or shrink
file - this includes autoshrink operations.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
Log Shipping out of sync
You need to recreate the Log Shipping Maintenance plan for the desired
database.
In order to deleted the current plan, you'll need to remove LS from the plan
first.
If the latest full db backup that you have was created after the error
occurred and you still have all of the log (and/or differential) backups
since the latest db backup then you can use these files to reinitialize db on
the secondary server instead of having to take a full backup of the db. This
would allow you to set up log shipping without having to impact performance
on your primary db.
check out these articles
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part4/c1361.mspx?mfr=true
MG
"Alex Delgado" wrote:
> Hi everyone,
> We have set up a log shipping between two SQL 2000 servers with a monitor
> server as well.
> The problem is that I've been on holidays, and the people that should check
> the databases didn't do it. The result is that the log shipping has been out
> ot sync since 18th april till now.
> We get the following error:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3242: [Microsoft][ODBC SQL
> Server Driver][SQL Server]The file on device
> 'C:\xx\LogShipIN\TRNs\xx_tlog_200704180300.TRN' is not a valid Microsoft Tape
> Format backup set.
> [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> abnormally.
> I've check the file and already exist in backup server, but it seems it
> wasn't copied correctly. Because it's a lot of days since the file fail to
> restore (it's from 18 th april), we don't have it on production server (we
> have only from 2 days ago).
> Does anybody knows how could I re-sync the log shipping on backup server
> again?
> Thansk a lot,
> Alex.
Hi Hurme,
Thanks a lot for your help.
I still have a doubt.
When you say:
" If the latest full db backup that you have was created after the error
occurred and you still have all of the log (and/or differential) backups
since the latest db backup then you can use these files to reinitialize db on
the secondary server instead of having to take a full backup of the db. This
would allow you to set up log shipping without having to impact performance
on your primary db. "
I have full db backup created after the error that we can restore on
secondary server. About the log backups, I understand you mean the trn files
that log shipping copies to secondary server and that it tries to restore,
don't you? If so, then my answer is yes, I also have them.
Should I run the restore with the following parameter: restore
"Hurme" wrote:
[vbcol=seagreen]
> You need to recreate the Log Shipping Maintenance plan for the desired
> database.
> In order to deleted the current plan, you'll need to remove LS from the plan
> first.
> If the latest full db backup that you have was created after the error
> occurred and you still have all of the log (and/or differential) backups
> since the latest db backup then you can use these files to reinitialize db on
> the secondary server instead of having to take a full backup of the db. This
> would allow you to set up log shipping without having to impact performance
> on your primary db.
> check out these articles
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx
> http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part4/c1361.mspx?mfr=true
>
> --
> MG
>
> "Alex Delgado" wrote:
|||Hi Hurme,
Thanks a lot for your help.
I still have a doubt.
When you say:
" If the latest full db backup that you have was created after the error
occurred and you still have all of the log (and/or differential) backups
since the latest db backup then you can use these files to reinitialize db on
the secondary server instead of having to take a full backup of the db. This
would allow you to set up log shipping without having to impact performance
on your primary db. "
I have full db backup created after the error that we can restore on
secondary server. About the log backups, I understand you mean the trn files
that log shipping copies to secondary server and that it tries to restore,
don't you? If so, then my answer is yes, I also have them.
Should I run the restore with the following parameter: restore with standby?
How should be the exact command to restore?
Thanks again,
Alex.
"Hurme" wrote:
[vbcol=seagreen]
> You need to recreate the Log Shipping Maintenance plan for the desired
> database.
> In order to deleted the current plan, you'll need to remove LS from the plan
> first.
> If the latest full db backup that you have was created after the error
> occurred and you still have all of the log (and/or differential) backups
> since the latest db backup then you can use these files to reinitialize db on
> the secondary server instead of having to take a full backup of the db. This
> would allow you to set up log shipping without having to impact performance
> on your primary db.
> check out these articles
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx
> http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part4/c1361.mspx?mfr=true
>
> --
> MG
>
> "Alex Delgado" wrote:
|||Yes, by log I meant the .trn files.
If you don't use standby, the backup database will be left offline and not
readable so that successive .trn (backup log) files can be restored.
If you restore with standby the backup db will be accessable in Read-Only
mode and still be able to have successive .trn files restored to it. The only
issue with this is that exclussive access to the db is still need to restore
a .trn so if some one is accessing the db they need to be kicked out of the
db before a restore can happen.
The restore with standby command should look like:
restore database <db_name>
from disk = 'c:\<backup>.BAK'
with standby = <undo_file_name>
likewise for .trn files
restore log <db_name>
from disk = 'c:\<log_backup>.trn'
with standby = <undo_file_name>
If you're not using it, try using Enterprise Manager to set up Log Shipping.
MG
"Alex Delgado" wrote:
[vbcol=seagreen]
> Hi Hurme,
> Thanks a lot for your help.
> I still have a doubt.
> When you say:
> " If the latest full db backup that you have was created after the error
> occurred and you still have all of the log (and/or differential) backups
> since the latest db backup then you can use these files to reinitialize db on
> the secondary server instead of having to take a full backup of the db. This
> would allow you to set up log shipping without having to impact performance
> on your primary db. "
> I have full db backup created after the error that we can restore on
> secondary server. About the log backups, I understand you mean the trn files
> that log shipping copies to secondary server and that it tries to restore,
> don't you? If so, then my answer is yes, I also have them.
> Should I run the restore with the following parameter: restore with standby?
> How should be the exact command to restore?
> Thanks again,
> Alex.
>
> "Hurme" wrote:
sql
database.
In order to deleted the current plan, you'll need to remove LS from the plan
first.
If the latest full db backup that you have was created after the error
occurred and you still have all of the log (and/or differential) backups
since the latest db backup then you can use these files to reinitialize db on
the secondary server instead of having to take a full backup of the db. This
would allow you to set up log shipping without having to impact performance
on your primary db.
check out these articles
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part4/c1361.mspx?mfr=true
MG
"Alex Delgado" wrote:
> Hi everyone,
> We have set up a log shipping between two SQL 2000 servers with a monitor
> server as well.
> The problem is that I've been on holidays, and the people that should check
> the databases didn't do it. The result is that the log shipping has been out
> ot sync since 18th april till now.
> We get the following error:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3242: [Microsoft][ODBC SQL
> Server Driver][SQL Server]The file on device
> 'C:\xx\LogShipIN\TRNs\xx_tlog_200704180300.TRN' is not a valid Microsoft Tape
> Format backup set.
> [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating
> abnormally.
> I've check the file and already exist in backup server, but it seems it
> wasn't copied correctly. Because it's a lot of days since the file fail to
> restore (it's from 18 th april), we don't have it on production server (we
> have only from 2 days ago).
> Does anybody knows how could I re-sync the log shipping on backup server
> again?
> Thansk a lot,
> Alex.
Hi Hurme,
Thanks a lot for your help.
I still have a doubt.
When you say:
" If the latest full db backup that you have was created after the error
occurred and you still have all of the log (and/or differential) backups
since the latest db backup then you can use these files to reinitialize db on
the secondary server instead of having to take a full backup of the db. This
would allow you to set up log shipping without having to impact performance
on your primary db. "
I have full db backup created after the error that we can restore on
secondary server. About the log backups, I understand you mean the trn files
that log shipping copies to secondary server and that it tries to restore,
don't you? If so, then my answer is yes, I also have them.
Should I run the restore with the following parameter: restore
"Hurme" wrote:
[vbcol=seagreen]
> You need to recreate the Log Shipping Maintenance plan for the desired
> database.
> In order to deleted the current plan, you'll need to remove LS from the plan
> first.
> If the latest full db backup that you have was created after the error
> occurred and you still have all of the log (and/or differential) backups
> since the latest db backup then you can use these files to reinitialize db on
> the secondary server instead of having to take a full backup of the db. This
> would allow you to set up log shipping without having to impact performance
> on your primary db.
> check out these articles
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx
> http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part4/c1361.mspx?mfr=true
>
> --
> MG
>
> "Alex Delgado" wrote:
|||Hi Hurme,
Thanks a lot for your help.
I still have a doubt.
When you say:
" If the latest full db backup that you have was created after the error
occurred and you still have all of the log (and/or differential) backups
since the latest db backup then you can use these files to reinitialize db on
the secondary server instead of having to take a full backup of the db. This
would allow you to set up log shipping without having to impact performance
on your primary db. "
I have full db backup created after the error that we can restore on
secondary server. About the log backups, I understand you mean the trn files
that log shipping copies to secondary server and that it tries to restore,
don't you? If so, then my answer is yes, I also have them.
Should I run the restore with the following parameter: restore with standby?
How should be the exact command to restore?
Thanks again,
Alex.
"Hurme" wrote:
[vbcol=seagreen]
> You need to recreate the Log Shipping Maintenance plan for the desired
> database.
> In order to deleted the current plan, you'll need to remove LS from the plan
> first.
> If the latest full db backup that you have was created after the error
> occurred and you still have all of the log (and/or differential) backups
> since the latest db backup then you can use these files to reinitialize db on
> the secondary server instead of having to take a full backup of the db. This
> would allow you to set up log shipping without having to impact performance
> on your primary db.
> check out these articles
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx
> http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part4/c1361.mspx?mfr=true
>
> --
> MG
>
> "Alex Delgado" wrote:
|||Yes, by log I meant the .trn files.
If you don't use standby, the backup database will be left offline and not
readable so that successive .trn (backup log) files can be restored.
If you restore with standby the backup db will be accessable in Read-Only
mode and still be able to have successive .trn files restored to it. The only
issue with this is that exclussive access to the db is still need to restore
a .trn so if some one is accessing the db they need to be kicked out of the
db before a restore can happen.
The restore with standby command should look like:
restore database <db_name>
from disk = 'c:\<backup>.BAK'
with standby = <undo_file_name>
likewise for .trn files
restore log <db_name>
from disk = 'c:\<log_backup>.trn'
with standby = <undo_file_name>
If you're not using it, try using Enterprise Manager to set up Log Shipping.
MG
"Alex Delgado" wrote:
[vbcol=seagreen]
> Hi Hurme,
> Thanks a lot for your help.
> I still have a doubt.
> When you say:
> " If the latest full db backup that you have was created after the error
> occurred and you still have all of the log (and/or differential) backups
> since the latest db backup then you can use these files to reinitialize db on
> the secondary server instead of having to take a full backup of the db. This
> would allow you to set up log shipping without having to impact performance
> on your primary db. "
> I have full db backup created after the error that we can restore on
> secondary server. About the log backups, I understand you mean the trn files
> that log shipping copies to secondary server and that it tries to restore,
> don't you? If so, then my answer is yes, I also have them.
> Should I run the restore with the following parameter: restore with standby?
> How should be the exact command to restore?
> Thanks again,
> Alex.
>
> "Hurme" wrote:
sql
Monday, March 12, 2012
Log shipping from SQL 2000 to SQL 2005
I have a DB on Server A ( SQL 2K) and just restored the DB on Server B( SQL
2K5) with norecovery.
Now i want to use the maintenance plan wizard in 2000 to set up log shipping
but since i cannot register the destination i.e the SQL 2K5 box in 2000
Enterprise Manager, I cannot seem to complete it.
How do you all set it up ? Please help.Hi Hassan
Have you looked at manually setting up log shipping as in the topic "How to
set up a Log Shipping Monitor (Transact-SQL)" in Books online?
John
"Hassan" wrote:
> I have a DB on Server A ( SQL 2K) and just restored the DB on Server B( SQL
> 2K5) with norecovery.
> Now i want to use the maintenance plan wizard in 2000 to set up log shipping
> but since i cannot register the destination i.e the SQL 2K5 box in 2000
> Enterprise Manager, I cannot seem to complete it.
> How do you all set it up ? Please help.
>
>|||I know how to setup log shipping, but what i dont know is how to setup log
shipping from SQL 2000 EE to SQL 2005 EE as the maintenance plans wont let
me.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6D95FCA9-03A5-4082-83BD-1DD974E09D69@.microsoft.com...
> Hi Hassan
> Have you looked at manually setting up log shipping as in the topic "How
> to
> set up a Log Shipping Monitor (Transact-SQL)" in Books online?
> John
> "Hassan" wrote:
>> I have a DB on Server A ( SQL 2K) and just restored the DB on Server B(
>> SQL
>> 2K5) with norecovery.
>> Now i want to use the maintenance plan wizard in 2000 to set up log
>> shipping
>> but since i cannot register the destination i.e the SQL 2K5 box in 2000
>> Enterprise Manager, I cannot seem to complete it.
>> How do you all set it up ? Please help.
>>|||Hassan wrote:
> I know how to setup log shipping, but what i dont know is how to setup log
> shipping from SQL 2000 EE to SQL 2005 EE as the maintenance plans wont let
> me.
>
So don't use the maintenance plans!! Log shipping is nothing more than
restoring transaction log backups from one database to another, with a
control wrapper to keep track of which logs have not been restored.
Very easy to roll your own process, and you can make it work the way YOU
want it to, not the way MS wants it to.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I assume that the SSMS / EM GUIs don't allow this, but it's really easy to
script a couple of SQL Agent jobs to perform the LogShipping - on the prod
side, a Maintenance Plan job to backup the log to a shared directory every n
minutes, then a second on the Standby server which polls that directory &
restores the log backups.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Hassan" <Hassan@.hotmail.com> wrote in message
news:uTHS4lM%23GHA.3480@.TK2MSFTNGP04.phx.gbl...
>I have a DB on Server A ( SQL 2K) and just restored the DB on Server B( SQL
>2K5) with norecovery.
> Now i want to use the maintenance plan wizard in 2000 to set up log
> shipping but since i cannot register the destination i.e the SQL 2K5 box
> in 2000 Enterprise Manager, I cannot seem to complete it.
> How do you all set it up ? Please help.
>|||Tracy,
So right now we have log shipping set up between a source SQL 2K server and
a standby SQL 2K server using the maintenance plan. What I want to
accomplish is to add the SQL 2K5 standby server to it which means I need to
somehow copy the log files and restore the log files on the SQL 2K5 server
through a script.
I dont know how to write that script. Can you help ?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4540E47C.7060908@.realsqlguy.com...
> Hassan wrote:
>> I know how to setup log shipping, but what i dont know is how to setup
>> log shipping from SQL 2000 EE to SQL 2005 EE as the maintenance plans
>> wont let me.
> So don't use the maintenance plans!! Log shipping is nothing more than
> restoring transaction log backups from one database to another, with a
> control wrapper to keep track of which logs have not been restored. Very
> easy to roll your own process, and you can make it work the way YOU want
> it to, not the way MS wants it to.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Scripting is not my forte Greg. Do you know how you can help me out here ?
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:ONGl$WV%23GHA.4464@.TK2MSFTNGP02.phx.gbl...
>I assume that the SSMS / EM GUIs don't allow this, but it's really easy to
>script a couple of SQL Agent jobs to perform the LogShipping - on the prod
>side, a Maintenance Plan job to backup the log to a shared directory every
>n minutes, then a second on the Standby server which polls that directory &
>restores the log backups.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:uTHS4lM%23GHA.3480@.TK2MSFTNGP04.phx.gbl...
>>I have a DB on Server A ( SQL 2K) and just restored the DB on Server B(
>>SQL 2K5) with norecovery.
>> Now i want to use the maintenance plan wizard in 2000 to set up log
>> shipping but since i cannot register the destination i.e the SQL 2K5 box
>> in 2000 Enterprise Manager, I cannot seem to complete it.
>> How do you all set it up ? Please help.
>|||Hi Hassan,
Those are the Simple Backup and Restore commands you have to use in the
script and schedule in Jobs.
You can take BOL Reference and start writing it....
Thanks
Ajay Rengunthwar
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OkQiuLW%23GHA.1752@.TK2MSFTNGP02.phx.gbl...
> Tracy,
> So right now we have log shipping set up between a source SQL 2K server
> and a standby SQL 2K server using the maintenance plan. What I want to
> accomplish is to add the SQL 2K5 standby server to it which means I need
> to somehow copy the log files and restore the log files on the SQL 2K5
> server through a script.
> I dont know how to write that script. Can you help ?
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:4540E47C.7060908@.realsqlguy.com...
>> Hassan wrote:
>> I know how to setup log shipping, but what i dont know is how to setup
>> log shipping from SQL 2000 EE to SQL 2005 EE as the maintenance plans
>> wont let me.
>>
>> So don't use the maintenance plans!! Log shipping is nothing more than
>> restoring transaction log backups from one database to another, with a
>> control wrapper to keep track of which logs have not been restored. Very
>> easy to roll your own process, and you can make it work the way YOU want
>> it to, not the way MS wants it to.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>
2K5) with norecovery.
Now i want to use the maintenance plan wizard in 2000 to set up log shipping
but since i cannot register the destination i.e the SQL 2K5 box in 2000
Enterprise Manager, I cannot seem to complete it.
How do you all set it up ? Please help.Hi Hassan
Have you looked at manually setting up log shipping as in the topic "How to
set up a Log Shipping Monitor (Transact-SQL)" in Books online?
John
"Hassan" wrote:
> I have a DB on Server A ( SQL 2K) and just restored the DB on Server B( SQL
> 2K5) with norecovery.
> Now i want to use the maintenance plan wizard in 2000 to set up log shipping
> but since i cannot register the destination i.e the SQL 2K5 box in 2000
> Enterprise Manager, I cannot seem to complete it.
> How do you all set it up ? Please help.
>
>|||I know how to setup log shipping, but what i dont know is how to setup log
shipping from SQL 2000 EE to SQL 2005 EE as the maintenance plans wont let
me.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6D95FCA9-03A5-4082-83BD-1DD974E09D69@.microsoft.com...
> Hi Hassan
> Have you looked at manually setting up log shipping as in the topic "How
> to
> set up a Log Shipping Monitor (Transact-SQL)" in Books online?
> John
> "Hassan" wrote:
>> I have a DB on Server A ( SQL 2K) and just restored the DB on Server B(
>> SQL
>> 2K5) with norecovery.
>> Now i want to use the maintenance plan wizard in 2000 to set up log
>> shipping
>> but since i cannot register the destination i.e the SQL 2K5 box in 2000
>> Enterprise Manager, I cannot seem to complete it.
>> How do you all set it up ? Please help.
>>|||Hassan wrote:
> I know how to setup log shipping, but what i dont know is how to setup log
> shipping from SQL 2000 EE to SQL 2005 EE as the maintenance plans wont let
> me.
>
So don't use the maintenance plans!! Log shipping is nothing more than
restoring transaction log backups from one database to another, with a
control wrapper to keep track of which logs have not been restored.
Very easy to roll your own process, and you can make it work the way YOU
want it to, not the way MS wants it to.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I assume that the SSMS / EM GUIs don't allow this, but it's really easy to
script a couple of SQL Agent jobs to perform the LogShipping - on the prod
side, a Maintenance Plan job to backup the log to a shared directory every n
minutes, then a second on the Standby server which polls that directory &
restores the log backups.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Hassan" <Hassan@.hotmail.com> wrote in message
news:uTHS4lM%23GHA.3480@.TK2MSFTNGP04.phx.gbl...
>I have a DB on Server A ( SQL 2K) and just restored the DB on Server B( SQL
>2K5) with norecovery.
> Now i want to use the maintenance plan wizard in 2000 to set up log
> shipping but since i cannot register the destination i.e the SQL 2K5 box
> in 2000 Enterprise Manager, I cannot seem to complete it.
> How do you all set it up ? Please help.
>|||Tracy,
So right now we have log shipping set up between a source SQL 2K server and
a standby SQL 2K server using the maintenance plan. What I want to
accomplish is to add the SQL 2K5 standby server to it which means I need to
somehow copy the log files and restore the log files on the SQL 2K5 server
through a script.
I dont know how to write that script. Can you help ?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4540E47C.7060908@.realsqlguy.com...
> Hassan wrote:
>> I know how to setup log shipping, but what i dont know is how to setup
>> log shipping from SQL 2000 EE to SQL 2005 EE as the maintenance plans
>> wont let me.
> So don't use the maintenance plans!! Log shipping is nothing more than
> restoring transaction log backups from one database to another, with a
> control wrapper to keep track of which logs have not been restored. Very
> easy to roll your own process, and you can make it work the way YOU want
> it to, not the way MS wants it to.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Scripting is not my forte Greg. Do you know how you can help me out here ?
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:ONGl$WV%23GHA.4464@.TK2MSFTNGP02.phx.gbl...
>I assume that the SSMS / EM GUIs don't allow this, but it's really easy to
>script a couple of SQL Agent jobs to perform the LogShipping - on the prod
>side, a Maintenance Plan job to backup the log to a shared directory every
>n minutes, then a second on the Standby server which polls that directory &
>restores the log backups.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:uTHS4lM%23GHA.3480@.TK2MSFTNGP04.phx.gbl...
>>I have a DB on Server A ( SQL 2K) and just restored the DB on Server B(
>>SQL 2K5) with norecovery.
>> Now i want to use the maintenance plan wizard in 2000 to set up log
>> shipping but since i cannot register the destination i.e the SQL 2K5 box
>> in 2000 Enterprise Manager, I cannot seem to complete it.
>> How do you all set it up ? Please help.
>|||Hi Hassan,
Those are the Simple Backup and Restore commands you have to use in the
script and schedule in Jobs.
You can take BOL Reference and start writing it....
Thanks
Ajay Rengunthwar
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OkQiuLW%23GHA.1752@.TK2MSFTNGP02.phx.gbl...
> Tracy,
> So right now we have log shipping set up between a source SQL 2K server
> and a standby SQL 2K server using the maintenance plan. What I want to
> accomplish is to add the SQL 2K5 standby server to it which means I need
> to somehow copy the log files and restore the log files on the SQL 2K5
> server through a script.
> I dont know how to write that script. Can you help ?
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:4540E47C.7060908@.realsqlguy.com...
>> Hassan wrote:
>> I know how to setup log shipping, but what i dont know is how to setup
>> log shipping from SQL 2000 EE to SQL 2005 EE as the maintenance plans
>> wont let me.
>>
>> So don't use the maintenance plans!! Log shipping is nothing more than
>> restoring transaction log backups from one database to another, with a
>> control wrapper to keep track of which logs have not been restored. Very
>> easy to roll your own process, and you can make it work the way YOU want
>> it to, not the way MS wants it to.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>
Log shipping from SQL 2000 to SQL 2005
I have a DB on Server A ( SQL 2K) and just restored the DB on Server B( SQL
2K5) with norecovery.
Now i want to use the maintenance plan wizard in 2000 to set up log shipping
but since i cannot register the destination i.e the SQL 2K5 box in 2000
Enterprise Manager, I cannot seem to complete it.
How do you all set it up ? Please help.Hi Hassan
Have you looked at manually setting up log shipping as in the topic "How to
set up a Log Shipping Monitor (Transact-SQL)" in Books online?
John
"Hassan" wrote:
> I have a DB on Server A ( SQL 2K) and just restored the DB on Server B( SQ
L
> 2K5) with norecovery.
> Now i want to use the maintenance plan wizard in 2000 to set up log shippi
ng
> but since i cannot register the destination i.e the SQL 2K5 box in 2000
> Enterprise Manager, I cannot seem to complete it.
> How do you all set it up ? Please help.
>
>|||I know how to setup log shipping, but what i dont know is how to setup log
shipping from SQL 2000 EE to SQL 2005 EE as the maintenance plans wont let
me.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6D95FCA9-03A5-4082-83BD-1DD974E09D69@.microsoft.com...[vbcol=seagreen]
> Hi Hassan
> Have you looked at manually setting up log shipping as in the topic "How
> to
> set up a Log Shipping Monitor (Transact-SQL)" in Books online?
> John
> "Hassan" wrote:
>|||Hassan wrote:
> I know how to setup log shipping, but what i dont know is how to setup log
> shipping from SQL 2000 EE to SQL 2005 EE as the maintenance plans wont let
> me.
>
So don't use the maintenance plans!! Log shipping is nothing more than
restoring transaction log backups from one database to another, with a
control wrapper to keep track of which logs have not been restored.
Very easy to roll your own process, and you can make it work the way YOU
want it to, not the way MS wants it to.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I assume that the SSMS / EM GUIs don't allow this, but it's really easy to
script a couple of SQL Agent jobs to perform the LogShipping - on the prod
side, a Maintenance Plan job to backup the log to a shared directory every n
minutes, then a second on the Standby server which polls that directory &
restores the log backups.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Hassan" <Hassan@.hotmail.com> wrote in message
news:uTHS4lM%23GHA.3480@.TK2MSFTNGP04.phx.gbl...
>I have a DB on Server A ( SQL 2K) and just restored the DB on Server B( SQL
>2K5) with norecovery.
> Now i want to use the maintenance plan wizard in 2000 to set up log
> shipping but since i cannot register the destination i.e the SQL 2K5 box
> in 2000 Enterprise Manager, I cannot seem to complete it.
> How do you all set it up ? Please help.
>|||Tracy,
So right now we have log shipping set up between a source SQL 2K server and
a standby SQL 2K server using the maintenance plan. What I want to
accomplish is to add the SQL 2K5 standby server to it which means I need to
somehow copy the log files and restore the log files on the SQL 2K5 server
through a script.
I dont know how to write that script. Can you help ?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4540E47C.7060908@.realsqlguy.com...
> Hassan wrote:
> So don't use the maintenance plans!! Log shipping is nothing more than
> restoring transaction log backups from one database to another, with a
> control wrapper to keep track of which logs have not been restored. Very
> easy to roll your own process, and you can make it work the way YOU want
> it to, not the way MS wants it to.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Scripting is not my forte Greg. Do you know how you can help me out here ?
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:ONGl$WV%23GHA.4464@.TK2MSFTNGP02.phx.gbl...
>I assume that the SSMS / EM GUIs don't allow this, but it's really easy to
>script a couple of SQL Agent jobs to perform the LogShipping - on the prod
>side, a Maintenance Plan job to backup the log to a shared directory every
>n minutes, then a second on the Standby server which polls that directory &
>restores the log backups.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:uTHS4lM%23GHA.3480@.TK2MSFTNGP04.phx.gbl...
>|||Hi Hassan,
Those are the Simple Backup and Restore commands you have to use in the
script and schedule in Jobs.
You can take BOL Reference and start writing it....
Thanks
Ajay Rengunthwar
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OkQiuLW%23GHA.1752@.TK2MSFTNGP02.phx.gbl...
> Tracy,
> So right now we have log shipping set up between a source SQL 2K server
> and a standby SQL 2K server using the maintenance plan. What I want to
> accomplish is to add the SQL 2K5 standby server to it which means I need
> to somehow copy the log files and restore the log files on the SQL 2K5
> server through a script.
> I dont know how to write that script. Can you help ?
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:4540E47C.7060908@.realsqlguy.com...
>
2K5) with norecovery.
Now i want to use the maintenance plan wizard in 2000 to set up log shipping
but since i cannot register the destination i.e the SQL 2K5 box in 2000
Enterprise Manager, I cannot seem to complete it.
How do you all set it up ? Please help.Hi Hassan
Have you looked at manually setting up log shipping as in the topic "How to
set up a Log Shipping Monitor (Transact-SQL)" in Books online?
John
"Hassan" wrote:
> I have a DB on Server A ( SQL 2K) and just restored the DB on Server B( SQ
L
> 2K5) with norecovery.
> Now i want to use the maintenance plan wizard in 2000 to set up log shippi
ng
> but since i cannot register the destination i.e the SQL 2K5 box in 2000
> Enterprise Manager, I cannot seem to complete it.
> How do you all set it up ? Please help.
>
>|||I know how to setup log shipping, but what i dont know is how to setup log
shipping from SQL 2000 EE to SQL 2005 EE as the maintenance plans wont let
me.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6D95FCA9-03A5-4082-83BD-1DD974E09D69@.microsoft.com...[vbcol=seagreen]
> Hi Hassan
> Have you looked at manually setting up log shipping as in the topic "How
> to
> set up a Log Shipping Monitor (Transact-SQL)" in Books online?
> John
> "Hassan" wrote:
>|||Hassan wrote:
> I know how to setup log shipping, but what i dont know is how to setup log
> shipping from SQL 2000 EE to SQL 2005 EE as the maintenance plans wont let
> me.
>
So don't use the maintenance plans!! Log shipping is nothing more than
restoring transaction log backups from one database to another, with a
control wrapper to keep track of which logs have not been restored.
Very easy to roll your own process, and you can make it work the way YOU
want it to, not the way MS wants it to.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I assume that the SSMS / EM GUIs don't allow this, but it's really easy to
script a couple of SQL Agent jobs to perform the LogShipping - on the prod
side, a Maintenance Plan job to backup the log to a shared directory every n
minutes, then a second on the Standby server which polls that directory &
restores the log backups.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Hassan" <Hassan@.hotmail.com> wrote in message
news:uTHS4lM%23GHA.3480@.TK2MSFTNGP04.phx.gbl...
>I have a DB on Server A ( SQL 2K) and just restored the DB on Server B( SQL
>2K5) with norecovery.
> Now i want to use the maintenance plan wizard in 2000 to set up log
> shipping but since i cannot register the destination i.e the SQL 2K5 box
> in 2000 Enterprise Manager, I cannot seem to complete it.
> How do you all set it up ? Please help.
>|||Tracy,
So right now we have log shipping set up between a source SQL 2K server and
a standby SQL 2K server using the maintenance plan. What I want to
accomplish is to add the SQL 2K5 standby server to it which means I need to
somehow copy the log files and restore the log files on the SQL 2K5 server
through a script.
I dont know how to write that script. Can you help ?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4540E47C.7060908@.realsqlguy.com...
> Hassan wrote:
> So don't use the maintenance plans!! Log shipping is nothing more than
> restoring transaction log backups from one database to another, with a
> control wrapper to keep track of which logs have not been restored. Very
> easy to roll your own process, and you can make it work the way YOU want
> it to, not the way MS wants it to.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Scripting is not my forte Greg. Do you know how you can help me out here ?
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:ONGl$WV%23GHA.4464@.TK2MSFTNGP02.phx.gbl...
>I assume that the SSMS / EM GUIs don't allow this, but it's really easy to
>script a couple of SQL Agent jobs to perform the LogShipping - on the prod
>side, a Maintenance Plan job to backup the log to a shared directory every
>n minutes, then a second on the Standby server which polls that directory &
>restores the log backups.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:uTHS4lM%23GHA.3480@.TK2MSFTNGP04.phx.gbl...
>|||Hi Hassan,
Those are the Simple Backup and Restore commands you have to use in the
script and schedule in Jobs.
You can take BOL Reference and start writing it....
Thanks
Ajay Rengunthwar
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OkQiuLW%23GHA.1752@.TK2MSFTNGP02.phx.gbl...
> Tracy,
> So right now we have log shipping set up between a source SQL 2K server
> and a standby SQL 2K server using the maintenance plan. What I want to
> accomplish is to add the SQL 2K5 standby server to it which means I need
> to somehow copy the log files and restore the log files on the SQL 2K5
> server through a script.
> I dont know how to write that script. Can you help ?
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:4540E47C.7060908@.realsqlguy.com...
>
Log shipping failover, Backup plan, Disk performance
I have been recently contracted for an NT Administrator position. I am
looking to come up with a backup plan, security plan, and we also have
problems with our SQL server (2000 Enterprise, We have two SQL servers set up
with log shipping). The primary has high CPU. Microsoft tech suggested that
certain stored procedures have recompiled which may contribute to high CPU.
More troubling is they said that the disk performance is not within
acceptable limits. On drives C and D, it took 4.13 seconds to do a single
transfer. Those values should be around 10 milliseconds. We contacted our
hardware vendor (Dell), and they said we must take the server offline to test
the disks.
We have no backup other than the transaction logs from log shipping. I am
not a DBA, but I would like to backup the data before working with our DBA to
fail the database over to the secondary server. What must I backup? What is
the best method? As far as I know there was no backup previous, although we
do have Backup Exec 2000 agent for SQL server v. 8.0, agent accelerator,
Intelligent disaster recovery option v2.0 (NT8.0). Assuming they were backed
up to a folder, what files would I search for to find a previous backup?
We have both SQL servers in a Colo site close by. For disaster recovery,
should we move one of them to another location or backup the logs to another
location? There is also a Merak Mail server and two Web servers that need
backing up. I hear that most companies are going away from tape backup, so
maybe I can get a NAS server and store it in a new location and backup the
SQL data and the rest to this server. What do you think? I know this is too
much to ask, but I don't know anyone to discuss strategy. If you give me
some direction or can answer any specific portions, that would be great.
Thanks for listening to my 10 questions!
Hi there,
brendang wrote:
[...]
> More troubling is they said that the disk performance is not within
> acceptable limits. On drives C and D, it took 4.13 seconds to do a single
> transfer. Those values should be around 10 milliseconds. We contacted our
> hardware vendor (Dell), and they said we must take the server offline to test
[...]
> We have no backup other than the transaction logs from log shipping. I am
> not a DBA, but I would like to backup the data before working with our DBA to
> fail the database over to the secondary server. What must I backup? What is
> the best method? As far as I know there was no backup previous, although we
> do have Backup Exec 2000 agent for SQL server v. 8.0, agent accelerator,
> Intelligent disaster recovery option v2.0 (NT8.0). Assuming they were backed
> up to a folder, what files would I search for to find a previous backup?
You can use SQL Server's Enterprise Manager to do a complete backup (and
later a complete restore) of your databae. After opening Enterprise
Manager, you can right click on the database you want to backup and then
choose to do a backup from the menus there. There are other ways to go
about a backup, but that may be the easiest for you. You can also find
much more helpful information in the Enterprise Manager's help menu.
I should point out that you should do a complete restore of your
database to a separate server before doing any maintenance on your
production data. After you do a complete restore, you'll want to make
sure that everything is there -- recent transactions, stored procedures,
scheduled tasks, and anything else that might be of importance to you.
You should verify that your backups are good by doing a full restore.
You wouldn't want to go to do a restore and find your data isn't there.
> We have both SQL servers in a Colo site close by. For disaster recovery,
> should we move one of them to another location or backup the logs to another
Move one of them to Cleveland so you can come visit our great city! ;)
> location? There is also a Merak Mail server and two Web servers that need
> backing up. I hear that most companies are going away from tape backup, so
> maybe I can get a NAS server and store it in a new location and backup the
> SQL data and the rest to this server. What do you think? I know this is too
> much to ask, but I don't know anyone to discuss strategy. If you give me
> some direction or can answer any specific portions, that would be great.
In all seriousness though, you may consider looking into a solution
consisting of both database replication and regular offline backups.
It's true that many people are moving away from tape backups to
more-reliable Storage Area Networks (SANs) and Network Attached Storage
(NAS) devices, but it's also important to keep a few things in mind when
implementing a backup solution using network-connected storage. For
example, what happens if someone hacks your network and your
network-attached storage gets modified or erased? Will you be able to
take your storage to a remote location for safety (removable disks)?
There's many things I'm sure you'll want to think through. I couldn't
write enough about the things you'll want to investigate. I also can't
be held responsible for anything you implement, and I strongly encourage
you to do your own homework, consult multiple sources for advice, and
make sure you're comfortable with your solution in the end by doing a
complete database disaster recovery drill, from start to finish.
Hope this helps.
looking to come up with a backup plan, security plan, and we also have
problems with our SQL server (2000 Enterprise, We have two SQL servers set up
with log shipping). The primary has high CPU. Microsoft tech suggested that
certain stored procedures have recompiled which may contribute to high CPU.
More troubling is they said that the disk performance is not within
acceptable limits. On drives C and D, it took 4.13 seconds to do a single
transfer. Those values should be around 10 milliseconds. We contacted our
hardware vendor (Dell), and they said we must take the server offline to test
the disks.
We have no backup other than the transaction logs from log shipping. I am
not a DBA, but I would like to backup the data before working with our DBA to
fail the database over to the secondary server. What must I backup? What is
the best method? As far as I know there was no backup previous, although we
do have Backup Exec 2000 agent for SQL server v. 8.0, agent accelerator,
Intelligent disaster recovery option v2.0 (NT8.0). Assuming they were backed
up to a folder, what files would I search for to find a previous backup?
We have both SQL servers in a Colo site close by. For disaster recovery,
should we move one of them to another location or backup the logs to another
location? There is also a Merak Mail server and two Web servers that need
backing up. I hear that most companies are going away from tape backup, so
maybe I can get a NAS server and store it in a new location and backup the
SQL data and the rest to this server. What do you think? I know this is too
much to ask, but I don't know anyone to discuss strategy. If you give me
some direction or can answer any specific portions, that would be great.
Thanks for listening to my 10 questions!
Hi there,
brendang wrote:
[...]
> More troubling is they said that the disk performance is not within
> acceptable limits. On drives C and D, it took 4.13 seconds to do a single
> transfer. Those values should be around 10 milliseconds. We contacted our
> hardware vendor (Dell), and they said we must take the server offline to test
[...]
> We have no backup other than the transaction logs from log shipping. I am
> not a DBA, but I would like to backup the data before working with our DBA to
> fail the database over to the secondary server. What must I backup? What is
> the best method? As far as I know there was no backup previous, although we
> do have Backup Exec 2000 agent for SQL server v. 8.0, agent accelerator,
> Intelligent disaster recovery option v2.0 (NT8.0). Assuming they were backed
> up to a folder, what files would I search for to find a previous backup?
You can use SQL Server's Enterprise Manager to do a complete backup (and
later a complete restore) of your databae. After opening Enterprise
Manager, you can right click on the database you want to backup and then
choose to do a backup from the menus there. There are other ways to go
about a backup, but that may be the easiest for you. You can also find
much more helpful information in the Enterprise Manager's help menu.
I should point out that you should do a complete restore of your
database to a separate server before doing any maintenance on your
production data. After you do a complete restore, you'll want to make
sure that everything is there -- recent transactions, stored procedures,
scheduled tasks, and anything else that might be of importance to you.
You should verify that your backups are good by doing a full restore.
You wouldn't want to go to do a restore and find your data isn't there.
> We have both SQL servers in a Colo site close by. For disaster recovery,
> should we move one of them to another location or backup the logs to another
Move one of them to Cleveland so you can come visit our great city! ;)
> location? There is also a Merak Mail server and two Web servers that need
> backing up. I hear that most companies are going away from tape backup, so
> maybe I can get a NAS server and store it in a new location and backup the
> SQL data and the rest to this server. What do you think? I know this is too
> much to ask, but I don't know anyone to discuss strategy. If you give me
> some direction or can answer any specific portions, that would be great.
In all seriousness though, you may consider looking into a solution
consisting of both database replication and regular offline backups.
It's true that many people are moving away from tape backups to
more-reliable Storage Area Networks (SANs) and Network Attached Storage
(NAS) devices, but it's also important to keep a few things in mind when
implementing a backup solution using network-connected storage. For
example, what happens if someone hacks your network and your
network-attached storage gets modified or erased? Will you be able to
take your storage to a remote location for safety (removable disks)?
There's many things I'm sure you'll want to think through. I couldn't
write enough about the things you'll want to investigate. I also can't
be held responsible for anything you implement, and I strongly encourage
you to do your own homework, consult multiple sources for advice, and
make sure you're comfortable with your solution in the end by doing a
complete database disaster recovery drill, from start to finish.
Hope this helps.
Log shipping failover, Backup plan, Disk performance
I have been recently contracted for an NT Administrator position. I am
looking to come up with a backup plan, security plan, and we also have
problems with our SQL server (2000 Enterprise, We have two SQL servers set up
with log shipping). The primary has high CPU. Microsoft tech suggested that
certain stored procedures have recompiled which may contribute to high CPU.
More troubling is they said that the disk performance is not within
acceptable limits. On drives C and D, it took 4.13 seconds to do a single
transfer. Those values should be around 10 milliseconds. We contacted our
hardware vendor (Dell), and they said we must take the server offline to test
the disks.
We have no backup other than the transaction logs from log shipping. I am
not a DBA, but I would like to backup the data before working with our DBA to
fail the database over to the secondary server. What must I backup? What is
the best method? As far as I know there was no backup previous, although we
do have Backup Exec 2000 agent for SQL server v. 8.0, agent accelerator,
Intelligent disaster recovery option v2.0 (NT8.0). Assuming they were backed
up to a folder, what files would I search for to find a previous backup?
We have both SQL servers in a Colo site close by. For disaster recovery,
should we move one of them to another location or backup the logs to another
location? There is also a Merak Mail server and two Web servers that need
backing up. I hear that most companies are going away from tape backup, so
maybe I can get a NAS server and store it in a new location and backup the
SQL data and the rest to this server. What do you think? I know this is too
much to ask, but I don't know anyone to discuss strategy. If you give me
some direction or can answer any specific portions, that would be great.
Thanks for listening to my 10 questions!Hi there,
brendang wrote:
[...]
> More troubling is they said that the disk performance is not within
> acceptable limits. On drives C and D, it took 4.13 seconds to do a single
> transfer. Those values should be around 10 milliseconds. We contacted our
> hardware vendor (Dell), and they said we must take the server offline to test
[...]
> We have no backup other than the transaction logs from log shipping. I am
> not a DBA, but I would like to backup the data before working with our DBA to
> fail the database over to the secondary server. What must I backup? What is
> the best method? As far as I know there was no backup previous, although we
> do have Backup Exec 2000 agent for SQL server v. 8.0, agent accelerator,
> Intelligent disaster recovery option v2.0 (NT8.0). Assuming they were backed
> up to a folder, what files would I search for to find a previous backup?
You can use SQL Server's Enterprise Manager to do a complete backup (and
later a complete restore) of your databae. After opening Enterprise
Manager, you can right click on the database you want to backup and then
choose to do a backup from the menus there. There are other ways to go
about a backup, but that may be the easiest for you. You can also find
much more helpful information in the Enterprise Manager's help menu.
I should point out that you should do a complete restore of your
database to a separate server before doing any maintenance on your
production data. After you do a complete restore, you'll want to make
sure that everything is there -- recent transactions, stored procedures,
scheduled tasks, and anything else that might be of importance to you.
You should verify that your backups are good by doing a full restore.
You wouldn't want to go to do a restore and find your data isn't there.
> We have both SQL servers in a Colo site close by. For disaster recovery,
> should we move one of them to another location or backup the logs to another
Move one of them to Cleveland so you can come visit our great city! ;)
> location? There is also a Merak Mail server and two Web servers that need
> backing up. I hear that most companies are going away from tape backup, so
> maybe I can get a NAS server and store it in a new location and backup the
> SQL data and the rest to this server. What do you think? I know this is too
> much to ask, but I don't know anyone to discuss strategy. If you give me
> some direction or can answer any specific portions, that would be great.
In all seriousness though, you may consider looking into a solution
consisting of both database replication and regular offline backups.
It's true that many people are moving away from tape backups to
more-reliable Storage Area Networks (SANs) and Network Attached Storage
(NAS) devices, but it's also important to keep a few things in mind when
implementing a backup solution using network-connected storage. For
example, what happens if someone hacks your network and your
network-attached storage gets modified or erased? Will you be able to
take your storage to a remote location for safety (removable disks)?
There's many things I'm sure you'll want to think through. I couldn't
write enough about the things you'll want to investigate. I also can't
be held responsible for anything you implement, and I strongly encourage
you to do your own homework, consult multiple sources for advice, and
make sure you're comfortable with your solution in the end by doing a
complete database disaster recovery drill, from start to finish.
Hope this helps.
looking to come up with a backup plan, security plan, and we also have
problems with our SQL server (2000 Enterprise, We have two SQL servers set up
with log shipping). The primary has high CPU. Microsoft tech suggested that
certain stored procedures have recompiled which may contribute to high CPU.
More troubling is they said that the disk performance is not within
acceptable limits. On drives C and D, it took 4.13 seconds to do a single
transfer. Those values should be around 10 milliseconds. We contacted our
hardware vendor (Dell), and they said we must take the server offline to test
the disks.
We have no backup other than the transaction logs from log shipping. I am
not a DBA, but I would like to backup the data before working with our DBA to
fail the database over to the secondary server. What must I backup? What is
the best method? As far as I know there was no backup previous, although we
do have Backup Exec 2000 agent for SQL server v. 8.0, agent accelerator,
Intelligent disaster recovery option v2.0 (NT8.0). Assuming they were backed
up to a folder, what files would I search for to find a previous backup?
We have both SQL servers in a Colo site close by. For disaster recovery,
should we move one of them to another location or backup the logs to another
location? There is also a Merak Mail server and two Web servers that need
backing up. I hear that most companies are going away from tape backup, so
maybe I can get a NAS server and store it in a new location and backup the
SQL data and the rest to this server. What do you think? I know this is too
much to ask, but I don't know anyone to discuss strategy. If you give me
some direction or can answer any specific portions, that would be great.
Thanks for listening to my 10 questions!Hi there,
brendang wrote:
[...]
> More troubling is they said that the disk performance is not within
> acceptable limits. On drives C and D, it took 4.13 seconds to do a single
> transfer. Those values should be around 10 milliseconds. We contacted our
> hardware vendor (Dell), and they said we must take the server offline to test
[...]
> We have no backup other than the transaction logs from log shipping. I am
> not a DBA, but I would like to backup the data before working with our DBA to
> fail the database over to the secondary server. What must I backup? What is
> the best method? As far as I know there was no backup previous, although we
> do have Backup Exec 2000 agent for SQL server v. 8.0, agent accelerator,
> Intelligent disaster recovery option v2.0 (NT8.0). Assuming they were backed
> up to a folder, what files would I search for to find a previous backup?
You can use SQL Server's Enterprise Manager to do a complete backup (and
later a complete restore) of your databae. After opening Enterprise
Manager, you can right click on the database you want to backup and then
choose to do a backup from the menus there. There are other ways to go
about a backup, but that may be the easiest for you. You can also find
much more helpful information in the Enterprise Manager's help menu.
I should point out that you should do a complete restore of your
database to a separate server before doing any maintenance on your
production data. After you do a complete restore, you'll want to make
sure that everything is there -- recent transactions, stored procedures,
scheduled tasks, and anything else that might be of importance to you.
You should verify that your backups are good by doing a full restore.
You wouldn't want to go to do a restore and find your data isn't there.
> We have both SQL servers in a Colo site close by. For disaster recovery,
> should we move one of them to another location or backup the logs to another
Move one of them to Cleveland so you can come visit our great city! ;)
> location? There is also a Merak Mail server and two Web servers that need
> backing up. I hear that most companies are going away from tape backup, so
> maybe I can get a NAS server and store it in a new location and backup the
> SQL data and the rest to this server. What do you think? I know this is too
> much to ask, but I don't know anyone to discuss strategy. If you give me
> some direction or can answer any specific portions, that would be great.
In all seriousness though, you may consider looking into a solution
consisting of both database replication and regular offline backups.
It's true that many people are moving away from tape backups to
more-reliable Storage Area Networks (SANs) and Network Attached Storage
(NAS) devices, but it's also important to keep a few things in mind when
implementing a backup solution using network-connected storage. For
example, what happens if someone hacks your network and your
network-attached storage gets modified or erased? Will you be able to
take your storage to a remote location for safety (removable disks)?
There's many things I'm sure you'll want to think through. I couldn't
write enough about the things you'll want to investigate. I also can't
be held responsible for anything you implement, and I strongly encourage
you to do your own homework, consult multiple sources for advice, and
make sure you're comfortable with your solution in the end by doing a
complete database disaster recovery drill, from start to finish.
Hope this helps.
Friday, March 9, 2012
Log shipping error - database in use
I am using MS builtin log-shipping functionality in
SQL2000.
My log shipping maintenance plan has a secondary load
state of 'standby' mode. And I have selected
the 'terminate users in database' option - however,
occasionally my log-shipping job is failing with '3101 -
database in use' error.
However, I thought that by selecting 'terminate users in
database' option - this should never happen ?
Has anyone seen anything similar before ?'terminate users in database' will not work always and
does not guarantee anything, a terminated process may drop
into a rollback mode and then it could take a long time to
finally get disappeared.
>--Original Message--
>I am using MS builtin log-shipping functionality in
>SQL2000.
>My log shipping maintenance plan has a secondary load
>state of 'standby' mode. And I have selected
>the 'terminate users in database' option - however,
>occasionally my log-shipping job is failing with '3101 -
>database in use' error.
>However, I thought that by selecting 'terminate users in
>database' option - this should never happen ?
>Has anyone seen anything similar before ?
>.
>
SQL2000.
My log shipping maintenance plan has a secondary load
state of 'standby' mode. And I have selected
the 'terminate users in database' option - however,
occasionally my log-shipping job is failing with '3101 -
database in use' error.
However, I thought that by selecting 'terminate users in
database' option - this should never happen ?
Has anyone seen anything similar before ?'terminate users in database' will not work always and
does not guarantee anything, a terminated process may drop
into a rollback mode and then it could take a long time to
finally get disappeared.
>--Original Message--
>I am using MS builtin log-shipping functionality in
>SQL2000.
>My log shipping maintenance plan has a secondary load
>state of 'standby' mode. And I have selected
>the 'terminate users in database' option - however,
>occasionally my log-shipping job is failing with '3101 -
>database in use' error.
>However, I thought that by selecting 'terminate users in
>database' option - this should never happen ?
>Has anyone seen anything similar before ?
>.
>
Wednesday, March 7, 2012
Log shipping best method? How to initiate an existing plan?
My question is two fold. First, What is the best method for mirroring to a fail-over server? Replication has limits on tables that do not have key fields, and I am finding that Log Shipping is harder to work with.
I have set it up several times, and conducted tests with test databases, but when it fails, I get no indication as to why, and I also can find no way to re-initiate it (without deleting the maintenance plan and starting over). Which leads to my second question, is there a way to manually initiate a log shipping plan already defined?
Thanks in advance!
Rollin
Refer to http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx for a discussion on Database Mirroring and comparison between various HA techniques -- mirroring, replication, log shipping, etc.
Each technology has its own place and usage. Your requirements will determine what is the best method to use.
|||Thank you for your reply to my question. We are using SQL 2000 (enterprise) here, and I don't think the Enterprise edition is available in SQL 2005. If it is, it will not be approved here for some time.
Is there an area that discusses this for SQL 2000?
Thank you,
Rollin
|||The following link provides more information specific to SQL 2000.
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlhalp.mspx
But, you ask for the "best method for mirroring to a fail-over server?" In general, there is no "best method" because there are tradeoffs between the different technologies. You have already indicated that there are differences between transactional replication and log shipping.
As a starting point, Microsoft recommends looking at the following technologies for high availability to see if they meet your business needs.
1. Failover clustering
2. Log shipping
3. Transactional replication
4. Backup / Restore.
There _should_ be information as to why the log shipping fails, but I cannot give more information without looking at the system. I do know there is no way to manually script log shipping in 2000.
I hope this helps.
Thanks,
Mark
|||Thank you for your reply. We were able to discover that the network controller, being set to auto, was still defaulting to the slowest speed, and I beleive the SQL transaction transfer was simpley timing out. We set the controller to 100, and things have been working so far.
Thanks again, Rollin
|||Can I log ship from a 2000 server toi a 2005 server?|||
This specific scenario is not tested, but the restore from a 2000 backup to a 2005 server is.
It is not supported through the normal log shipping stored procedures.
But, _you_ can write jobs that back the 2000 log, copy it to the 2005 server and do the restore.
Does this make sense?
Thanks,
Mark
|||I don't know if this is still active, but...
Does the compatibility level of the desination DB need to remain at 80, or can it be set to 90 with a SWL 2000 source if we wanted to manually configure log shipping?
Thanks,
Jason
Log shipping best method? How to initiate an existing plan?
My question is two fold. First, What is the best method for mirroring to a fail-over server? Replication has limits on tables that do not have key fields, and I am finding that Log Shipping is harder to work with.
I have set it up several times, and conducted tests with test databases, but when it fails, I get no indication as to why, and I also can find no way to re-initiate it (without deleting the maintenance plan and starting over). Which leads to my second question, is there a way to manually initiate a log shipping plan already defined?
Thanks in advance!
Rollin
Refer to http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx for a discussion on Database Mirroring and comparison between various HA techniques -- mirroring, replication, log shipping, etc.
Each technology has its own place and usage. Your requirements will determine what is the best method to use.
|||Thank you for your reply to my question. We are using SQL 2000 (enterprise) here, and I don't think the Enterprise edition is available in SQL 2005. If it is, it will not be approved here for some time.
Is there an area that discusses this for SQL 2000?
Thank you,
Rollin
|||The following link provides more information specific to SQL 2000.
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlhalp.mspx
But, you ask for the "best method for mirroring to a fail-over server?" In general, there is no "best method" because there are tradeoffs between the different technologies. You have already indicated that there are differences between transactional replication and log shipping.
As a starting point, Microsoft recommends looking at the following technologies for high availability to see if they meet your business needs.
1. Failover clustering
2. Log shipping
3. Transactional replication
4. Backup / Restore.
There _should_ be information as to why the log shipping fails, but I cannot give more information without looking at the system. I do know there is no way to manually script log shipping in 2000.
I hope this helps.
Thanks,
Mark
|||Thank you for your reply. We were able to discover that the network controller, being set to auto, was still defaulting to the slowest speed, and I beleive the SQL transaction transfer was simpley timing out. We set the controller to 100, and things have been working so far.
Thanks again, Rollin
|||Can I log ship from a 2000 server toi a 2005 server?|||
This specific scenario is not tested, but the restore from a 2000 backup to a 2005 server is.
It is not supported through the normal log shipping stored procedures.
But, _you_ can write jobs that back the 2000 log, copy it to the 2005 server and do the restore.
Does this make sense?
Thanks,
Mark
|||I don't know if this is still active, but...
Does the compatibility level of the desination DB need to remain at 80, or can it be set to 90 with a SWL 2000 source if we wanted to manually configure log shipping?
Thanks,
Jason
Log shipping best method? How to initiate an existing plan?
My question is two fold. First, What is the best method for mirroring to a fail-over server? Replication has limits on tables that do not have key fields, and I am finding that Log Shipping is harder to work with.
I have set it up several times, and conducted tests with test databases, but when it fails, I get no indication as to why, and I also can find no way to re-initiate it (without deleting the maintenance plan and starting over). Which leads to my second question, is there a way to manually initiate a log shipping plan already defined?
Thanks in advance!
Rollin
Refer to http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx for a discussion on Database Mirroring and comparison between various HA techniques -- mirroring, replication, log shipping, etc.
Each technology has its own place and usage. Your requirements will determine what is the best method to use.
|||Thank you for your reply to my question. We are using SQL 2000 (enterprise) here, and I don't think the Enterprise edition is available in SQL 2005. If it is, it will not be approved here for some time.
Is there an area that discusses this for SQL 2000?
Thank you,
Rollin
|||The following link provides more information specific to SQL 2000.
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlhalp.mspx
But, you ask for the "best method for mirroring to a fail-over server?" In general, there is no "best method" because there are tradeoffs between the different technologies. You have already indicated that there are differences between transactional replication and log shipping.
As a starting point, Microsoft recommends looking at the following technologies for high availability to see if they meet your business needs.
1. Failover clustering
2. Log shipping
3. Transactional replication
4. Backup / Restore.
There _should_ be information as to why the log shipping fails, but I cannot give more information without looking at the system. I do know there is no way to manually script log shipping in 2000.
I hope this helps.
Thanks,
Mark
|||Thank you for your reply. We were able to discover that the network controller, being set to auto, was still defaulting to the slowest speed, and I beleive the SQL transaction transfer was simpley timing out. We set the controller to 100, and things have been working so far.
Thanks again, Rollin
|||Can I log ship from a 2000 server toi a 2005 server?|||
This specific scenario is not tested, but the restore from a 2000 backup to a 2005 server is.
It is not supported through the normal log shipping stored procedures.
But, _you_ can write jobs that back the 2000 log, copy it to the 2005 server and do the restore.
Does this make sense?
Thanks,
Mark
|||I don't know if this is still active, but...
Does the compatibility level of the desination DB need to remain at 80, or can it be set to 90 with a SWL 2000 source if we wanted to manually configure log shipping?
Thanks,
Jason
Log shipping best method? How to initiate an existing plan?
My question is two fold. First, What is the best method for mirroring to a fail-over server? Replication has limits on tables that do not have key fields, and I am finding that Log Shipping is harder to work with.
I have set it up several times, and conducted tests with test databases, but when it fails, I get no indication as to why, and I also can find no way to re-initiate it (without deleting the maintenance plan and starting over). Which leads to my second question, is there a way to manually initiate a log shipping plan already defined?
Thanks in advance!
Rollin
Refer to http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx for a discussion on Database Mirroring and comparison between various HA techniques -- mirroring, replication, log shipping, etc.
Each technology has its own place and usage. Your requirements will determine what is the best method to use.
|||Thank you for your reply to my question. We are using SQL 2000 (enterprise) here, and I don't think the Enterprise edition is available in SQL 2005. If it is, it will not be approved here for some time.
Is there an area that discusses this for SQL 2000?
Thank you,
Rollin
|||The following link provides more information specific to SQL 2000.
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlhalp.mspx
But, you ask for the "best method for mirroring to a fail-over server?" In general, there is no "best method" because there are tradeoffs between the different technologies. You have already indicated that there are differences between transactional replication and log shipping.
As a starting point, Microsoft recommends looking at the following technologies for high availability to see if they meet your business needs.
1. Failover clustering
2. Log shipping
3. Transactional replication
4. Backup / Restore.
There _should_ be information as to why the log shipping fails, but I cannot give more information without looking at the system. I do know there is no way to manually script log shipping in 2000.
I hope this helps.
Thanks,
Mark
|||Thank you for your reply. We were able to discover that the network controller, being set to auto, was still defaulting to the slowest speed, and I beleive the SQL transaction transfer was simpley timing out. We set the controller to 100, and things have been working so far.
Thanks again, Rollin
|||Can I log ship from a 2000 server toi a 2005 server?|||
This specific scenario is not tested, but the restore from a 2000 backup to a 2005 server is.
It is not supported through the normal log shipping stored procedures.
But, _you_ can write jobs that back the 2000 log, copy it to the 2005 server and do the restore.
Does this make sense?
Thanks,
Mark
|||I don't know if this is still active, but...
Does the compatibility level of the desination DB need to remain at 80, or can it be set to 90 with a SWL 2000 source if we wanted to manually configure log shipping?
Thanks,
Jason
Friday, February 24, 2012
Log Shipping and Backup Jobs = Conflict?
Can we run log shipping alongside our backup plan?
The concern is that the full backup runs overnight and
then diff backups run during the day and at the same time
log shipping occurs every 30 mins - don't both jobs
require the resetting of the archive bit specifying when
the last trn backup took place?
Many thanks in advance for the help, Rgds, Simon.As long as the ONLY log backups are done through log shipping you should be
fine...Whole and differential backups do NOT affect the transaction log...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Simon" <anonymous@.discussions.microsoft.com> wrote in message
news:2494501c46018$ae3cd380$a601280a@.phx.gbl...
> Can we run log shipping alongside our backup plan?
> The concern is that the full backup runs overnight and
> then diff backups run during the day and at the same time
> log shipping occurs every 30 mins - don't both jobs
> require the resetting of the archive bit specifying when
> the last trn backup took place?
> Many thanks in advance for the help, Rgds, Simon.
The concern is that the full backup runs overnight and
then diff backups run during the day and at the same time
log shipping occurs every 30 mins - don't both jobs
require the resetting of the archive bit specifying when
the last trn backup took place?
Many thanks in advance for the help, Rgds, Simon.As long as the ONLY log backups are done through log shipping you should be
fine...Whole and differential backups do NOT affect the transaction log...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Simon" <anonymous@.discussions.microsoft.com> wrote in message
news:2494501c46018$ae3cd380$a601280a@.phx.gbl...
> Can we run log shipping alongside our backup plan?
> The concern is that the full backup runs overnight and
> then diff backups run during the day and at the same time
> log shipping occurs every 30 mins - don't both jobs
> require the resetting of the archive bit specifying when
> the last trn backup took place?
> Many thanks in advance for the help, Rgds, Simon.
Log Shipping and a separate backup plan
Log Shipping and a separate backup plan
I have a clustered sql 2k enterprise with mission critical application
database. A group of people has managed a disaster recovery plan using sql
backup full nightly backup and hourly tlog backup. The other group, the
developers, need to have hot standby server using log shipping to move the
data from the production server to the reporting servers.
My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
separately. That means the separate jobs and separate file backups so the log
shipping has its own tlog backup files and is not touching the full backup/
tlog backup files of the main backup plan.
I also have a different software to replicate data from the active site to a
disaster site real time.
We tried to have these two plans running but the log shipping job fails max
after a couple of days, so currently, our developers cannot use log shipping.
They simply use a full back to restore the database on the reporting servers.
How can we resolve this issue?
Thank you,
ktfSounds like time to pass the Senior SQL Administrators graduation exam.
That exam consists of writing your own log shipping scripts. Feel free to
consult google for examples.
Seriously, many admins write their own log shipping scripts because the
built-in version is geared towards a full disaster recovery scenario and not
towards providing a reporting and ETL source. There are several examples on
the web you can use as starting points and adapt them to your own specific
needs.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> Log Shipping and a separate backup plan
> I have a clustered sql 2k enterprise with mission critical application
> database. A group of people has managed a disaster recovery plan using sql
> backup full nightly backup and hourly tlog backup. The other group, the
> developers, need to have hot standby server using log shipping to move the
> data from the production server to the reporting servers.
> My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> separately. That means the separate jobs and separate file backups so the
> log
> shipping has its own tlog backup files and is not touching the full
> backup/
> tlog backup files of the main backup plan.
> I also have a different software to replicate data from the active site to
> a
> disaster site real time.
> We tried to have these two plans running but the log shipping job fails
> max
> after a couple of days, so currently, our developers cannot use log
> shipping.
> They simply use a full back to restore the database on the reporting
> servers.
> How can we resolve this issue?
> Thank you,
> ktf|||No can do. Log backups need to be restored in sequence, so log shipping will influence your other
backup strategy. In 2005, you have a COPY_ONLY option of database and log backups, but there you
also have database mirroring, which would probably be a better option compared to log shipping.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> Log Shipping and a separate backup plan
> I have a clustered sql 2k enterprise with mission critical application
> database. A group of people has managed a disaster recovery plan using sql
> backup full nightly backup and hourly tlog backup. The other group, the
> developers, need to have hot standby server using log shipping to move the
> data from the production server to the reporting servers.
> My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> separately. That means the separate jobs and separate file backups so the log
> shipping has its own tlog backup files and is not touching the full backup/
> tlog backup files of the main backup plan.
> I also have a different software to replicate data from the active site to a
> disaster site real time.
> We tried to have these two plans running but the log shipping job fails max
> after a couple of days, so currently, our developers cannot use log shipping.
> They simply use a full back to restore the database on the reporting servers.
> How can we resolve this issue?
> Thank you,
> ktf|||Tibor,
The way I do backup is a full backup daily with overwrite and two tlog
backup hourly job for even and odd hours (23 tlog backups). I replace these
file every 2-3 weeks because they get corrupted so every time I replace these
files the tlog files will be different that is why I wanted these to be
separated.
Thank you in advance.
ktf
"Tibor Karaszi" wrote:
> No can do. Log backups need to be restored in sequence, so log shipping will influence your other
> backup strategy. In 2005, you have a COPY_ONLY option of database and log backups, but there you
> also have database mirroring, which would probably be a better option compared to log shipping.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> > Log Shipping and a separate backup plan
> >
> > I have a clustered sql 2k enterprise with mission critical application
> > database. A group of people has managed a disaster recovery plan using sql
> > backup full nightly backup and hourly tlog backup. The other group, the
> > developers, need to have hot standby server using log shipping to move the
> > data from the production server to the reporting servers.
> >
> > My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> > separately. That means the separate jobs and separate file backups so the log
> > shipping has its own tlog backup files and is not touching the full backup/
> > tlog backup files of the main backup plan.
> >
> > I also have a different software to replicate data from the active site to a
> > disaster site real time.
> >
> > We tried to have these two plans running but the log shipping job fails max
> > after a couple of days, so currently, our developers cannot use log shipping.
> > They simply use a full back to restore the database on the reporting servers.
> >
> > How can we resolve this issue?
> >
> > Thank you,
> > ktf
>|||OK... I'm not sure if you are asking anything? All I'm saying is that you have to restore the log
backups in sequence.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...
> Tibor,
> The way I do backup is a full backup daily with overwrite and two tlog
> backup hourly job for even and odd hours (23 tlog backups). I replace these
> file every 2-3 weeks because they get corrupted so every time I replace these
> files the tlog files will be different that is why I wanted these to be
> separated.
>
> Thank you in advance.
> ktf
> "Tibor Karaszi" wrote:
>> No can do. Log backups need to be restored in sequence, so log shipping will influence your other
>> backup strategy. In 2005, you have a COPY_ONLY option of database and log backups, but there you
>> also have database mirroring, which would probably be a better option compared to log shipping.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
>> > Log Shipping and a separate backup plan
>> >
>> > I have a clustered sql 2k enterprise with mission critical application
>> > database. A group of people has managed a disaster recovery plan using sql
>> > backup full nightly backup and hourly tlog backup. The other group, the
>> > developers, need to have hot standby server using log shipping to move the
>> > data from the production server to the reporting servers.
>> >
>> > My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
>> > separately. That means the separate jobs and separate file backups so the log
>> > shipping has its own tlog backup files and is not touching the full backup/
>> > tlog backup files of the main backup plan.
>> >
>> > I also have a different software to replicate data from the active site to a
>> > disaster site real time.
>> >
>> > We tried to have these two plans running but the log shipping job fails max
>> > after a couple of days, so currently, our developers cannot use log shipping.
>> > They simply use a full back to restore the database on the reporting servers.
>> >
>> > How can we resolve this issue?
>> >
>> > Thank you,
>> > ktf
>>|||Here is the quick version of how I do it:
Everything goes to disk files first via UNC share. Files never get
overwritten.
Full backups nightly. Full backups weekly to a separate folder. Weekly
backups copied to tape with monthly or quarterly archive.
TLog backups to UNC share with datetime filename suffixes. You can use the
Maintenance Plan wizard to create these or "roll your own" code. I keep
these for four to seven days and then clean them out. Again, the
maintenance plan can do this or you can write your own directory clean out
program.
To log ship for reporting, I have a script that queries the MSDB tables on
the source server for the log backup sequence. I then restore this on the
target system(s). Note that I can run this restore script at any time so I
can schedule around normal database use. Since I am using UNC shares, the
paths are identical for backup and restore.
To log ship for Disaster Recovery, I start with the same basic structure as
for reporting, except I copy the files to a second file share that is
located near the target server. I then add restore work items to a table on
the target server. A scheduled job running on the target server grabs these
and restores on schedule, usually with a 24-hour delay to avoid streaming an
"oops" to the failover system. All the log shipping stuff is custom SQL
code.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...
> Tibor,
> The way I do backup is a full backup daily with overwrite and two tlog
> backup hourly job for even and odd hours (23 tlog backups). I replace
> these
> file every 2-3 weeks because they get corrupted so every time I replace
> these
> files the tlog files will be different that is why I wanted these to be
> separated.
>
> Thank you in advance.
> ktf
> "Tibor Karaszi" wrote:
>> No can do. Log backups need to be restored in sequence, so log shipping
>> will influence your other
>> backup strategy. In 2005, you have a COPY_ONLY option of database and log
>> backups, but there you
>> also have database mirroring, which would probably be a better option
>> compared to log shipping.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
>> > Log Shipping and a separate backup plan
>> >
>> > I have a clustered sql 2k enterprise with mission critical application
>> > database. A group of people has managed a disaster recovery plan using
>> > sql
>> > backup full nightly backup and hourly tlog backup. The other group, the
>> > developers, need to have hot standby server using log shipping to move
>> > the
>> > data from the production server to the reporting servers.
>> >
>> > My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be
>> > ran
>> > separately. That means the separate jobs and separate file backups so
>> > the log
>> > shipping has its own tlog backup files and is not touching the full
>> > backup/
>> > tlog backup files of the main backup plan.
>> >
>> > I also have a different software to replicate data from the active site
>> > to a
>> > disaster site real time.
>> >
>> > We tried to have these two plans running but the log shipping job fails
>> > max
>> > after a couple of days, so currently, our developers cannot use log
>> > shipping.
>> > They simply use a full back to restore the database on the reporting
>> > servers.
>> >
>> > How can we resolve this issue?
>> >
>> > Thank you,
>> > ktf
>>|||Tibor,
I have no problem on backup and restore and I have already restored the
production after a disaster occurred in 3 hours. They are very critical that
is why I do not want to touch my backups.
All I wanted is to have a totally separated log shipping job in place
pointed to an intermediate database (read only) as the target and after that
we are all covered from there.
Geoff,
I have these question and I donâ't want you feel I am acting as a handicap in
here but maybe you are giving me the best answers.
What is UNC in here?
TLog backups to UNC share with datetime filename suffixes.
What toll you use to open Tlog file to see the filename and datetime please?
Do you use a vb app to recognize the filename and datatime?
Do you have sample code for this operation specifically to create this kind
of jobs, control and clean as you said?
About log ship for reporting would you send me the script to
kamitf@.yahoo.com please?
Where is the best place to see log shipping custom code.
Thanks in advance to both of you,
V/r
ktf
"Geoff N. Hiten" wrote:
> Here is the quick version of how I do it:
> Everything goes to disk files first via UNC share. Files never get
> overwritten.
> Full backups nightly. Full backups weekly to a separate folder. Weekly
> backups copied to tape with monthly or quarterly archive.
> TLog backups to UNC share with datetime filename suffixes. You can use the
> Maintenance Plan wizard to create these or "roll your own" code. I keep
> these for four to seven days and then clean them out. Again, the
> maintenance plan can do this or you can write your own directory clean out
> program.
> To log ship for reporting, I have a script that queries the MSDB tables on
> the source server for the log backup sequence. I then restore this on the
> target system(s). Note that I can run this restore script at any time so I
> can schedule around normal database use. Since I am using UNC shares, the
> paths are identical for backup and restore.
> To log ship for Disaster Recovery, I start with the same basic structure as
> for reporting, except I copy the files to a second file share that is
> located near the target server. I then add restore work items to a table on
> the target server. A scheduled job running on the target server grabs these
> and restores on schedule, usually with a 24-hour delay to avoid streaming an
> "oops" to the failover system. All the log shipping stuff is custom SQL
> code.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...
> > Tibor,
> >
> > The way I do backup is a full backup daily with overwrite and two tlog
> > backup hourly job for even and odd hours (23 tlog backups). I replace
> > these
> > file every 2-3 weeks because they get corrupted so every time I replace
> > these
> > files the tlog files will be different that is why I wanted these to be
> > separated.
> >
> >
> > Thank you in advance.
> >
> > ktf
> >
> > "Tibor Karaszi" wrote:
> >
> >> No can do. Log backups need to be restored in sequence, so log shipping
> >> will influence your other
> >> backup strategy. In 2005, you have a COPY_ONLY option of database and log
> >> backups, but there you
> >> also have database mirroring, which would probably be a better option
> >> compared to log shipping.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> >> news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> >> > Log Shipping and a separate backup plan
> >> >
> >> > I have a clustered sql 2k enterprise with mission critical application
> >> > database. A group of people has managed a disaster recovery plan using
> >> > sql
> >> > backup full nightly backup and hourly tlog backup. The other group, the
> >> > developers, need to have hot standby server using log shipping to move
> >> > the
> >> > data from the production server to the reporting servers.
> >> >
> >> > My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be
> >> > ran
> >> > separately. That means the separate jobs and separate file backups so
> >> > the log
> >> > shipping has its own tlog backup files and is not touching the full
> >> > backup/
> >> > tlog backup files of the main backup plan.
> >> >
> >> > I also have a different software to replicate data from the active site
> >> > to a
> >> > disaster site real time.
> >> >
> >> > We tried to have these two plans running but the log shipping job fails
> >> > max
> >> > after a couple of days, so currently, our developers cannot use log
> >> > shipping.
> >> > They simply use a full back to restore the database on the reporting
> >> > servers.
> >> >
> >> > How can we resolve this issue?
> >> >
> >> > Thank you,
> >> > ktf
> >>
> >>
>
>
I have a clustered sql 2k enterprise with mission critical application
database. A group of people has managed a disaster recovery plan using sql
backup full nightly backup and hourly tlog backup. The other group, the
developers, need to have hot standby server using log shipping to move the
data from the production server to the reporting servers.
My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
separately. That means the separate jobs and separate file backups so the log
shipping has its own tlog backup files and is not touching the full backup/
tlog backup files of the main backup plan.
I also have a different software to replicate data from the active site to a
disaster site real time.
We tried to have these two plans running but the log shipping job fails max
after a couple of days, so currently, our developers cannot use log shipping.
They simply use a full back to restore the database on the reporting servers.
How can we resolve this issue?
Thank you,
ktfSounds like time to pass the Senior SQL Administrators graduation exam.
That exam consists of writing your own log shipping scripts. Feel free to
consult google for examples.
Seriously, many admins write their own log shipping scripts because the
built-in version is geared towards a full disaster recovery scenario and not
towards providing a reporting and ETL source. There are several examples on
the web you can use as starting points and adapt them to your own specific
needs.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> Log Shipping and a separate backup plan
> I have a clustered sql 2k enterprise with mission critical application
> database. A group of people has managed a disaster recovery plan using sql
> backup full nightly backup and hourly tlog backup. The other group, the
> developers, need to have hot standby server using log shipping to move the
> data from the production server to the reporting servers.
> My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> separately. That means the separate jobs and separate file backups so the
> log
> shipping has its own tlog backup files and is not touching the full
> backup/
> tlog backup files of the main backup plan.
> I also have a different software to replicate data from the active site to
> a
> disaster site real time.
> We tried to have these two plans running but the log shipping job fails
> max
> after a couple of days, so currently, our developers cannot use log
> shipping.
> They simply use a full back to restore the database on the reporting
> servers.
> How can we resolve this issue?
> Thank you,
> ktf|||No can do. Log backups need to be restored in sequence, so log shipping will influence your other
backup strategy. In 2005, you have a COPY_ONLY option of database and log backups, but there you
also have database mirroring, which would probably be a better option compared to log shipping.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> Log Shipping and a separate backup plan
> I have a clustered sql 2k enterprise with mission critical application
> database. A group of people has managed a disaster recovery plan using sql
> backup full nightly backup and hourly tlog backup. The other group, the
> developers, need to have hot standby server using log shipping to move the
> data from the production server to the reporting servers.
> My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> separately. That means the separate jobs and separate file backups so the log
> shipping has its own tlog backup files and is not touching the full backup/
> tlog backup files of the main backup plan.
> I also have a different software to replicate data from the active site to a
> disaster site real time.
> We tried to have these two plans running but the log shipping job fails max
> after a couple of days, so currently, our developers cannot use log shipping.
> They simply use a full back to restore the database on the reporting servers.
> How can we resolve this issue?
> Thank you,
> ktf|||Tibor,
The way I do backup is a full backup daily with overwrite and two tlog
backup hourly job for even and odd hours (23 tlog backups). I replace these
file every 2-3 weeks because they get corrupted so every time I replace these
files the tlog files will be different that is why I wanted these to be
separated.
Thank you in advance.
ktf
"Tibor Karaszi" wrote:
> No can do. Log backups need to be restored in sequence, so log shipping will influence your other
> backup strategy. In 2005, you have a COPY_ONLY option of database and log backups, but there you
> also have database mirroring, which would probably be a better option compared to log shipping.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> > Log Shipping and a separate backup plan
> >
> > I have a clustered sql 2k enterprise with mission critical application
> > database. A group of people has managed a disaster recovery plan using sql
> > backup full nightly backup and hourly tlog backup. The other group, the
> > developers, need to have hot standby server using log shipping to move the
> > data from the production server to the reporting servers.
> >
> > My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> > separately. That means the separate jobs and separate file backups so the log
> > shipping has its own tlog backup files and is not touching the full backup/
> > tlog backup files of the main backup plan.
> >
> > I also have a different software to replicate data from the active site to a
> > disaster site real time.
> >
> > We tried to have these two plans running but the log shipping job fails max
> > after a couple of days, so currently, our developers cannot use log shipping.
> > They simply use a full back to restore the database on the reporting servers.
> >
> > How can we resolve this issue?
> >
> > Thank you,
> > ktf
>|||OK... I'm not sure if you are asking anything? All I'm saying is that you have to restore the log
backups in sequence.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...
> Tibor,
> The way I do backup is a full backup daily with overwrite and two tlog
> backup hourly job for even and odd hours (23 tlog backups). I replace these
> file every 2-3 weeks because they get corrupted so every time I replace these
> files the tlog files will be different that is why I wanted these to be
> separated.
>
> Thank you in advance.
> ktf
> "Tibor Karaszi" wrote:
>> No can do. Log backups need to be restored in sequence, so log shipping will influence your other
>> backup strategy. In 2005, you have a COPY_ONLY option of database and log backups, but there you
>> also have database mirroring, which would probably be a better option compared to log shipping.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
>> > Log Shipping and a separate backup plan
>> >
>> > I have a clustered sql 2k enterprise with mission critical application
>> > database. A group of people has managed a disaster recovery plan using sql
>> > backup full nightly backup and hourly tlog backup. The other group, the
>> > developers, need to have hot standby server using log shipping to move the
>> > data from the production server to the reporting servers.
>> >
>> > My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
>> > separately. That means the separate jobs and separate file backups so the log
>> > shipping has its own tlog backup files and is not touching the full backup/
>> > tlog backup files of the main backup plan.
>> >
>> > I also have a different software to replicate data from the active site to a
>> > disaster site real time.
>> >
>> > We tried to have these two plans running but the log shipping job fails max
>> > after a couple of days, so currently, our developers cannot use log shipping.
>> > They simply use a full back to restore the database on the reporting servers.
>> >
>> > How can we resolve this issue?
>> >
>> > Thank you,
>> > ktf
>>|||Here is the quick version of how I do it:
Everything goes to disk files first via UNC share. Files never get
overwritten.
Full backups nightly. Full backups weekly to a separate folder. Weekly
backups copied to tape with monthly or quarterly archive.
TLog backups to UNC share with datetime filename suffixes. You can use the
Maintenance Plan wizard to create these or "roll your own" code. I keep
these for four to seven days and then clean them out. Again, the
maintenance plan can do this or you can write your own directory clean out
program.
To log ship for reporting, I have a script that queries the MSDB tables on
the source server for the log backup sequence. I then restore this on the
target system(s). Note that I can run this restore script at any time so I
can schedule around normal database use. Since I am using UNC shares, the
paths are identical for backup and restore.
To log ship for Disaster Recovery, I start with the same basic structure as
for reporting, except I copy the files to a second file share that is
located near the target server. I then add restore work items to a table on
the target server. A scheduled job running on the target server grabs these
and restores on schedule, usually with a 24-hour delay to avoid streaming an
"oops" to the failover system. All the log shipping stuff is custom SQL
code.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...
> Tibor,
> The way I do backup is a full backup daily with overwrite and two tlog
> backup hourly job for even and odd hours (23 tlog backups). I replace
> these
> file every 2-3 weeks because they get corrupted so every time I replace
> these
> files the tlog files will be different that is why I wanted these to be
> separated.
>
> Thank you in advance.
> ktf
> "Tibor Karaszi" wrote:
>> No can do. Log backups need to be restored in sequence, so log shipping
>> will influence your other
>> backup strategy. In 2005, you have a COPY_ONLY option of database and log
>> backups, but there you
>> also have database mirroring, which would probably be a better option
>> compared to log shipping.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
>> > Log Shipping and a separate backup plan
>> >
>> > I have a clustered sql 2k enterprise with mission critical application
>> > database. A group of people has managed a disaster recovery plan using
>> > sql
>> > backup full nightly backup and hourly tlog backup. The other group, the
>> > developers, need to have hot standby server using log shipping to move
>> > the
>> > data from the production server to the reporting servers.
>> >
>> > My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be
>> > ran
>> > separately. That means the separate jobs and separate file backups so
>> > the log
>> > shipping has its own tlog backup files and is not touching the full
>> > backup/
>> > tlog backup files of the main backup plan.
>> >
>> > I also have a different software to replicate data from the active site
>> > to a
>> > disaster site real time.
>> >
>> > We tried to have these two plans running but the log shipping job fails
>> > max
>> > after a couple of days, so currently, our developers cannot use log
>> > shipping.
>> > They simply use a full back to restore the database on the reporting
>> > servers.
>> >
>> > How can we resolve this issue?
>> >
>> > Thank you,
>> > ktf
>>|||Tibor,
I have no problem on backup and restore and I have already restored the
production after a disaster occurred in 3 hours. They are very critical that
is why I do not want to touch my backups.
All I wanted is to have a totally separated log shipping job in place
pointed to an intermediate database (read only) as the target and after that
we are all covered from there.
Geoff,
I have these question and I donâ't want you feel I am acting as a handicap in
here but maybe you are giving me the best answers.
What is UNC in here?
TLog backups to UNC share with datetime filename suffixes.
What toll you use to open Tlog file to see the filename and datetime please?
Do you use a vb app to recognize the filename and datatime?
Do you have sample code for this operation specifically to create this kind
of jobs, control and clean as you said?
About log ship for reporting would you send me the script to
kamitf@.yahoo.com please?
Where is the best place to see log shipping custom code.
Thanks in advance to both of you,
V/r
ktf
"Geoff N. Hiten" wrote:
> Here is the quick version of how I do it:
> Everything goes to disk files first via UNC share. Files never get
> overwritten.
> Full backups nightly. Full backups weekly to a separate folder. Weekly
> backups copied to tape with monthly or quarterly archive.
> TLog backups to UNC share with datetime filename suffixes. You can use the
> Maintenance Plan wizard to create these or "roll your own" code. I keep
> these for four to seven days and then clean them out. Again, the
> maintenance plan can do this or you can write your own directory clean out
> program.
> To log ship for reporting, I have a script that queries the MSDB tables on
> the source server for the log backup sequence. I then restore this on the
> target system(s). Note that I can run this restore script at any time so I
> can schedule around normal database use. Since I am using UNC shares, the
> paths are identical for backup and restore.
> To log ship for Disaster Recovery, I start with the same basic structure as
> for reporting, except I copy the files to a second file share that is
> located near the target server. I then add restore work items to a table on
> the target server. A scheduled job running on the target server grabs these
> and restores on schedule, usually with a 24-hour delay to avoid streaming an
> "oops" to the failover system. All the log shipping stuff is custom SQL
> code.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...
> > Tibor,
> >
> > The way I do backup is a full backup daily with overwrite and two tlog
> > backup hourly job for even and odd hours (23 tlog backups). I replace
> > these
> > file every 2-3 weeks because they get corrupted so every time I replace
> > these
> > files the tlog files will be different that is why I wanted these to be
> > separated.
> >
> >
> > Thank you in advance.
> >
> > ktf
> >
> > "Tibor Karaszi" wrote:
> >
> >> No can do. Log backups need to be restored in sequence, so log shipping
> >> will influence your other
> >> backup strategy. In 2005, you have a COPY_ONLY option of database and log
> >> backups, but there you
> >> also have database mirroring, which would probably be a better option
> >> compared to log shipping.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> >> news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> >> > Log Shipping and a separate backup plan
> >> >
> >> > I have a clustered sql 2k enterprise with mission critical application
> >> > database. A group of people has managed a disaster recovery plan using
> >> > sql
> >> > backup full nightly backup and hourly tlog backup. The other group, the
> >> > developers, need to have hot standby server using log shipping to move
> >> > the
> >> > data from the production server to the reporting servers.
> >> >
> >> > My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be
> >> > ran
> >> > separately. That means the separate jobs and separate file backups so
> >> > the log
> >> > shipping has its own tlog backup files and is not touching the full
> >> > backup/
> >> > tlog backup files of the main backup plan.
> >> >
> >> > I also have a different software to replicate data from the active site
> >> > to a
> >> > disaster site real time.
> >> >
> >> > We tried to have these two plans running but the log shipping job fails
> >> > max
> >> > after a couple of days, so currently, our developers cannot use log
> >> > shipping.
> >> > They simply use a full back to restore the database on the reporting
> >> > servers.
> >> >
> >> > How can we resolve this issue?
> >> >
> >> > Thank you,
> >> > ktf
> >>
> >>
>
>
Log Shipping and a separate backup plan
Log Shipping and a separate backup plan
I have a clustered sql 2k enterprise with mission critical application
database. A group of people has managed a disaster recovery plan using sql
backup full nightly backup and hourly tlog backup. The other group, the
developers, need to have hot standby server using log shipping to move the
data from the production server to the reporting servers.
My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
separately. That means the separate jobs and separate file backups so the log
shipping has its own tlog backup files and is not touching the full backup/
tlog backup files of the main backup plan.
I also have a different software to replicate data from the active site to a
disaster site real time.
We tried to have these two plans running but the log shipping job fails max
after a couple of days, so currently, our developers cannot use log shipping.
They simply use a full back to restore the database on the reporting servers.
How can we resolve this issue?
Thank you,
ktf
Sounds like time to pass the Senior SQL Administrators graduation exam.
That exam consists of writing your own log shipping scripts. Feel free to
consult google for examples.
Seriously, many admins write their own log shipping scripts because the
built-in version is geared towards a full disaster recovery scenario and not
towards providing a reporting and ETL source. There are several examples on
the web you can use as starting points and adapt them to your own specific
needs.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> Log Shipping and a separate backup plan
> I have a clustered sql 2k enterprise with mission critical application
> database. A group of people has managed a disaster recovery plan using sql
> backup full nightly backup and hourly tlog backup. The other group, the
> developers, need to have hot standby server using log shipping to move the
> data from the production server to the reporting servers.
> My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> separately. That means the separate jobs and separate file backups so the
> log
> shipping has its own tlog backup files and is not touching the full
> backup/
> tlog backup files of the main backup plan.
> I also have a different software to replicate data from the active site to
> a
> disaster site real time.
> We tried to have these two plans running but the log shipping job fails
> max
> after a couple of days, so currently, our developers cannot use log
> shipping.
> They simply use a full back to restore the database on the reporting
> servers.
> How can we resolve this issue?
> Thank you,
> ktf
|||No can do. Log backups need to be restored in sequence, so log shipping will influence your other
backup strategy. In 2005, you have a COPY_ONLY option of database and log backups, but there you
also have database mirroring, which would probably be a better option compared to log shipping.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> Log Shipping and a separate backup plan
> I have a clustered sql 2k enterprise with mission critical application
> database. A group of people has managed a disaster recovery plan using sql
> backup full nightly backup and hourly tlog backup. The other group, the
> developers, need to have hot standby server using log shipping to move the
> data from the production server to the reporting servers.
> My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> separately. That means the separate jobs and separate file backups so the log
> shipping has its own tlog backup files and is not touching the full backup/
> tlog backup files of the main backup plan.
> I also have a different software to replicate data from the active site to a
> disaster site real time.
> We tried to have these two plans running but the log shipping job fails max
> after a couple of days, so currently, our developers cannot use log shipping.
> They simply use a full back to restore the database on the reporting servers.
> How can we resolve this issue?
> Thank you,
> ktf
|||Tibor,
The way I do backup is a full backup daily with overwrite and two tlog
backup hourly job for even and odd hours (23 tlog backups). I replace these
file every 2-3 weeks because they get corrupted so every time I replace these
files the tlog files will be different that is why I wanted these to be
separated.
Thank you in advance.
ktf
"Tibor Karaszi" wrote:
> No can do. Log backups need to be restored in sequence, so log shipping will influence your other
> backup strategy. In 2005, you have a COPY_ONLY option of database and log backups, but there you
> also have database mirroring, which would probably be a better option compared to log shipping.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
>
|||OK... I'm not sure if you are asking anything? All I'm saying is that you have to restore the log
backups in sequence.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...[vbcol=seagreen]
> Tibor,
> The way I do backup is a full backup daily with overwrite and two tlog
> backup hourly job for even and odd hours (23 tlog backups). I replace these
> file every 2-3 weeks because they get corrupted so every time I replace these
> files the tlog files will be different that is why I wanted these to be
> separated.
>
> Thank you in advance.
> ktf
> "Tibor Karaszi" wrote:
|||Here is the quick version of how I do it:
Everything goes to disk files first via UNC share. Files never get
overwritten.
Full backups nightly. Full backups weekly to a separate folder. Weekly
backups copied to tape with monthly or quarterly archive.
TLog backups to UNC share with datetime filename suffixes. You can use the
Maintenance Plan wizard to create these or "roll your own" code. I keep
these for four to seven days and then clean them out. Again, the
maintenance plan can do this or you can write your own directory clean out
program.
To log ship for reporting, I have a script that queries the MSDB tables on
the source server for the log backup sequence. I then restore this on the
target system(s). Note that I can run this restore script at any time so I
can schedule around normal database use. Since I am using UNC shares, the
paths are identical for backup and restore.
To log ship for Disaster Recovery, I start with the same basic structure as
for reporting, except I copy the files to a second file share that is
located near the target server. I then add restore work items to a table on
the target server. A scheduled job running on the target server grabs these
and restores on schedule, usually with a 24-hour delay to avoid streaming an
"oops" to the failover system. All the log shipping stuff is custom SQL
code.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...[vbcol=seagreen]
> Tibor,
> The way I do backup is a full backup daily with overwrite and two tlog
> backup hourly job for even and odd hours (23 tlog backups). I replace
> these
> file every 2-3 weeks because they get corrupted so every time I replace
> these
> files the tlog files will be different that is why I wanted these to be
> separated.
>
> Thank you in advance.
> ktf
> "Tibor Karaszi" wrote:
I have a clustered sql 2k enterprise with mission critical application
database. A group of people has managed a disaster recovery plan using sql
backup full nightly backup and hourly tlog backup. The other group, the
developers, need to have hot standby server using log shipping to move the
data from the production server to the reporting servers.
My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
separately. That means the separate jobs and separate file backups so the log
shipping has its own tlog backup files and is not touching the full backup/
tlog backup files of the main backup plan.
I also have a different software to replicate data from the active site to a
disaster site real time.
We tried to have these two plans running but the log shipping job fails max
after a couple of days, so currently, our developers cannot use log shipping.
They simply use a full back to restore the database on the reporting servers.
How can we resolve this issue?
Thank you,
ktf
Sounds like time to pass the Senior SQL Administrators graduation exam.
That exam consists of writing your own log shipping scripts. Feel free to
consult google for examples.
Seriously, many admins write their own log shipping scripts because the
built-in version is geared towards a full disaster recovery scenario and not
towards providing a reporting and ETL source. There are several examples on
the web you can use as starting points and adapt them to your own specific
needs.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> Log Shipping and a separate backup plan
> I have a clustered sql 2k enterprise with mission critical application
> database. A group of people has managed a disaster recovery plan using sql
> backup full nightly backup and hourly tlog backup. The other group, the
> developers, need to have hot standby server using log shipping to move the
> data from the production server to the reporting servers.
> My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> separately. That means the separate jobs and separate file backups so the
> log
> shipping has its own tlog backup files and is not touching the full
> backup/
> tlog backup files of the main backup plan.
> I also have a different software to replicate data from the active site to
> a
> disaster site real time.
> We tried to have these two plans running but the log shipping job fails
> max
> after a couple of days, so currently, our developers cannot use log
> shipping.
> They simply use a full back to restore the database on the reporting
> servers.
> How can we resolve this issue?
> Thank you,
> ktf
|||No can do. Log backups need to be restored in sequence, so log shipping will influence your other
backup strategy. In 2005, you have a COPY_ONLY option of database and log backups, but there you
also have database mirroring, which would probably be a better option compared to log shipping.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> Log Shipping and a separate backup plan
> I have a clustered sql 2k enterprise with mission critical application
> database. A group of people has managed a disaster recovery plan using sql
> backup full nightly backup and hourly tlog backup. The other group, the
> developers, need to have hot standby server using log shipping to move the
> data from the production server to the reporting servers.
> My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> separately. That means the separate jobs and separate file backups so the log
> shipping has its own tlog backup files and is not touching the full backup/
> tlog backup files of the main backup plan.
> I also have a different software to replicate data from the active site to a
> disaster site real time.
> We tried to have these two plans running but the log shipping job fails max
> after a couple of days, so currently, our developers cannot use log shipping.
> They simply use a full back to restore the database on the reporting servers.
> How can we resolve this issue?
> Thank you,
> ktf
|||Tibor,
The way I do backup is a full backup daily with overwrite and two tlog
backup hourly job for even and odd hours (23 tlog backups). I replace these
file every 2-3 weeks because they get corrupted so every time I replace these
files the tlog files will be different that is why I wanted these to be
separated.
Thank you in advance.
ktf
"Tibor Karaszi" wrote:
> No can do. Log backups need to be restored in sequence, so log shipping will influence your other
> backup strategy. In 2005, you have a COPY_ONLY option of database and log backups, but there you
> also have database mirroring, which would probably be a better option compared to log shipping.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
>
|||OK... I'm not sure if you are asking anything? All I'm saying is that you have to restore the log
backups in sequence.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...[vbcol=seagreen]
> Tibor,
> The way I do backup is a full backup daily with overwrite and two tlog
> backup hourly job for even and odd hours (23 tlog backups). I replace these
> file every 2-3 weeks because they get corrupted so every time I replace these
> files the tlog files will be different that is why I wanted these to be
> separated.
>
> Thank you in advance.
> ktf
> "Tibor Karaszi" wrote:
|||Here is the quick version of how I do it:
Everything goes to disk files first via UNC share. Files never get
overwritten.
Full backups nightly. Full backups weekly to a separate folder. Weekly
backups copied to tape with monthly or quarterly archive.
TLog backups to UNC share with datetime filename suffixes. You can use the
Maintenance Plan wizard to create these or "roll your own" code. I keep
these for four to seven days and then clean them out. Again, the
maintenance plan can do this or you can write your own directory clean out
program.
To log ship for reporting, I have a script that queries the MSDB tables on
the source server for the log backup sequence. I then restore this on the
target system(s). Note that I can run this restore script at any time so I
can schedule around normal database use. Since I am using UNC shares, the
paths are identical for backup and restore.
To log ship for Disaster Recovery, I start with the same basic structure as
for reporting, except I copy the files to a second file share that is
located near the target server. I then add restore work items to a table on
the target server. A scheduled job running on the target server grabs these
and restores on schedule, usually with a 24-hour delay to avoid streaming an
"oops" to the failover system. All the log shipping stuff is custom SQL
code.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...[vbcol=seagreen]
> Tibor,
> The way I do backup is a full backup daily with overwrite and two tlog
> backup hourly job for even and odd hours (23 tlog backups). I replace
> these
> file every 2-3 weeks because they get corrupted so every time I replace
> these
> files the tlog files will be different that is why I wanted these to be
> separated.
>
> Thank you in advance.
> ktf
> "Tibor Karaszi" wrote:
Log Shipping and a separate backup plan
Log Shipping and a separate backup plan
I have a clustered sql 2k enterprise with mission critical application
database. A group of people has managed a disaster recovery plan using sql
backup full nightly backup and hourly tlog backup. The other group, the
developers, need to have hot standby server using log shipping to move the
data from the production server to the reporting servers.
My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
separately. That means the separate jobs and separate file backups so the lo
g
shipping has its own tlog backup files and is not touching the full backup/
tlog backup files of the main backup plan.
I also have a different software to replicate data from the active site to a
disaster site real time.
We tried to have these two plans running but the log shipping job fails max
after a couple of days, so currently, our developers cannot use log shipping
.
They simply use a full back to restore the database on the reporting servers
.
How can we resolve this issue?
Thank you,
ktfSounds like time to pass the Senior SQL Administrators graduation exam.
That exam consists of writing your own log shipping scripts. Feel free to
consult google for examples.
Seriously, many admins write their own log shipping scripts because the
built-in version is geared towards a full disaster recovery scenario and not
towards providing a reporting and ETL source. There are several examples on
the web you can use as starting points and adapt them to your own specific
needs.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> Log Shipping and a separate backup plan
> I have a clustered sql 2k enterprise with mission critical application
> database. A group of people has managed a disaster recovery plan using sql
> backup full nightly backup and hourly tlog backup. The other group, the
> developers, need to have hot standby server using log shipping to move the
> data from the production server to the reporting servers.
> My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> separately. That means the separate jobs and separate file backups so the
> log
> shipping has its own tlog backup files and is not touching the full
> backup/
> tlog backup files of the main backup plan.
> I also have a different software to replicate data from the active site to
> a
> disaster site real time.
> We tried to have these two plans running but the log shipping job fails
> max
> after a couple of days, so currently, our developers cannot use log
> shipping.
> They simply use a full back to restore the database on the reporting
> servers.
> How can we resolve this issue?
> Thank you,
> ktf|||No can do. Log backups need to be restored in sequence, so log shipping will
influence your other
backup strategy. In 2005, you have a COPY_ONLY option of database and log ba
ckups, but there you
also have database mirroring, which would probably be a better option compar
ed to log shipping.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> Log Shipping and a separate backup plan
> I have a clustered sql 2k enterprise with mission critical application
> database. A group of people has managed a disaster recovery plan using sql
> backup full nightly backup and hourly tlog backup. The other group, the
> developers, need to have hot standby server using log shipping to move the
> data from the production server to the reporting servers.
> My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> separately. That means the separate jobs and separate file backups so the
log
> shipping has its own tlog backup files and is not touching the full backup
/
> tlog backup files of the main backup plan.
> I also have a different software to replicate data from the active site to
a
> disaster site real time.
> We tried to have these two plans running but the log shipping job fails ma
x
> after a couple of days, so currently, our developers cannot use log shippi
ng.
> They simply use a full back to restore the database on the reporting serve
rs.
> How can we resolve this issue?
> Thank you,
> ktf|||Tibor,
The way I do backup is a full backup daily with overwrite and two tlog
backup hourly job for even and odd hours (23 tlog backups). I replace these
file every 2-3 weeks because they get corrupted so every time I replace thes
e
files the tlog files will be different that is why I wanted these to be
separated.
Thank you in advance.
ktf
"Tibor Karaszi" wrote:
> No can do. Log backups need to be restored in sequence, so log shipping wi
ll influence your other
> backup strategy. In 2005, you have a COPY_ONLY option of database and log
backups, but there you
> also have database mirroring, which would probably be a better option comp
ared to log shipping.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
>|||OK... I'm not sure if you are asking anything? All I'm saying is that you ha
ve to restore the log
backups in sequence.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...[vbcol=seagreen]
> Tibor,
> The way I do backup is a full backup daily with overwrite and two tlog
> backup hourly job for even and odd hours (23 tlog backups). I replace thes
e
> file every 2-3 weeks because they get corrupted so every time I replace th
ese
> files the tlog files will be different that is why I wanted these to be
> separated.
>
> Thank you in advance.
> ktf
> "Tibor Karaszi" wrote:
>|||Here is the quick version of how I do it:
Everything goes to disk files first via UNC share. Files never get
overwritten.
Full backups nightly. Full backups weekly to a separate folder. Weekly
backups copied to tape with monthly or quarterly archive.
TLog backups to UNC share with datetime filename suffixes. You can use the
Maintenance Plan wizard to create these or "roll your own" code. I keep
these for four to seven days and then clean them out. Again, the
maintenance plan can do this or you can write your own directory clean out
program.
To log ship for reporting, I have a script that queries the MSDB tables on
the source server for the log backup sequence. I then restore this on the
target system(s). Note that I can run this restore script at any time so I
can schedule around normal database use. Since I am using UNC shares, the
paths are identical for backup and restore.
To log ship for Disaster Recovery, I start with the same basic structure as
for reporting, except I copy the files to a second file share that is
located near the target server. I then add restore work items to a table on
the target server. A scheduled job running on the target server grabs these
and restores on schedule, usually with a 24-hour delay to avoid streaming an
"oops" to the failover system. All the log shipping stuff is custom SQL
code.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...[vbcol=seagreen]
> Tibor,
> The way I do backup is a full backup daily with overwrite and two tlog
> backup hourly job for even and odd hours (23 tlog backups). I replace
> these
> file every 2-3 weeks because they get corrupted so every time I replace
> these
> files the tlog files will be different that is why I wanted these to be
> separated.
>
> Thank you in advance.
> ktf
> "Tibor Karaszi" wrote:
>
I have a clustered sql 2k enterprise with mission critical application
database. A group of people has managed a disaster recovery plan using sql
backup full nightly backup and hourly tlog backup. The other group, the
developers, need to have hot standby server using log shipping to move the
data from the production server to the reporting servers.
My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
separately. That means the separate jobs and separate file backups so the lo
g
shipping has its own tlog backup files and is not touching the full backup/
tlog backup files of the main backup plan.
I also have a different software to replicate data from the active site to a
disaster site real time.
We tried to have these two plans running but the log shipping job fails max
after a couple of days, so currently, our developers cannot use log shipping
.
They simply use a full back to restore the database on the reporting servers
.
How can we resolve this issue?
Thank you,
ktfSounds like time to pass the Senior SQL Administrators graduation exam.
That exam consists of writing your own log shipping scripts. Feel free to
consult google for examples.
Seriously, many admins write their own log shipping scripts because the
built-in version is geared towards a full disaster recovery scenario and not
towards providing a reporting and ETL source. There are several examples on
the web you can use as starting points and adapt them to your own specific
needs.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> Log Shipping and a separate backup plan
> I have a clustered sql 2k enterprise with mission critical application
> database. A group of people has managed a disaster recovery plan using sql
> backup full nightly backup and hourly tlog backup. The other group, the
> developers, need to have hot standby server using log shipping to move the
> data from the production server to the reporting servers.
> My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> separately. That means the separate jobs and separate file backups so the
> log
> shipping has its own tlog backup files and is not touching the full
> backup/
> tlog backup files of the main backup plan.
> I also have a different software to replicate data from the active site to
> a
> disaster site real time.
> We tried to have these two plans running but the log shipping job fails
> max
> after a couple of days, so currently, our developers cannot use log
> shipping.
> They simply use a full back to restore the database on the reporting
> servers.
> How can we resolve this issue?
> Thank you,
> ktf|||No can do. Log backups need to be restored in sequence, so log shipping will
influence your other
backup strategy. In 2005, you have a COPY_ONLY option of database and log ba
ckups, but there you
also have database mirroring, which would probably be a better option compar
ed to log shipping.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
> Log Shipping and a separate backup plan
> I have a clustered sql 2k enterprise with mission critical application
> database. A group of people has managed a disaster recovery plan using sql
> backup full nightly backup and hourly tlog backup. The other group, the
> developers, need to have hot standby server using log shipping to move the
> data from the production server to the reporting servers.
> My goal is to have two plans (fullbkp/tlogbkp and log shipping) to be ran
> separately. That means the separate jobs and separate file backups so the
log
> shipping has its own tlog backup files and is not touching the full backup
/
> tlog backup files of the main backup plan.
> I also have a different software to replicate data from the active site to
a
> disaster site real time.
> We tried to have these two plans running but the log shipping job fails ma
x
> after a couple of days, so currently, our developers cannot use log shippi
ng.
> They simply use a full back to restore the database on the reporting serve
rs.
> How can we resolve this issue?
> Thank you,
> ktf|||Tibor,
The way I do backup is a full backup daily with overwrite and two tlog
backup hourly job for even and odd hours (23 tlog backups). I replace these
file every 2-3 weeks because they get corrupted so every time I replace thes
e
files the tlog files will be different that is why I wanted these to be
separated.
Thank you in advance.
ktf
"Tibor Karaszi" wrote:
> No can do. Log backups need to be restored in sequence, so log shipping wi
ll influence your other
> backup strategy. In 2005, you have a COPY_ONLY option of database and log
backups, but there you
> also have database mirroring, which would probably be a better option comp
ared to log shipping.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:436B06DE-7281-4522-B4AF-BE87AF7A7D63@.microsoft.com...
>|||OK... I'm not sure if you are asking anything? All I'm saying is that you ha
ve to restore the log
backups in sequence.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...[vbcol=seagreen]
> Tibor,
> The way I do backup is a full backup daily with overwrite and two tlog
> backup hourly job for even and odd hours (23 tlog backups). I replace thes
e
> file every 2-3 weeks because they get corrupted so every time I replace th
ese
> files the tlog files will be different that is why I wanted these to be
> separated.
>
> Thank you in advance.
> ktf
> "Tibor Karaszi" wrote:
>|||Here is the quick version of how I do it:
Everything goes to disk files first via UNC share. Files never get
overwritten.
Full backups nightly. Full backups weekly to a separate folder. Weekly
backups copied to tape with monthly or quarterly archive.
TLog backups to UNC share with datetime filename suffixes. You can use the
Maintenance Plan wizard to create these or "roll your own" code. I keep
these for four to seven days and then clean them out. Again, the
maintenance plan can do this or you can write your own directory clean out
program.
To log ship for reporting, I have a script that queries the MSDB tables on
the source server for the log backup sequence. I then restore this on the
target system(s). Note that I can run this restore script at any time so I
can schedule around normal database use. Since I am using UNC shares, the
paths are identical for backup and restore.
To log ship for Disaster Recovery, I start with the same basic structure as
for reporting, except I copy the files to a second file share that is
located near the target server. I then add restore work items to a table on
the target server. A scheduled job running on the target server grabs these
and restores on schedule, usually with a 24-hour delay to avoid streaming an
"oops" to the failover system. All the log shipping stuff is custom SQL
code.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:344482BA-14CA-471F-9DEC-DCBAAE29B765@.microsoft.com...[vbcol=seagreen]
> Tibor,
> The way I do backup is a full backup daily with overwrite and two tlog
> backup hourly job for even and odd hours (23 tlog backups). I replace
> these
> file every 2-3 weeks because they get corrupted so every time I replace
> these
> files the tlog files will be different that is why I wanted these to be
> separated.
>
> Thank you in advance.
> ktf
> "Tibor Karaszi" wrote:
>
Subscribe to:
Posts (Atom)