log shipping initial backup restore

hi,
sql2000 enterprise edi. Planning for 15 min log shipping .
database size is around 6 gb. took the full backup and try to copy to the
secondary server over the network taking too much of time (say .5mb/sec).
meanwhile i stoped the transaction log backup . because the full backup is
not restored in the secondary server. my question is , ' can i restore log
backups after the full backup restore on secondary to make sync. ? then start
the log shipping process? or peer to peer connection needed in between
primary and secondary?
2) Another question : after the reindex process, the log file size will be
similar to dbsize. so transafering the log file over the network will take
take and log shipping may break because of copy time? how can i handle this?
thanks
Hello
I had a quick question - Did you start the file copy process for your
Complete backup through the Log Shipping Setup wizard?
Assuming that you did not, then regarding your first question :
You can actually perform the transaction log backups on your primary server
while the complete backup is copying over.
1. Once the complete backup is finished copying, start the restore of this
complete backup in NORECOVERY mode and in the meantime start copying over
the transaction log backups.
2. When the restore of the complete backup is complete, start applying the
transaction logs with NORECOVERY option.
3. At some point, stop performing transaction log backups on your primary
server and complete copying/restoring the transaction log backups on the
secondary server.
4. Once the secondary database is ready, start the Log Shipping Setup
Wizard (through the Maintenance Plan) and on the Log Shipping secondary
dialog, select Existing database option and select the NORECOVERY database
that you have created in steps earlier. Selecting this option will prevent
the Wizard from actually initiating a copy/load of the complete backup
during setup.
Regarding your second question:
Since rebuilding an index is a logged operation, there is no way for you to
get around this problem. The only way you can avoid this is by
reinitializing log shipping which is going to be more time consuming. Think
of this process as "keeping your secondary database in complete sync with
your primary". Assume for a second that as soon as you've finished
restoring the large transaction log backup (performed after the index
rebuild operation) to the secondary standby database, your primary database
goes offline. At this point since you have to bring the secondary online,
you would expect it to perform just as fast as your primary (given all
other factors including hardware etc are the same between the 2 machines).
Well if there was a way for you to avoid transferring the info related to
index rebuild to the secondary, the performance on your secondary most
likely would be very slow since there were no index updates performed on it.
Let me know if you have further questions.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||pankaj,
Thanks for you suggestion.
I took the full backup and copied over the network to the standby server,
before starting Logshipping process Setup. But it was very slow. For setting
up logshipping for production environment, i dont want any sync problems
like copying delay and restore log after the reindex process. I want to
know that whether i missed something.
I am thinking of adding one more NIC to my production server to connect to
the standby to improve the copy process.
"Pankaj Agarwal [MSFT]" <pankaja@.online.microsoft.com> wrote in message
news:k0XK9CVkEHA.2656@.cpmsftngxa10.phx.gbl...
> Hello
> I had a quick question - Did you start the file copy process for your
> Complete backup through the Log Shipping Setup wizard?
> Assuming that you did not, then regarding your first question :
> You can actually perform the transaction log backups on your primary
server
> while the complete backup is copying over.
> 1. Once the complete backup is finished copying, start the restore of this
> complete backup in NORECOVERY mode and in the meantime start copying over
> the transaction log backups.
> 2. When the restore of the complete backup is complete, start applying the
> transaction logs with NORECOVERY option.
> 3. At some point, stop performing transaction log backups on your primary
> server and complete copying/restoring the transaction log backups on the
> secondary server.
> 4. Once the secondary database is ready, start the Log Shipping Setup
> Wizard (through the Maintenance Plan) and on the Log Shipping secondary
> dialog, select Existing database option and select the NORECOVERY database
> that you have created in steps earlier. Selecting this option will prevent
> the Wizard from actually initiating a copy/load of the complete backup
> during setup.
> Regarding your second question:
> Since rebuilding an index is a logged operation, there is no way for you
to
> get around this problem. The only way you can avoid this is by
> reinitializing log shipping which is going to be more time consuming.
Think
> of this process as "keeping your secondary database in complete sync with
> your primary". Assume for a second that as soon as you've finished
> restoring the large transaction log backup (performed after the index
> rebuild operation) to the secondary standby database, your primary
database
> goes offline. At this point since you have to bring the secondary online,
> you would expect it to perform just as fast as your primary (given all
> other factors including hardware etc are the same between the 2 machines).
> Well if there was a way for you to avoid transferring the info related to
> index rebuild to the secondary, the performance on your secondary most
> likely would be very slow since there were no index updates performed on
it.
> Let me know if you have further questions.
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
|||Unfortunately there is no way to get around the large transaction log when
you are performing index rebuild. If you feel that adding another NIC will
provide better bandwidth and hence faster copy, then that may be something
to consider.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

log shipping initial backup restore

