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.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

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

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

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

Log Shipping Monitor

Hello,
I have configured a SQL Server for Log Shipping using the Database Maintenan
ce Planner Wizard. Now a Log Shipping Monitor object appears below the Manag
ement folder. How do I reverse this process, and make the Log Shipping Monit
or disappear? In other word
s I want to revert back to the state SQL Server was in before I ran the Data
base Maintenance Planner.
Thanks,
Jerome SmithHello all,
I'll answer my own question for everybody's benefit. Eliminating the Log Shi
pping Monitor is equivalent to eliminating all Log Shipping pairs from the L
og Shipping system database. This is done with the stored procedure:
sp_delete_log_shipping_primary
located in the msdb system database.
Following is the help article.
Cheers,
Jerome
sp_delete_log_shipping_primary
Deletes the primary server from the log_shipping_primaries table.
Syntax
sp_delete_log_shipping_primary
[ @.primary_server_name = ] 'primary_server_name' ,
[ @.primary_database_name = ] 'primary_database_name' ,
{ [ @.delete_secondaries = ] delete_secondaries }
Arguments
[@.primary_server_name =] 'primary_server_name'
Is the name of the primary server. primary_server_name is sysname.
[@.primary_database_name =] 'primary_database_name'
Is the name of the secondary server. primary_database_name is sysname.
[@.delete_secondaries =] delete_secondaries
Specifies that the delete action is also applied to log_shipping_secondaries
table. delete_secondaries is bit, with a default of zero (0).
Return Code Values
0 (success) or 1 (failure)
Remarks
This stored procedure only removes the primary and secondary server from the
monitor. Log shipping still has to be removed from the primary and secondar
y servers.
sp_delete_log_shipping_primary deletes a log shipping primary table. If ther
e are corresponding rows in the log_shipping_databases table, delete_seconda
ries must be set to one (1) or the stored procedure will fail.
Permissions
Only members of the sysadmin fixed server role can execute sp_delete_log_shi
pping_primaries.
Examples
This example deletes the source database "pubs" from the server "source". Th
ere are no corresponding rows in log_shipping_secondaries table.
EXEC sp_delete_log_shipping_primary @.primary_server_name = N'source', @.pri
mary_database_name = N'pubs'
?1988-2000 Microsoft Corporation. All Rights Reserved.|||Actually, when nothing has gone wrong, the easiest way is in Enterprise
Manager:
Management->Database Maintenance Plans->Right-Click->Properties->Log
Shipping->Remove Log Shipping.
Then delete Plan.
Jerome
"Jerome Smith" <jerosmith@.hotmail.com> escribi en el mensaje
news:3B6447C3-7819-4F31-983B-7BDD84D2311F@.microsoft.com...
> Hello all,
> I'll answer my own question for everybody's benefit. Eliminating the Log
Shipping Monitor is equivalent to eliminating all Log Shipping pairs from
the Log Shipping system database. This is done with the stored procedure:
> sp_delete_log_shipping_primary
> located in the msdb system database.
> Following is the help article.
> Cheers,
> Jerome
> sp_delete_log_shipping_primary
> Deletes the primary server from the log_shipping_primaries table.
> Syntax
> sp_delete_log_shipping_primary
> [ @.primary_server_name = ] 'primary_server_name' ,
> [ @.primary_database_name = ] 'primary_database_name' ,
> { [ @.delete_secondaries = ] delete_secondaries }
> Arguments
> [@.primary_server_name =] 'primary_server_name'
> Is the name of the primary server. primary_server_name is sysname.
> [@.primary_database_name =] 'primary_database_name'
> Is the name of the secondary server. primary_database_name is sysname.
> [@.delete_secondaries =] delete_secondaries
> Specifies that the delete action is also applied to
log_shipping_secondaries table. delete_secondaries is bit, with a default of
zero (0).
> Return Code Values
> 0 (success) or 1 (failure)
> Remarks
> This stored procedure only removes the primary and secondary server from
the monitor. Log shipping still has to be removed from the primary and
secondary servers.
> sp_delete_log_shipping_primary deletes a log shipping primary table. If
there are corresponding rows in the log_shipping_databases table,
delete_secondaries must be set to one (1) or the stored procedure will fail.
> Permissions
> Only members of the sysadmin fixed server role can execute
sp_delete_log_shipping_primaries.
> Examples
> This example deletes the source database "pubs" from the server "source".
There are no corresponding rows in log_shipping_secondaries table.
> EXEC sp_delete_log_shipping_primary @.primary_server_name = N'source',
@.primary_database_name = N'pubs'
> 1988-2000 Microsoft Corporation. All Rights Reserved.
>
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.629 / Virus Database: 403 - Release Date: 17-03-2004

Log shipping maintenance on secondary server