hi,
sql2000 enterprise edi. Planning for 15 min log shipping .
database size is around 6 gb. took the full backup and try to copy to the
secondary server over the network taking too much of time (say .5mb/sec).
meanwhile i stoped the transaction log backup . because the full backup is
not restored in the secondary server. my question is , ' can i restore log
backups after the full backup restore on secondary to make sync. ? then star
t
the log shipping process? or peer to peer connection needed in between
primary and secondary?
2) Another question : after the reindex process, the log file size will be
similar to dbsize. so transafering the log file over the network will take
take and log shipping may break because of copy time? how can i handle this
?
thanksHello
I had a quick question - Did you start the file copy process for your
Complete backup through the Log Shipping Setup wizard?
Assuming that you did not, then regarding your first question :
You can actually perform the transaction log backups on your primary server
while the complete backup is copying over.
1. Once the complete backup is finished copying, start the restore of this
complete backup in NORECOVERY mode and in the meantime start copying over
the transaction log backups.
2. When the restore of the complete backup is complete, start applying the
transaction logs with NORECOVERY option.
3. At some point, stop performing transaction log backups on your primary
server and complete copying/restoring the transaction log backups on the
secondary server.
4. Once the secondary database is ready, start the Log Shipping Setup
Wizard (through the Maintenance Plan) and on the Log Shipping secondary
dialog, select Existing database option and select the NORECOVERY database
that you have created in steps earlier. Selecting this option will prevent
the Wizard from actually initiating a copy/load of the complete backup
during setup.
Regarding your second question:
Since rebuilding an index is a logged operation, there is no way for you to
get around this problem. The only way you can avoid this is by
reinitializing log shipping which is going to be more time consuming. Think
of this process as "keeping your secondary database in complete sync with
your primary". Assume for a second that as soon as you've finished
restoring the large transaction log backup (performed after the index
rebuild operation) to the secondary standby database, your primary database
goes offline. At this point since you have to bring the secondary online,
you would expect it to perform just as fast as your primary (given all
other factors including hardware etc are the same between the 2 machines).
Well if there was a way for you to avoid transferring the info related to
index rebuild to the secondary, the performance on your secondary most
likely would be very slow since there were no index updates performed on it.
Let me know if you have further questions.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||pankaj,
Thanks for you suggestion.
I took the full backup and copied over the network to the standby server,
before starting Logshipping process Setup. But it was very slow. For setting
up logshipping for production environment, i dont want any sync problems
like copying delay and restore log after the reindex process. I want to
know that whether i missed something.
I am thinking of adding one more NIC to my production server to connect to
the standby to improve the copy process.
"Pankaj Agarwal [MSFT]" <pankaja@.online.microsoft.com> wrote in message
news:k0XK9CVkEHA.2656@.cpmsftngxa10.phx.gbl...
> Hello
> I had a quick question - Did you start the file copy process for your
> Complete backup through the Log Shipping Setup wizard?
> Assuming that you did not, then regarding your first question :
> You can actually perform the transaction log backups on your primary
server
> while the complete backup is copying over.
> 1. Once the complete backup is finished copying, start the restore of this
> complete backup in NORECOVERY mode and in the meantime start copying over
> the transaction log backups.
> 2. When the restore of the complete backup is complete, start applying the
> transaction logs with NORECOVERY option.
> 3. At some point, stop performing transaction log backups on your primary
> server and complete copying/restoring the transaction log backups on the
> secondary server.
> 4. Once the secondary database is ready, start the Log Shipping Setup
> Wizard (through the Maintenance Plan) and on the Log Shipping secondary
> dialog, select Existing database option and select the NORECOVERY database
> that you have created in steps earlier. Selecting this option will prevent
> the Wizard from actually initiating a copy/load of the complete backup
> during setup.
> Regarding your second question:
> Since rebuilding an index is a logged operation, there is no way for you
to
> get around this problem. The only way you can avoid this is by
> reinitializing log shipping which is going to be more time consuming.
Think
> of this process as "keeping your secondary database in complete sync with
> your primary". Assume for a second that as soon as you've finished
> restoring the large transaction log backup (performed after the index
> rebuild operation) to the secondary standby database, your primary
database
> goes offline. At this point since you have to bring the secondary online,
> you would expect it to perform just as fast as your primary (given all
> other factors including hardware etc are the same between the 2 machines).
> Well if there was a way for you to avoid transferring the info related to
> index rebuild to the secondary, the performance on your secondary most
> likely would be very slow since there were no index updates performed on
it.
> Let me know if you have further questions.
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>|||Unfortunately there is no way to get around the large transaction log when
you are performing index rebuild. If you feel that adding another NIC will
provide better bandwidth and hence faster copy, then that may be something
to consider.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Log shipping in workgroup environment

I have two SQL servers that I am trying to setup log shipping on. I am unable to get the restore of the initial database to work on the standby server. It gets a "unable to open file" error when trying to do the restore.

I have created a "syncaccount" user on both machines, and both have same password. I have used configuration manager to set the user/password for the agents on both servers.