Due to a large input on the primary server, one of our tables had grown
enormously. With the right commands (delete, dbcc shrinkdatabase), we managed
to shrink this primary server down to normal proportions. Is there a way to
do this also for the secondary server?
As it is log shipped you can't.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JohnPaw" <JohnPaw@.discussions.microsoft.com> wrote in message
news:C2CCBFDF-6C2C-4D62-B5D0-5224E7E5CB59@.microsoft.com...
> Due to a large input on the primary server, one of our tables had grown
> enormously. With the right commands (delete, dbcc shrinkdatabase), we
> managed
> to shrink this primary server down to normal proportions. Is there a way
> to
> do this also for the secondary server?
>
|||That database cannot be shrunk at all? Just delete log shipping and start all
over again?
|||I don't believe it can as it will break the log shipping chain. Give it a
try and see what happens.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JohnPaw" <JohnPaw@.discussions.microsoft.com> wrote in message
news:2CF05F31-AC52-48D2-93BE-661C783BDBA2@.microsoft.com...
> That database cannot be shrunk at all? Just delete log shipping and start
> all
> over again?
>

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
>

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...
>

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 ?
>.
>

Log Shipping and maintenance plans

BACKGROUND:
I'm a developer who has just recently been given the additional
responsibility of DBA, we don't have the resources to hire a full-time DBA
yet but we can no longer afford to treat our database so casually. I am
trying to setup a configuration which involves
Replication, Mirroring and Log Shipping. We have a primary database SQL00
where all data entry happens, then we use replication to publish the data to
3 other servers (SQL01, SQ02, SQL03) which serve as "reporting" servers for
the public web site. We do nightly full backups. We want to add mirroring,
log shipping and a maintenance plan. We will have a 5th server (SQL04) which
will act as a mirror for the primary and a 6th server (SQL05) which will be
the monitor for mirroring. Then we will use log shipping for reporting as
well as a set of "warm" standby servers.
As I am reading about log shipping I understand that I can only have one job
which will backup the transaction log for the primary database which will of
course be the log shipping job. I don't want to have to keep the entire
history of transaction log backups around so I want to do regular full and
differential backups.
QUESTION:
Because I want to do full and differential backups on a regular scheduled
basis I want to know if running a full or differential backup will
invalidate the transaction log backups created by the log shipping job and
cause the log shipping restore job to fail on the secondary. I am assuming
that I should be able to do this, but I still don't fully understand how all
the different backup types work together so I want to make sure that I'm
doing things right.
Thanks in advance for any help,
MarkThe short answer to your question is no, full and differential backups on
the primary database will not invalidate the transaction logs being shipped.
In a log shipping scenario, as soon as the standby server receives the log
backup, it restores it.
"Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
news:OJaPpmcqGHA.3248@.TK2MSFTNGP04.phx.gbl...
> BACKGROUND:
> I'm a developer who has just recently been given the additional
> responsibility of DBA, we don't have the resources to hire a full-time DBA
> yet but we can no longer afford to treat our database so casually. I am
> trying to setup a configuration which involves
> Replication, Mirroring and Log Shipping. We have a primary database SQL00
> where all data entry happens, then we use replication to publish the data
> to 3 other servers (SQL01, SQ02, SQL03) which serve as "reporting" servers
> for the public web site. We do nightly full backups. We want to add
> mirroring, log shipping and a maintenance plan. We will have a 5th server
> (SQL04) which will act as a mirror for the primary and a 6th server
> (SQL05) which will be the monitor for mirroring. Then we will use log
> shipping for reporting as well as a set of "warm" standby servers.
> As I am reading about log shipping I understand that I can only have one
> job which will backup the transaction log for the primary database which
> will of course be the log shipping job. I don't want to have to keep the
> entire history of transaction log backups around so I want to do regular
> full and differential backups.
> QUESTION:
> Because I want to do full and differential backups on a regular scheduled
> basis I want to know if running a full or differential backup will
> invalidate the transaction log backups created by the log shipping job and
> cause the log shipping restore job to fail on the secondary. I am assuming
> that I should be able to do this, but I still don't fully understand how
> all the different backup types work together so I want to make sure that
> I'm doing things right.
> Thanks in advance for any help,
> Mark
>|||Mark Miller wrote:
> BACKGROUND:
> I'm a developer who has just recently been given the additional
> responsibility of DBA, we don't have the resources to hire a full-time DBA
> yet but we can no longer afford to treat our database so casually. I am
> trying to setup a configuration which involves
> Replication, Mirroring and Log Shipping. We have a primary database SQL00
> where all data entry happens, then we use replication to publish the data
to
> 3 other servers (SQL01, SQ02, SQL03) which serve as "reporting" servers fo
r
> the public web site. We do nightly full backups. We want to add mirroring,
> log shipping and a maintenance plan. We will have a 5th server (SQL04) whi
ch
> will act as a mirror for the primary and a 6th server (SQL05) which will b
e
> the monitor for mirroring. Then we will use log shipping for reporting as
> well as a set of "warm" standby servers.
> As I am reading about log shipping I understand that I can only have one j
ob
> which will backup the transaction log for the primary database which will
of
> course be the log shipping job. I don't want to have to keep the entire
> history of transaction log backups around so I want to do regular full and
> differential backups.
> QUESTION:
> Because I want to do full and differential backups on a regular scheduled
> basis I want to know if running a full or differential backup will
> invalidate the transaction log backups created by the log shipping job and
> cause the log shipping restore job to fail on the secondary. I am assuming
> that I should be able to do this, but I still don't fully understand how a
ll
> the different backup types work together so I want to make sure that I'm
> doing things right.
> Thanks in advance for any help,
> Mark
>
Easy answer is "NO", running a full or diff backup does not disrupt the
transaction logs. That said however, since you already have replication
working, I question why you would want to switch to log-shipping for
your reporting needs. Replication is a much better option for a
reporting solution - log shipping makes the database unavailable for a
brief period while doing a restore.
You also state the desire to create a maintenance plan, I assume to
handle index fragmentation? You might be interested in this script:
http://realsqlguy.com/twiki/bin/vie...IndexesAsNeeded
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks, James.
You're right, I went and tested this by runing a series of backups and then
restoring them in differnt sequence.
FULL BACKUP #1
add table
LOG BACKUP #1
insert rows in new table
FULL BACKUP #2
insert more rows
LOG BACKUP #2
I tried
RESTORE FULL #1
RESTORE LOG #1
RESTORE LOG #2
and I also tried
RESTORE FULL #2
RESTORE LOG #2
It didn't matter which order I restored them in I always had all the data in
the end.
Thank you for your help.
Mark
"James Luetkehoelter" <jamesUNDERSCORELuetkehoelter@.yahoo.com> wrote in
message news:umrQb1cqGHA.2304@.TK2MSFTNGP03.phx.gbl...
> The short answer to your question is no, full and differential backups on
> the primary database will not invalidate the transaction logs being
> shipped. In a log shipping scenario, as soon as the standby server
> receives the log backup, it restores it.
> "Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
> news:OJaPpmcqGHA.3248@.TK2MSFTNGP04.phx.gbl...
>|||One quick caveat - differential backups (which you mentioned in the first
post) do throw a potential wrench into the works. A differential is always
tied to the last full backup. It is possible to setup a plan (or make an
"out-of-backup-scheme" full backup to restore to a test database or
something) that invalidates a number of differential backups. If you need to
have those type of full backups, you can use the WITH COPY_ONLY clause in
the full backup command. This tell 2005 (and it only works on 2005) to
ignore this full backup as part of the recorded backup chain - essentially
preserving the differential backups.
"Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
news:OFN6E7cqGHA.2452@.TK2MSFTNGP03.phx.gbl...
> Thanks, James.
> You're right, I went and tested this by runing a series of backups and
> then restoring them in differnt sequence.
> FULL BACKUP #1
> add table
> LOG BACKUP #1
> insert rows in new table
> FULL BACKUP #2
> insert more rows
> LOG BACKUP #2
> I tried
> RESTORE FULL #1
> RESTORE LOG #1
> RESTORE LOG #2
> and I also tried
> RESTORE FULL #2
> RESTORE LOG #2
> It didn't matter which order I restored them in I always had all the data
> in the end.
> Thank you for your help.
> Mark
> "James Luetkehoelter" <jamesUNDERSCORELuetkehoelter@.yahoo.com> wrote in
> message news:umrQb1cqGHA.2304@.TK2MSFTNGP03.phx.gbl...
>|||Thanks, your answer is what I was looking for. To satisfy curiosity and
answer your question the answer is this:
The database is growing very large and much of the data in the largest
tables may never be updated again, but I have to still provide access to it
on the site. I would like to use table partitioning on the reporting
servers, but because of budget constraints we cannot afford Enterprise
edition. So I am filtering the replication which I hope will give me a
"partition" for current data. For older "archive" data I want to use log
shipping which I can configure to run on a less frequent schedule. I will
have multiple secondaries which will be restored on different schedules so I
will always have 2 which are available for reporting.
As far as maintenance goes I meant that I need to make sure I have regular
backups which I can move offsite incase of disaster.
Thanks,
Mark
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e0P7v5cqGHA.4684@.TK2MSFTNGP05.phx.gbl...
> Mark Miller wrote:
> Easy answer is "NO", running a full or diff backup does not disrupt the
> transaction logs. That said however, since you already have replication
> working, I question why you would want to switch to log-shipping for your
> reporting needs. Replication is a much better option for a reporting
> solution - log shipping makes the database unavailable for a brief period
> while doing a restore.
> You also state the desire to create a maintenance plan, I assume to handle
> index fragmentation? You might be interested in this script:
> http://realsqlguy.com/twiki/bin/vie...IndexesAsNeeded
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Thanks, that will help a lot with "ad hoc" backups!
Mark
"James Luetkehoelter" <jamesUNDERSCORELuetkehoelter@.yahoo.com> wrote in
message news:uPJUW$cqGHA.3248@.TK2MSFTNGP04.phx.gbl...
> One quick caveat - differential backups (which you mentioned in the first
> post) do throw a potential wrench into the works. A differential is always
> tied to the last full backup. It is possible to setup a plan (or make an
> "out-of-backup-scheme" full backup to restore to a test database or
> something) that invalidates a number of differential backups. If you need
> to have those type of full backups, you can use the WITH COPY_ONLY clause
> in the full backup command. This tell 2005 (and it only works on 2005) to
> ignore this full backup as part of the recorded backup chain - essentially
> preserving the differential backups.
> "Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
> news:OFN6E7cqGHA.2452@.TK2MSFTNGP03.phx.gbl...
>|||Mark Miller wrote:
> Thanks, your answer is what I was looking for. To satisfy curiosity and
> answer your question the answer is this:
> The database is growing very large and much of the data in the largest
> tables may never be updated again, but I have to still provide access to i
t
> on the site. I would like to use table partitioning on the reporting
> servers, but because of budget constraints we cannot afford Enterprise
> edition. So I am filtering the replication which I hope will give me a
> "partition" for current data. For older "archive" data I want to use log
> shipping which I can configure to run on a less frequent schedule. I will
> have multiple secondaries which will be restored on different schedules so
I
> will always have 2 which are available for reporting.
> As far as maintenance goes I meant that I need to make sure I have regular
> backups which I can move offsite incase of disaster.
> Thanks,
> Mark
>
Hi Mark
You'll have to keep in mind that Log Shipping isn't available in SQL2000
Standard edition - you need Enterprise Edition for that.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||Steen Persson (DK) wrote:
> Mark Miller wrote:
> Hi Mark
> You'll have to keep in mind that Log Shipping isn't available in SQL2000
> Standard edition - you need Enterprise Edition for that.
>
Or write your own - it's not too difficult, and easier to troubleshoot...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Log Shipping and maintenance plans