I have given syncaccount admin access. I have added syncaccount to the shares on both servers.

Wy am I getting this error.

Does anyone else think it is VERY difficult to make this function work.

Just check the paths where your backup file exist ........and ensure there is modify privileges for sql agent service in the that path.the refer the below link for configuring log shipping in sql 2005,

http://dumthata.com/sqlchn/articles/lship.htm

this is surely a permission issue as it clearly states that "unable to open the file".........always ensure that theres modify privileges for the shared folder in the primary for sql agent account of secondary server......then only copy job will succeed.........also the folder(in secondary server) in which the backups from primary are copied ,the sql service account in secondary shud have modify privileges in this folder in secondary server........ensure it.......

thanxs

|||

I have checked and recheked this over and over again. It doesn't work.

I see in the server error log that the secondary server is having trouble opening the backup file.

I have created a user account on both machines, with identical user name and passwords, and given full access on both sides. I setup the SQL Server agent on both sides to use this logon.

I can connect to the share (where the backup resides) on the primary from the secondary server using the account name I created. This works fine, which would indicate that the service account can indeed access the share in question.

Something else is wrong.

This is a workgroup network environment. There is no domain logon. I read about creating the user accounts while researching this online.

|||

ok fine you try to restore the backup file manually using with standby option.........and then configure log shipping......if that is not possible check the integrity of the backup file by,

restore verifyonly from disk='Path of backup\backup.bak'

|||

Why won't the product work the way it is documented? Why do I have to do something different?

Why is it so difficult to make a product that works?

|||Dave, we cant say 100% that there is a defect with the product......just ensure that everything from your end is perfect and restore it manually as i said earlier........|||

I am trying to follow documentation supplied with the product, and also third party books that describe how to set this up and make it work. I appreciate the fact that you are giving me alternatives, but lets just say that your instructions are incomplete. I am not a sql server expert, nor should I have to be to set this up. I really want this to work the way it is supposed to. Is that asking too much?

I tried your suggestion. The restore worked. Log shipping does not appear to be. The database is in standby.

I have about 20-25 databases that I want to setup log shipping on between the servers. All I ask is that the product work. It doesn't! (Like so many other MicroSoft products).

|||Dave,pls let us know the exact error you are facing while configuring the log shipping...are you not able to apply the tran logs through the restore job ? ? ?.........

Log shipping in workgroup environment

I have two SQL servers that I am trying to setup log shipping on. I am unable to get the restore of the initial database to work on the standby server. It gets a "unable to open file" error when trying to do the restore.

I have created a "syncaccount" user on both machines, and both have same password. I have used configuration manager to set the user/password for the agents on both servers.

I have given syncaccount admin access. I have added syncaccount to the shares on both servers.

Wy am I getting this error.

Does anyone else think it is VERY difficult to make this function work.

Just check the paths where your backup file exist ........and ensure there is modify privileges for sql agent service in the that path.the refer the below link for configuring log shipping in sql 2005,

http://dumthata.com/sqlchn/articles/lship.htm

this is surely a permission issue as it clearly states that "unable to open the file".........always ensure that theres modify privileges for the shared folder in the primary for sql agent account of secondary server......then only copy job will succeed.........also the folder(in secondary server) in which the backups from primary are copied ,the sql service account in secondary shud have modify privileges in this folder in secondary server........ensure it.......

thanxs

|||

I have checked and recheked this over and over again. It doesn't work.

I see in the server error log that the secondary server is having trouble opening the backup file.

I have created a user account on both machines, with identical user name and passwords, and given full access on both sides. I setup the SQL Server agent on both sides to use this logon.

I can connect to the share (where the backup resides) on the primary from the secondary server using the account name I created. This works fine, which would indicate that the service account can indeed access the share in question.

Something else is wrong.

This is a workgroup network environment. There is no domain logon. I read about creating the user accounts while researching this online.

|||

ok fine you try to restore the backup file manually using with standby option.........and then configure log shipping......if that is not possible check the integrity of the backup file by,

restore verifyonly from disk='Path of backup\backup.bak'

|||

Why won't the product work the way it is documented? Why do I have to do something different?

Why is it so difficult to make a product that works?

|||Dave, we cant say 100% that there is a defect with the product......just ensure that everything from your end is perfect and restore it manually as i said earlier........|||

I am trying to follow documentation supplied with the product, and also third party books that describe how to set this up and make it work. I appreciate the fact that you are giving me alternatives, but lets just say that your instructions are incomplete. I am not a sql server expert, nor should I have to be to set this up. I really want this to work the way it is supposed to. Is that asking too much?

I tried your suggestion. The restore worked. Log shipping does not appear to be. The database is in standby.

I have about 20-25 databases that I want to setup log shipping on between the servers. All I ask is that the product work. It doesn't! (Like so many other MicroSoft products).

|||Dave,pls let us know the exact error you are facing while configuring the log shipping...are you not able to apply the tran logs through the restore job ? ? ?.........