BACKGROUND:
I'm a developer who has just recently been given the additional
responsibility of DBA, we don't have the resources to hire a full-time DBA
yet but we can no longer afford to treat our database so casually. I am
trying to setup a configuration which involves
Replication, Mirroring and Log Shipping. We have a primary database SQL00
where all data entry happens, then we use replication to publish the data to
3 other servers (SQL01, SQ02, SQL03) which serve as "reporting" servers for
the public web site. We do nightly full backups. We want to add mirroring,
log shipping and a maintenance plan. We will have a 5th server (SQL04) which
will act as a mirror for the primary and a 6th server (SQL05) which will be
the monitor for mirroring. Then we will use log shipping for reporting as
well as a set of "warm" standby servers.
As I am reading about log shipping I understand that I can only have one job
which will backup the transaction log for the primary database which will of
course be the log shipping job. I don't want to have to keep the entire
history of transaction log backups around so I want to do regular full and
differential backups.
QUESTION:
Because I want to do full and differential backups on a regular scheduled
basis I want to know if running a full or differential backup will
invalidate the transaction log backups created by the log shipping job and
cause the log shipping restore job to fail on the secondary. I am assuming
that I should be able to do this, but I still don't fully understand how all
the different backup types work together so I want to make sure that I'm
doing things right.
Thanks in advance for any help,
MarkThe short answer to your question is no, full and differential backups on
the primary database will not invalidate the transaction logs being shipped.
In a log shipping scenario, as soon as the standby server receives the log
backup, it restores it.
"Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
news:OJaPpmcqGHA.3248@.TK2MSFTNGP04.phx.gbl...
> BACKGROUND:
> I'm a developer who has just recently been given the additional
> responsibility of DBA, we don't have the resources to hire a full-time DBA
> yet but we can no longer afford to treat our database so casually. I am
> trying to setup a configuration which involves
> Replication, Mirroring and Log Shipping. We have a primary database SQL00
> where all data entry happens, then we use replication to publish the data
> to 3 other servers (SQL01, SQ02, SQL03) which serve as "reporting" servers
> for the public web site. We do nightly full backups. We want to add
> mirroring, log shipping and a maintenance plan. We will have a 5th server
> (SQL04) which will act as a mirror for the primary and a 6th server
> (SQL05) which will be the monitor for mirroring. Then we will use log
> shipping for reporting as well as a set of "warm" standby servers.
> As I am reading about log shipping I understand that I can only have one
> job which will backup the transaction log for the primary database which
> will of course be the log shipping job. I don't want to have to keep the
> entire history of transaction log backups around so I want to do regular
> full and differential backups.
> QUESTION:
> Because I want to do full and differential backups on a regular scheduled
> basis I want to know if running a full or differential backup will
> invalidate the transaction log backups created by the log shipping job and
> cause the log shipping restore job to fail on the secondary. I am assuming
> that I should be able to do this, but I still don't fully understand how
> all the different backup types work together so I want to make sure that
> I'm doing things right.
> Thanks in advance for any help,
> Mark
>|||Mark Miller wrote:
> BACKGROUND:
> I'm a developer who has just recently been given the additional
> responsibility of DBA, we don't have the resources to hire a full-time DBA
> yet but we can no longer afford to treat our database so casually. I am
> trying to setup a configuration which involves
> Replication, Mirroring and Log Shipping. We have a primary database SQL00
> where all data entry happens, then we use replication to publish the data to
> 3 other servers (SQL01, SQ02, SQL03) which serve as "reporting" servers for
> the public web site. We do nightly full backups. We want to add mirroring,
> log shipping and a maintenance plan. We will have a 5th server (SQL04) which
> will act as a mirror for the primary and a 6th server (SQL05) which will be
> the monitor for mirroring. Then we will use log shipping for reporting as
> well as a set of "warm" standby servers.
> As I am reading about log shipping I understand that I can only have one job
> which will backup the transaction log for the primary database which will of
> course be the log shipping job. I don't want to have to keep the entire
> history of transaction log backups around so I want to do regular full and
> differential backups.
> QUESTION:
> Because I want to do full and differential backups on a regular scheduled
> basis I want to know if running a full or differential backup will
> invalidate the transaction log backups created by the log shipping job and
> cause the log shipping restore job to fail on the secondary. I am assuming
> that I should be able to do this, but I still don't fully understand how all
> the different backup types work together so I want to make sure that I'm
> doing things right.
> Thanks in advance for any help,
> Mark
>
Easy answer is "NO", running a full or diff backup does not disrupt the
transaction logs. That said however, since you already have replication
working, I question why you would want to switch to log-shipping for
your reporting needs. Replication is a much better option for a
reporting solution - log shipping makes the database unavailable for a
brief period while doing a restore.
You also state the desire to create a maintenance plan, I assume to
handle index fragmentation? You might be interested in this script:
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks, James.
You're right, I went and tested this by runing a series of backups and then
restoring them in differnt sequence.
FULL BACKUP #1
add table
LOG BACKUP #1
insert rows in new table
FULL BACKUP #2
insert more rows
LOG BACKUP #2
I tried
RESTORE FULL #1
RESTORE LOG #1
RESTORE LOG #2
and I also tried
RESTORE FULL #2
RESTORE LOG #2
It didn't matter which order I restored them in I always had all the data in
the end.
Thank you for your help.
Mark
"James Luetkehoelter" <jamesUNDERSCORELuetkehoelter@.yahoo.com> wrote in
message news:umrQb1cqGHA.2304@.TK2MSFTNGP03.phx.gbl...
> The short answer to your question is no, full and differential backups on
> the primary database will not invalidate the transaction logs being
> shipped. In a log shipping scenario, as soon as the standby server
> receives the log backup, it restores it.
> "Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
> news:OJaPpmcqGHA.3248@.TK2MSFTNGP04.phx.gbl...
>> BACKGROUND:
>> I'm a developer who has just recently been given the additional
>> responsibility of DBA, we don't have the resources to hire a full-time
>> DBA yet but we can no longer afford to treat our database so casually. I
>> am trying to setup a configuration which involves
>> Replication, Mirroring and Log Shipping. We have a primary database SQL00
>> where all data entry happens, then we use replication to publish the data
>> to 3 other servers (SQL01, SQ02, SQL03) which serve as "reporting"
>> servers for the public web site. We do nightly full backups. We want to
>> add mirroring, log shipping and a maintenance plan. We will have a 5th
>> server (SQL04) which will act as a mirror for the primary and a 6th
>> server (SQL05) which will be the monitor for mirroring. Then we will use
>> log shipping for reporting as well as a set of "warm" standby servers.
>> As I am reading about log shipping I understand that I can only have one
>> job which will backup the transaction log for the primary database which
>> will of course be the log shipping job. I don't want to have to keep the
>> entire history of transaction log backups around so I want to do regular
>> full and differential backups.
>> QUESTION:
>> Because I want to do full and differential backups on a regular scheduled
>> basis I want to know if running a full or differential backup will
>> invalidate the transaction log backups created by the log shipping job
>> and cause the log shipping restore job to fail on the secondary. I am
>> assuming that I should be able to do this, but I still don't fully
>> understand how all the different backup types work together so I want to
>> make sure that I'm doing things right.
>> Thanks in advance for any help,
>> Mark
>|||One quick caveat - differential backups (which you mentioned in the first
post) do throw a potential wrench into the works. A differential is always
tied to the last full backup. It is possible to setup a plan (or make an
"out-of-backup-scheme" full backup to restore to a test database or
something) that invalidates a number of differential backups. If you need to
have those type of full backups, you can use the WITH COPY_ONLY clause in
the full backup command. This tell 2005 (and it only works on 2005) to
ignore this full backup as part of the recorded backup chain - essentially
preserving the differential backups.
"Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
news:OFN6E7cqGHA.2452@.TK2MSFTNGP03.phx.gbl...
> Thanks, James.
> You're right, I went and tested this by runing a series of backups and
> then restoring them in differnt sequence.
> FULL BACKUP #1
> add table
> LOG BACKUP #1
> insert rows in new table
> FULL BACKUP #2
> insert more rows
> LOG BACKUP #2
> I tried
> RESTORE FULL #1
> RESTORE LOG #1
> RESTORE LOG #2
> and I also tried
> RESTORE FULL #2
> RESTORE LOG #2
> It didn't matter which order I restored them in I always had all the data
> in the end.
> Thank you for your help.
> Mark
> "James Luetkehoelter" <jamesUNDERSCORELuetkehoelter@.yahoo.com> wrote in
> message news:umrQb1cqGHA.2304@.TK2MSFTNGP03.phx.gbl...
>> The short answer to your question is no, full and differential backups on
>> the primary database will not invalidate the transaction logs being
>> shipped. In a log shipping scenario, as soon as the standby server
>> receives the log backup, it restores it.
>> "Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
>> news:OJaPpmcqGHA.3248@.TK2MSFTNGP04.phx.gbl...
>> BACKGROUND:
>> I'm a developer who has just recently been given the additional
>> responsibility of DBA, we don't have the resources to hire a full-time
>> DBA yet but we can no longer afford to treat our database so casually. I
>> am trying to setup a configuration which involves
>> Replication, Mirroring and Log Shipping. We have a primary database
>> SQL00 where all data entry happens, then we use replication to publish
>> the data to 3 other servers (SQL01, SQ02, SQL03) which serve as
>> "reporting" servers for the public web site. We do nightly full backups.
>> We want to add mirroring, log shipping and a maintenance plan. We will
>> have a 5th server (SQL04) which will act as a mirror for the primary and
>> a 6th server (SQL05) which will be the monitor for mirroring. Then we
>> will use log shipping for reporting as well as a set of "warm" standby
>> servers.
>> As I am reading about log shipping I understand that I can only have one
>> job which will backup the transaction log for the primary database which
>> will of course be the log shipping job. I don't want to have to keep the
>> entire history of transaction log backups around so I want to do regular
>> full and differential backups.
>> QUESTION:
>> Because I want to do full and differential backups on a regular
>> scheduled basis I want to know if running a full or differential backup
>> will invalidate the transaction log backups created by the log shipping
>> job and cause the log shipping restore job to fail on the secondary. I
>> am assuming that I should be able to do this, but I still don't fully
>> understand how all the different backup types work together so I want to
>> make sure that I'm doing things right.
>> Thanks in advance for any help,
>> Mark
>>
>|||Thanks, your answer is what I was looking for. To satisfy curiosity and
answer your question the answer is this:
The database is growing very large and much of the data in the largest
tables may never be updated again, but I have to still provide access to it
on the site. I would like to use table partitioning on the reporting
servers, but because of budget constraints we cannot afford Enterprise
edition. So I am filtering the replication which I hope will give me a
"partition" for current data. For older "archive" data I want to use log
shipping which I can configure to run on a less frequent schedule. I will
have multiple secondaries which will be restored on different schedules so I
will always have 2 which are available for reporting.
As far as maintenance goes I meant that I need to make sure I have regular
backups which I can move offsite incase of disaster.
Thanks,
Mark
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:e0P7v5cqGHA.4684@.TK2MSFTNGP05.phx.gbl...
> Mark Miller wrote:
>> BACKGROUND:
>> I'm a developer who has just recently been given the additional
>> responsibility of DBA, we don't have the resources to hire a full-time
>> DBA yet but we can no longer afford to treat our database so casually. I
>> am trying to setup a configuration which involves
>> Replication, Mirroring and Log Shipping. We have a primary database SQL00
>> where all data entry happens, then we use replication to publish the data
>> to 3 other servers (SQL01, SQ02, SQL03) which serve as "reporting"
>> servers for the public web site. We do nightly full backups. We want to
>> add mirroring, log shipping and a maintenance plan. We will have a 5th
>> server (SQL04) which will act as a mirror for the primary and a 6th
>> server (SQL05) which will be the monitor for mirroring. Then we will use
>> log shipping for reporting as well as a set of "warm" standby servers.
>> As I am reading about log shipping I understand that I can only have one
>> job which will backup the transaction log for the primary database which
>> will of course be the log shipping job. I don't want to have to keep the
>> entire history of transaction log backups around so I want to do regular
>> full and differential backups.
>> QUESTION:
>> Because I want to do full and differential backups on a regular scheduled
>> basis I want to know if running a full or differential backup will
>> invalidate the transaction log backups created by the log shipping job
>> and cause the log shipping restore job to fail on the secondary. I am
>> assuming that I should be able to do this, but I still don't fully
>> understand how all the different backup types work together so I want to
>> make sure that I'm doing things right.
>> Thanks in advance for any help,
>> Mark
> Easy answer is "NO", running a full or diff backup does not disrupt the
> transaction logs. That said however, since you already have replication
> working, I question why you would want to switch to log-shipping for your
> reporting needs. Replication is a much better option for a reporting
> solution - log shipping makes the database unavailable for a brief period
> while doing a restore.
> You also state the desire to create a maintenance plan, I assume to handle
> index fragmentation? You might be interested in this script:
> http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Thanks, that will help a lot with "ad hoc" backups!
Mark
"James Luetkehoelter" <jamesUNDERSCORELuetkehoelter@.yahoo.com> wrote in
message news:uPJUW$cqGHA.3248@.TK2MSFTNGP04.phx.gbl...
> One quick caveat - differential backups (which you mentioned in the first
> post) do throw a potential wrench into the works. A differential is always
> tied to the last full backup. It is possible to setup a plan (or make an
> "out-of-backup-scheme" full backup to restore to a test database or
> something) that invalidates a number of differential backups. If you need
> to have those type of full backups, you can use the WITH COPY_ONLY clause
> in the full backup command. This tell 2005 (and it only works on 2005) to
> ignore this full backup as part of the recorded backup chain - essentially
> preserving the differential backups.
> "Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
> news:OFN6E7cqGHA.2452@.TK2MSFTNGP03.phx.gbl...
>> Thanks, James.
>> You're right, I went and tested this by runing a series of backups and
>> then restoring them in differnt sequence.
>> FULL BACKUP #1
>> add table
>> LOG BACKUP #1
>> insert rows in new table
>> FULL BACKUP #2
>> insert more rows
>> LOG BACKUP #2
>> I tried
>> RESTORE FULL #1
>> RESTORE LOG #1
>> RESTORE LOG #2
>> and I also tried
>> RESTORE FULL #2
>> RESTORE LOG #2
>> It didn't matter which order I restored them in I always had all the data
>> in the end.
>> Thank you for your help.
>> Mark
>> "James Luetkehoelter" <jamesUNDERSCORELuetkehoelter@.yahoo.com> wrote in
>> message news:umrQb1cqGHA.2304@.TK2MSFTNGP03.phx.gbl...
>> The short answer to your question is no, full and differential backups
>> on the primary database will not invalidate the transaction logs being
>> shipped. In a log shipping scenario, as soon as the standby server
>> receives the log backup, it restores it.
>> "Mark Miller" <mark_n_0-s*p..am@.maxpreps.com> wrote in message
>> news:OJaPpmcqGHA.3248@.TK2MSFTNGP04.phx.gbl...
>> BACKGROUND:
>> I'm a developer who has just recently been given the additional
>> responsibility of DBA, we don't have the resources to hire a full-time
>> DBA yet but we can no longer afford to treat our database so casually.
>> I am trying to setup a configuration which involves
>> Replication, Mirroring and Log Shipping. We have a primary database
>> SQL00 where all data entry happens, then we use replication to publish
>> the data to 3 other servers (SQL01, SQ02, SQL03) which serve as
>> "reporting" servers for the public web site. We do nightly full
>> backups. We want to add mirroring, log shipping and a maintenance plan.
>> We will have a 5th server (SQL04) which will act as a mirror for the
>> primary and a 6th server (SQL05) which will be the monitor for
>> mirroring. Then we will use log shipping for reporting as well as a set
>> of "warm" standby servers.
>> As I am reading about log shipping I understand that I can only have
>> one job which will backup the transaction log for the primary database
>> which will of course be the log shipping job. I don't want to have to
>> keep the entire history of transaction log backups around so I want to
>> do regular full and differential backups.
>> QUESTION:
>> Because I want to do full and differential backups on a regular
>> scheduled basis I want to know if running a full or differential backup
>> will invalidate the transaction log backups created by the log shipping
>> job and cause the log shipping restore job to fail on the secondary. I
>> am assuming that I should be able to do this, but I still don't fully
>> understand how all the different backup types work together so I want
>> to make sure that I'm doing things right.
>> Thanks in advance for any help,
>> Mark
>>
>>
>|||Mark Miller wrote:
> Thanks, your answer is what I was looking for. To satisfy curiosity and
> answer your question the answer is this:
> The database is growing very large and much of the data in the largest
> tables may never be updated again, but I have to still provide access to it
> on the site. I would like to use table partitioning on the reporting
> servers, but because of budget constraints we cannot afford Enterprise
> edition. So I am filtering the replication which I hope will give me a
> "partition" for current data. For older "archive" data I want to use log
> shipping which I can configure to run on a less frequent schedule. I will
> have multiple secondaries which will be restored on different schedules so I
> will always have 2 which are available for reporting.
> As far as maintenance goes I meant that I need to make sure I have regular
> backups which I can move offsite incase of disaster.
> Thanks,
> Mark
>
Hi Mark
You'll have to keep in mind that Log Shipping isn't available in SQL2000
Standard edition - you need Enterprise Edition for that.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Steen Persson (DK) wrote:
> Mark Miller wrote:
>> Thanks, your answer is what I was looking for. To satisfy curiosity
>> and answer your question the answer is this:
>> The database is growing very large and much of the data in the largest
>> tables may never be updated again, but I have to still provide access
>> to it on the site. I would like to use table partitioning on the
>> reporting servers, but because of budget constraints we cannot afford
>> Enterprise edition. So I am filtering the replication which I hope
>> will give me a "partition" for current data. For older "archive" data
>> I want to use log shipping which I can configure to run on a less
>> frequent schedule. I will have multiple secondaries which will be
>> restored on different schedules so I will always have 2 which are
>> available for reporting.
>> As far as maintenance goes I meant that I need to make sure I have
>> regular backups which I can move offsite incase of disaster.
>> Thanks,
>> Mark
> Hi Mark
> You'll have to keep in mind that Log Shipping isn't available in SQL2000
> Standard edition - you need Enterprise Edition for that.
>
Or write your own - it's not too difficult, and easier to troubleshoot...
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Log Shipping (sort of) with Standard Edition

I have about 30 Databases on a Production SQL Server STANDARD Edition 2000k Instance.

All Backups are performed using SQL Server Maintenance plans (Full Nightly backups and periodic Xaction log backups during business hours)

I would like the ability to copy the FULL Backups to another Server - perhaps on a nightly basis - and then restore them to this other Server as a means of having some sort of Disaster Recovery.

I know with Enterprise Edition, you can set up linked servers and do log shipping or replication, but I don't have that luxury here. I also don't have access to the MS Backoffice Resource Kit - which I understand has some canned scripts to do what I want to do here.

My problem really is that because I don't have control over the backup filenames, how to I automate the restore process on the second Server?

any help would be much appreciated.You could try using the Scripting.FileSystemObject to perform a directory listing of the files in a particular directory. Then use a DO...LOOP to scroll through the list, copying the file and then restoring it to a remote server.

Alternatively, SQL keeps track of the file names used for backups in the msdb database. Look at the following tables:

backupfile
backupmediafamily
backupmediaset
backupset

Finally, you might just consider having the backups run out over the network to a remote server. In the event the primary node fails, you could restore the backups to an alternate server pretty quickly. Just remember that you also need to script off logins, jobs and save DTS packages (as binary files). You then also need to "exercise" your disaster recovery plan on a periodic basis to validate/verify your processes.

Regards,

hmscott|||Thank you for your reply!

I am actually already doing backups locally as well as to another Server on the Network - My problem was how to 'programatically' restore these backups on the remote server.

I found this script - posted by someone else on another forum...

It is SWEET!!!! As long as my backups are in a certain format and all in the same directory, this store procedure will check the folder for the newest .bak file for a specific database and restore it!!!!

So now what I have done is set up a maintenance plan on my prod server to do a full set of backups to this 'network' server - then I just schedule this stored procedure to run on the 'network' server and my databases are automatically refreshed..

Thanks again for your help|||Hi,
Can you let me know where you got that script...
Looking to do something similar.

Thanks,
-jmr|||I would also like to see that script.

log shipping - out of sync

I have tried to set up log shipping for one of our databases - first
time. Using EM I was able to create the job and maintenance plan
sucessfully. I wasn't able to find any errors in any logs. In the
monitor server, when I look at job history, everything is getting
copied and loaded sucessfully, yet the STATUS in the log shipping
monitor states it is out of sync.

How do I get this into sync? I have set the out of sync threshold to
45 minutes. I have made changes in our production database and those
changes are showing up in the standby database, so it all appears to
be working.

Thanks."Rob F." <farrellyr@.yourfuture.ab.ca> wrote in message
news:25f83890.0409270741.715c6c8d@.posting.google.c om...
>I have tried to set up log shipping for one of our databases - first
> time. Using EM I was able to create the job and maintenance plan
> sucessfully. I wasn't able to find any errors in any logs. In the
> monitor server, when I look at job history, everything is getting
> copied and loaded sucessfully, yet the STATUS in the log shipping
> monitor states it is out of sync.
> How do I get this into sync? I have set the out of sync threshold to
> 45 minutes. I have made changes in our production database and those
> changes are showing up in the standby database, so it all appears to
> be working.
>
> Thanks.

http://support.microsoft.com/defaul...kb;en-us;329133

Simon