Log Shipping with additional backups

I am planning to implement log shipping between two servers.
I understand that if I perform a full database backup of the primary
database during the log shipping, this will have no effect on the log
shipping. If I am log shipping every 30 minutes, so I have a log backup at
09:00 and 09:30, perform a full database backup at 09:15, the log backup at
09:30 will contain all changes from 09:00.
Can I use this full database backup and the log shipping logs to perform a
database recovery? Can I restore the 09:15 full backup and then the 09:30
log? Or, do I need to perform additional log backups, e.g. every hour? If
so, how do these log backups affect the log shipping backups?
Many ThanksYes you can use the log shipping log backups to do a recovery. You will
probably have to look at your log shipping scripts to make sure they are not
deleting the log backup files.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"si" <si@.discussions.microsoft.com> wrote in message
news:819CBAE2-0A90-42BB-8B2B-2B1DFF7DBE05@.microsoft.com...
>I am planning to implement log shipping between two servers.
> I understand that if I perform a full database backup of the primary
> database during the log shipping, this will have no effect on the log
> shipping. If I am log shipping every 30 minutes, so I have a log backup
> at
> 09:00 and 09:30, perform a full database backup at 09:15, the log backup
> at
> 09:30 will contain all changes from 09:00.
> Can I use this full database backup and the log shipping logs to perform a
> database recovery? Can I restore the 09:15 full backup and then the 09:30
> log? Or, do I need to perform additional log backups, e.g. every hour?
> If
> so, how do these log backups affect the log shipping backups?
> Many Thanks

Log Shipping with additional backups

I am planning to implement log shipping between two servers.
I understand that if I perform a full database backup of the primary
database during the log shipping, this will have no effect on the log
shipping. If I am log shipping every 30 minutes, so I have a log backup at
09:00 and 09:30, perform a full database backup at 09:15, the log backup at
09:30 will contain all changes from 09:00.
Can I use this full database backup and the log shipping logs to perform a
database recovery? Can I restore the 09:15 full backup and then the 09:30
log? Or, do I need to perform additional log backups, e.g. every hour? If
so, how do these log backups affect the log shipping backups?
Many ThanksYes you can use the log shipping log backups to do a recovery. You will
probably have to look at your log shipping scripts to make sure they are not
deleting the log backup files.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"si" <si@.discussions.microsoft.com> wrote in message
news:819CBAE2-0A90-42BB-8B2B-2B1DFF7DBE05@.microsoft.com...
>I am planning to implement log shipping between two servers.
> I understand that if I perform a full database backup of the primary
> database during the log shipping, this will have no effect on the log
> shipping. If I am log shipping every 30 minutes, so I have a log backup
> at
> 09:00 and 09:30, perform a full database backup at 09:15, the log backup
> at
> 09:30 will contain all changes from 09:00.
> Can I use this full database backup and the log shipping logs to perform a
> database recovery? Can I restore the 09:15 full backup and then the 09:30
> log? Or, do I need to perform additional log backups, e.g. every hour?
> If
> so, how do these log backups affect the log shipping backups?
> Many Thanks

Log Shipping with additional backups

I am planning to implement log shipping between two servers.
I understand that if I perform a full database backup of the primary
database during the log shipping, this will have no effect on the log
shipping. If I am log shipping every 30 minutes, so I have a log backup at
09:00 and 09:30, perform a full database backup at 09:15, the log backup at
09:30 will contain all changes from 09:00.
Can I use this full database backup and the log shipping logs to perform a
database recovery? Can I restore the 09:15 full backup and then the 09:30
log? Or, do I need to perform additional log backups, e.g. every hour? If
so, how do these log backups affect the log shipping backups?
Many Thanks
Yes you can use the log shipping log backups to do a recovery. You will
probably have to look at your log shipping scripts to make sure they are not
deleting the log backup files.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"si" <si@.discussions.microsoft.com> wrote in message
news:819CBAE2-0A90-42BB-8B2B-2B1DFF7DBE05@.microsoft.com...
>I am planning to implement log shipping between two servers.
> I understand that if I perform a full database backup of the primary
> database during the log shipping, this will have no effect on the log
> shipping. If I am log shipping every 30 minutes, so I have a log backup
> at
> 09:00 and 09:30, perform a full database backup at 09:15, the log backup
> at
> 09:30 will contain all changes from 09:00.
> Can I use this full database backup and the log shipping logs to perform a
> database recovery? Can I restore the 09:15 full backup and then the 09:30
> log? Or, do I need to perform additional log backups, e.g. every hour?
> If
> so, how do these log backups affect the log shipping backups?
> Many Thanks

Log shipping question

My company is embarking on a DRP project where we will move our production
servers to a less geographically insecure area. We'll need to perform log
shipping back to our DRP site.
I know that SQL Server logs index rebuilds or index defragmentation and that
we perform rebuilds regularly so I have a few questions if anyone can help:
1.. Can we stop index builds from being logged?
2.. If we cannot stop index logging, are there any different practices we
can follow to do index maintenance that will not require logging (e.g.
defrag)
Does anyone have any experience of this?
If we were to ship index rebuilds or defragmentation, we'll be shipping
gigabytes of unnecessary data, but apparently SQL Server will not let this
be switched off?
Thanks.
Bill
If you don't log operations, the forth coming log backup would not be useful. You cannot eat the
cake and have it. :-)
You could look into bulk logged recovery mode, but a log backup in this mode will include data (not
log records) modified by minimally logged operations. I doubt you will gain much, if anything.
Your best bet is to only rebuild if you need to. A start is to use the code in Books Online, DBCC
SHOWCONTIG which only do rebuild if frag level is > a certain level. You can customize this further
and rebuild different indexes at different times.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bill Wang" <wdw2130833@.hotmail.com> wrote in message
news:9fTle.28354$Ot6.1561166@.news20.bellglobal.com ...
> My company is embarking on a DRP project where we will move our production
> servers to a less geographically insecure area. We'll need to perform log
> shipping back to our DRP site.
> I know that SQL Server logs index rebuilds or index defragmentation and that
> we perform rebuilds regularly so I have a few questions if anyone can help:
> 1.. Can we stop index builds from being logged?
> 2.. If we cannot stop index logging, are there any different practices we
> can follow to do index maintenance that will not require logging (e.g.
> defrag)
> Does anyone have any experience of this?
> If we were to ship index rebuilds or defragmentation, we'll be shipping
> gigabytes of unnecessary data, but apparently SQL Server will not let this
> be switched off?
> Thanks.
>
> Bill
>

Log shipping question

My company is embarking on a DRP project where we will move our production
servers to a less geographically insecure area. We'll need to perform log
shipping back to our DRP site.
I know that SQL Server logs index rebuilds or index defragmentation and that
we perform rebuilds regularly so I have a few questions if anyone can help:
1.. Can we stop index builds from being logged?
2.. If we cannot stop index logging, are there any different practices we
can follow to do index maintenance that will not require logging (e.g.
defrag)
Does anyone have any experience of this?
If we were to ship index rebuilds or defragmentation, we'll be shipping
gigabytes of unnecessary data, but apparently SQL Server will not let this
be switched off?
Thanks.
BillIf you don't log operations, the forth coming log backup would not be useful
. You cannot eat the
cake and have it. :-)
You could look into bulk logged recovery mode, but a log backup in this mode
will include data (not
log records) modified by minimally logged operations. I doubt you will gain
much, if anything.
Your best bet is to only rebuild if you need to. A start is to use the code
in Books Online, DBCC
SHOWCONTIG which only do rebuild if frag level is > a certain level. You can
customize this further
and rebuild different indexes at different times.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bill Wang" <wdw2130833@.hotmail.com> wrote in message
news:9fTle.28354$Ot6.1561166@.news20.bellglobal.com...
> My company is embarking on a DRP project where we will move our production
> servers to a less geographically insecure area. We'll need to perform log
> shipping back to our DRP site.
> I know that SQL Server logs index rebuilds or index defragmentation and th
at
> we perform rebuilds regularly so I have a few questions if anyone can help
:
> 1.. Can we stop index builds from being logged?
> 2.. If we cannot stop index logging, are there any different practices we
> can follow to do index maintenance that will not require logging (e.g.
> defrag)
> Does anyone have any experience of this?
> If we were to ship index rebuilds or defragmentation, we'll be shipping
> gigabytes of unnecessary data, but apparently SQL Server will not let this
> be switched off?
> Thanks.
>
> Bill
>

Log Shipping Question

While reviewing our log shipping setup, I noticed we backup, ship and restore
logs from 7am-7pm. We also perform a full backup at 9pm.
My question is this: Will the transaction backup at 7am pick up
transactions since the last transaction log backup at 7pm or since the full
backup at 9PM. Are we losing 2 hours of transactions?
SqlServer2005 SP2.
Thanks.
Ron
Full and transaction logs are almost completely independent. Almost means
that you have to have a full backup to initialize the log chain. After
that, a full backup has no impact on the transaction log backups. For
example, if last night's backup was bad, you can go back two days and
restore logs up to today. As a side effect, full backups do not mark any
part of the transaction log as inactive so transaction log backups are
necessary to truncate a log when in full recovery mode.
Each log backup consists of the completed and checkpointed transactions
since the last transaction log backup. Once these transactions are backed
up, the log space is marked inactive and can be reused.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:4A35A3CB-E733-4AE5-9780-1DACB82C9ABE@.microsoft.com...
> While reviewing our log shipping setup, I noticed we backup, ship and
> restore
> logs from 7am-7pm. We also perform a full backup at 9pm.
> My question is this: Will the transaction backup at 7am pick up
> transactions since the last transaction log backup at 7pm or since the
> full
> backup at 9PM. Are we losing 2 hours of transactions?
> SqlServer2005 SP2.
> Thanks.
> Ron

Log shipping question

My company is embarking on a DRP project where we will move our production
servers to a less geographically insecure area. We'll need to perform log
shipping back to our DRP site.
I know that SQL Server logs index rebuilds or index defragmentation and that
we perform rebuilds regularly so I have a few questions if anyone can help:
1.. Can we stop index builds from being logged?
2.. If we cannot stop index logging, are there any different practices we
can follow to do index maintenance that will not require logging (e.g.
defrag)
Does anyone have any experience of this?
If we were to ship index rebuilds or defragmentation, we'll be shipping
gigabytes of unnecessary data, but apparently SQL Server will not let this
be switched off?
Thanks.
BillIf you don't log operations, the forth coming log backup would not be useful. You cannot eat the
cake and have it. :-)
You could look into bulk logged recovery mode, but a log backup in this mode will include data (not
log records) modified by minimally logged operations. I doubt you will gain much, if anything.
Your best bet is to only rebuild if you need to. A start is to use the code in Books Online, DBCC
SHOWCONTIG which only do rebuild if frag level is > a certain level. You can customize this further
and rebuild different indexes at different times.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bill Wang" <wdw2130833@.hotmail.com> wrote in message
news:9fTle.28354$Ot6.1561166@.news20.bellglobal.com...
> My company is embarking on a DRP project where we will move our production
> servers to a less geographically insecure area. We'll need to perform log
> shipping back to our DRP site.
> I know that SQL Server logs index rebuilds or index defragmentation and that
> we perform rebuilds regularly so I have a few questions if anyone can help:
> 1.. Can we stop index builds from being logged?
> 2.. If we cannot stop index logging, are there any different practices we
> can follow to do index maintenance that will not require logging (e.g.
> defrag)
> Does anyone have any experience of this?
> If we were to ship index rebuilds or defragmentation, we'll be shipping
> gigabytes of unnecessary data, but apparently SQL Server will not let this
> be switched off?
> Thanks.
>
> Bill
>

Log Shipping between SQL Server versions

Is it possible to perform log shipping between a server running SQL 2000
Enterprise and another server running SQL 2005 Enterprise?
What would be the purpose of the secondary database?
Since you can restore SQL Server 2000 databases into SQL Server 2005
technically you can do log shipping to SQL Server 2005. But the opposite is
not possible because you can not restore SQL Server 2005 databases into SQL
Server 2000.
So, if you lose your SQL Server 2000 database and you only have the 2005
database. Do you plan to continue with 2005 only?
Ben Nevarez
Senior Database Administrator
"KnightFall1" wrote:

> Is it possible to perform log shipping between a server running SQL 2000
> Enterprise and another server running SQL 2005 Enterprise?
|||Hi Ben,
The original question relates to a work situation where we have a SQL 2000
Enterprise production database that needs to be replicated (for reporting
purposes) to an environment that has SQL 2005 running.
So I was curious if we could perform log shipping between the 2 servers to
replicate the database (assuming this is supported between these 2 versions
of SQL Server). This also brings up the question about whether transactional
replication is permitted between SQL 2000 and SQL 2005.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> What would be the purpose of the secondary database?
> Since you can restore SQL Server 2000 databases into SQL Server 2005
> technically you can do log shipping to SQL Server 2005. But the opposite is
> not possible because you can not restore SQL Server 2005 databases into SQL
> Server 2000.
> So, if you lose your SQL Server 2000 database and you only have the 2005
> database. Do you plan to continue with 2005 only?
> Ben Nevarez
> Senior Database Administrator
>
> "KnightFall1" wrote:
|||You say that you want to use the Secondary Server (in Log Shipping) for
reporting purposes. Then you should set its recovery state as STANDBY to
query against it. However then your users will be disconnected for the
coming Transaction Log to be applied to your Secondary Server. So your users
would experience disconnections.
Ekrem ?nsoy
"KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
news:C8170C24-90E5-47C6-ABA0-5139B920FB1C@.microsoft.com...[vbcol=seagreen]
> Hi Ben,
> The original question relates to a work situation where we have a SQL 2000
> Enterprise production database that needs to be replicated (for reporting
> purposes) to an environment that has SQL 2005 running.
> So I was curious if we could perform log shipping between the 2 servers to
> replicate the database (assuming this is supported between these 2
> versions
> of SQL Server). This also brings up the question about whether
> transactional
> replication is permitted between SQL 2000 and SQL 2005.
> "Ben Nevarez" wrote:
|||Hi Ekrem,
The log shipping will be on a set schedule. Currently the business
requirement is once a day (we are still in the process of establishing a
specific time of day) for this process. So even if there is a disconnection,
the users on that end will know when the log shipping is occurring, so there
shouldn't be a problem.
Wouldn't you agree?
"Ekrem ?nsoy" wrote:

> You say that you want to use the Secondary Server (in Log Shipping) for
> reporting purposes. Then you should set its recovery state as STANDBY to
> query against it. However then your users will be disconnected for the
> coming Transaction Log to be applied to your Secondary Server. So your users
> would experience disconnections.
> --
> Ekrem ?nsoy
>
> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
> news:C8170C24-90E5-47C6-ABA0-5139B920FB1C@.microsoft.com...
>
|||Then your users would be querying a database which is older between 0-24
hours than the original one.
If this would not make any trouble and if you set up the Restore job (on the
Secondary Server) to start at a time when your users would be asleep then I
don't think there would be any problem with this setup.
P.S.
You know that your users will be able to use only SELECT against your
Secondary Database right? I just wanted to be sure you know about this.
Ekrem ?nsoy
"KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
news:1F9B7D48-BCC2-4086-8CC5-CE2E01DE9959@.microsoft.com...[vbcol=seagreen]
> Hi Ekrem,
> The log shipping will be on a set schedule. Currently the business
> requirement is once a day (we are still in the process of establishing a
> specific time of day) for this process. So even if there is a
> disconnection,
> the users on that end will know when the log shipping is occurring, so
> there
> shouldn't be a problem.
> Wouldn't you agree?
>
> "Ekrem ?nsoy" wrote:

Log Shipping between SQL Server versions

Is it possible to perform log shipping between a server running SQL 2000
Enterprise and another server running SQL 2005 Enterprise?What would be the purpose of the secondary database?
Since you can restore SQL Server 2000 databases into SQL Server 2005
technically you can do log shipping to SQL Server 2005. But the opposite is
not possible because you can not restore SQL Server 2005 databases into SQL
Server 2000.
So, if you lose your SQL Server 2000 database and you only have the 2005
database. Do you plan to continue with 2005 only?
Ben Nevarez
Senior Database Administrator
"KnightFall1" wrote:
> Is it possible to perform log shipping between a server running SQL 2000
> Enterprise and another server running SQL 2005 Enterprise?|||Hi Ben,
The original question relates to a work situation where we have a SQL 2000
Enterprise production database that needs to be replicated (for reporting
purposes) to an environment that has SQL 2005 running.
So I was curious if we could perform log shipping between the 2 servers to
replicate the database (assuming this is supported between these 2 versions
of SQL Server). This also brings up the question about whether transactional
replication is permitted between SQL 2000 and SQL 2005.
"Ben Nevarez" wrote:
> What would be the purpose of the secondary database?
> Since you can restore SQL Server 2000 databases into SQL Server 2005
> technically you can do log shipping to SQL Server 2005. But the opposite is
> not possible because you can not restore SQL Server 2005 databases into SQL
> Server 2000.
> So, if you lose your SQL Server 2000 database and you only have the 2005
> database. Do you plan to continue with 2005 only?
> Ben Nevarez
> Senior Database Administrator
>
> "KnightFall1" wrote:
> > Is it possible to perform log shipping between a server running SQL 2000
> > Enterprise and another server running SQL 2005 Enterprise?|||You say that you want to use the Secondary Server (in Log Shipping) for
reporting purposes. Then you should set its recovery state as STANDBY to
query against it. However then your users will be disconnected for the
coming Transaction Log to be applied to your Secondary Server. So your users
would experience disconnections.
--
Ekrem Ã?nsoy
"KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
news:C8170C24-90E5-47C6-ABA0-5139B920FB1C@.microsoft.com...
> Hi Ben,
> The original question relates to a work situation where we have a SQL 2000
> Enterprise production database that needs to be replicated (for reporting
> purposes) to an environment that has SQL 2005 running.
> So I was curious if we could perform log shipping between the 2 servers to
> replicate the database (assuming this is supported between these 2
> versions
> of SQL Server). This also brings up the question about whether
> transactional
> replication is permitted between SQL 2000 and SQL 2005.
> "Ben Nevarez" wrote:
>> What would be the purpose of the secondary database?
>> Since you can restore SQL Server 2000 databases into SQL Server 2005
>> technically you can do log shipping to SQL Server 2005. But the opposite
>> is
>> not possible because you can not restore SQL Server 2005 databases into
>> SQL
>> Server 2000.
>> So, if you lose your SQL Server 2000 database and you only have the 2005
>> database. Do you plan to continue with 2005 only?
>> Ben Nevarez
>> Senior Database Administrator
>>
>> "KnightFall1" wrote:
>> > Is it possible to perform log shipping between a server running SQL
>> > 2000
>> > Enterprise and another server running SQL 2005 Enterprise?|||Hi Ekrem,
The log shipping will be on a set schedule. Currently the business
requirement is once a day (we are still in the process of establishing a
specific time of day) for this process. So even if there is a disconnection,
the users on that end will know when the log shipping is occurring, so there
shouldn't be a problem.
Wouldn't you agree?
"Ekrem Ã?nsoy" wrote:
> You say that you want to use the Secondary Server (in Log Shipping) for
> reporting purposes. Then you should set its recovery state as STANDBY to
> query against it. However then your users will be disconnected for the
> coming Transaction Log to be applied to your Secondary Server. So your users
> would experience disconnections.
> --
> Ekrem Ã?nsoy
>
> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
> news:C8170C24-90E5-47C6-ABA0-5139B920FB1C@.microsoft.com...
> > Hi Ben,
> >
> > The original question relates to a work situation where we have a SQL 2000
> > Enterprise production database that needs to be replicated (for reporting
> > purposes) to an environment that has SQL 2005 running.
> >
> > So I was curious if we could perform log shipping between the 2 servers to
> > replicate the database (assuming this is supported between these 2
> > versions
> > of SQL Server). This also brings up the question about whether
> > transactional
> > replication is permitted between SQL 2000 and SQL 2005.
> >
> > "Ben Nevarez" wrote:
> >
> >>
> >> What would be the purpose of the secondary database?
> >>
> >> Since you can restore SQL Server 2000 databases into SQL Server 2005
> >> technically you can do log shipping to SQL Server 2005. But the opposite
> >> is
> >> not possible because you can not restore SQL Server 2005 databases into
> >> SQL
> >> Server 2000.
> >>
> >> So, if you lose your SQL Server 2000 database and you only have the 2005
> >> database. Do you plan to continue with 2005 only?
> >>
> >> Ben Nevarez
> >> Senior Database Administrator
> >>
> >>
> >>
> >> "KnightFall1" wrote:
> >>
> >> > Is it possible to perform log shipping between a server running SQL
> >> > 2000
> >> > Enterprise and another server running SQL 2005 Enterprise?
>|||Then your users would be querying a database which is older between 0-24
hours than the original one.
If this would not make any trouble and if you set up the Restore job (on the
Secondary Server) to start at a time when your users would be asleep then I
don't think there would be any problem with this setup.
P.S.
You know that your users will be able to use only SELECT against your
Secondary Database right? I just wanted to be sure you know about this.
--
Ekrem Ã?nsoy
"KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
news:1F9B7D48-BCC2-4086-8CC5-CE2E01DE9959@.microsoft.com...
> Hi Ekrem,
> The log shipping will be on a set schedule. Currently the business
> requirement is once a day (we are still in the process of establishing a
> specific time of day) for this process. So even if there is a
> disconnection,
> the users on that end will know when the log shipping is occurring, so
> there
> shouldn't be a problem.
> Wouldn't you agree?
>
> "Ekrem Ã?nsoy" wrote:
>> You say that you want to use the Secondary Server (in Log Shipping) for
>> reporting purposes. Then you should set its recovery state as STANDBY to
>> query against it. However then your users will be disconnected for the
>> coming Transaction Log to be applied to your Secondary Server. So your
>> users
>> would experience disconnections.
>> --
>> Ekrem Ã?nsoy
>>
>> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
>> news:C8170C24-90E5-47C6-ABA0-5139B920FB1C@.microsoft.com...
>> > Hi Ben,
>> >
>> > The original question relates to a work situation where we have a SQL
>> > 2000
>> > Enterprise production database that needs to be replicated (for
>> > reporting
>> > purposes) to an environment that has SQL 2005 running.
>> >
>> > So I was curious if we could perform log shipping between the 2 servers
>> > to
>> > replicate the database (assuming this is supported between these 2
>> > versions
>> > of SQL Server). This also brings up the question about whether
>> > transactional
>> > replication is permitted between SQL 2000 and SQL 2005.
>> >
>> > "Ben Nevarez" wrote:
>> >
>> >>
>> >> What would be the purpose of the secondary database?
>> >>
>> >> Since you can restore SQL Server 2000 databases into SQL Server 2005
>> >> technically you can do log shipping to SQL Server 2005. But the
>> >> opposite
>> >> is
>> >> not possible because you can not restore SQL Server 2005 databases
>> >> into
>> >> SQL
>> >> Server 2000.
>> >>
>> >> So, if you lose your SQL Server 2000 database and you only have the
>> >> 2005
>> >> database. Do you plan to continue with 2005 only?
>> >>
>> >> Ben Nevarez
>> >> Senior Database Administrator
>> >>
>> >>
>> >>
>> >> "KnightFall1" wrote:
>> >>
>> >> > Is it possible to perform log shipping between a server running SQL
>> >> > 2000
>> >> > Enterprise and another server running SQL 2005 Enterprise?
>>

Log Shipping between SQL Server versions

Is it possible to perform log shipping between a server running SQL 2000
Enterprise and another server running SQL 2005 Enterprise?What would be the purpose of the secondary database?
Since you can restore SQL Server 2000 databases into SQL Server 2005
technically you can do log shipping to SQL Server 2005. But the opposite is
not possible because you can not restore SQL Server 2005 databases into SQL
Server 2000.
So, if you lose your SQL Server 2000 database and you only have the 2005
database. Do you plan to continue with 2005 only?
Ben Nevarez
Senior Database Administrator
"KnightFall1" wrote:

> Is it possible to perform log shipping between a server running SQL 2000
> Enterprise and another server running SQL 2005 Enterprise?|||Hi Ben,
The original question relates to a work situation where we have a SQL 2000
Enterprise production database that needs to be replicated (for reporting
purposes) to an environment that has SQL 2005 running.
So I was curious if we could perform log shipping between the 2 servers to
replicate the database (assuming this is supported between these 2 versions
of SQL Server). This also brings up the question about whether transactional
replication is permitted between SQL 2000 and SQL 2005.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> What would be the purpose of the secondary database?
> Since you can restore SQL Server 2000 databases into SQL Server 2005
> technically you can do log shipping to SQL Server 2005. But the opposite i
s
> not possible because you can not restore SQL Server 2005 databases into SQ
L
> Server 2000.
> So, if you lose your SQL Server 2000 database and you only have the 2005
> database. Do you plan to continue with 2005 only?
> Ben Nevarez
> Senior Database Administrator
>
> "KnightFall1" wrote:
>|||You say that you want to use the Secondary Server (in Log Shipping) for
reporting purposes. Then you should set its recovery state as STANDBY to
query against it. However then your users will be disconnected for the
coming Transaction Log to be applied to your Secondary Server. So your users
would experience disconnections.
Ekrem ?nsoy
"KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
news:C8170C24-90E5-47C6-ABA0-5139B920FB1C@.microsoft.com...[vbcol=seagreen]
> Hi Ben,
> The original question relates to a work situation where we have a SQL 2000
> Enterprise production database that needs to be replicated (for reporting
> purposes) to an environment that has SQL 2005 running.
> So I was curious if we could perform log shipping between the 2 servers to
> replicate the database (assuming this is supported between these 2
> versions
> of SQL Server). This also brings up the question about whether
> transactional
> replication is permitted between SQL 2000 and SQL 2005.
> "Ben Nevarez" wrote:
>|||Hi Ekrem,
The log shipping will be on a set schedule. Currently the business
requirement is once a day (we are still in the process of establishing a
specific time of day) for this process. So even if there is a disconnection,
the users on that end will know when the log shipping is occurring, so there
shouldn't be a problem.
Wouldn't you agree?
"Ekrem ?nsoy" wrote:

> You say that you want to use the Secondary Server (in Log Shipping) for
> reporting purposes. Then you should set its recovery state as STANDBY to
> query against it. However then your users will be disconnected for the
> coming Transaction Log to be applied to your Secondary Server. So your use
rs
> would experience disconnections.
> --
> Ekrem ?nsoy
>
> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
> news:C8170C24-90E5-47C6-ABA0-5139B920FB1C@.microsoft.com...
>|||Then your users would be querying a database which is older between 0-24
hours than the original one.
If this would not make any trouble and if you set up the Restore job (on the
Secondary Server) to start at a time when your users would be asleep then I
don't think there would be any problem with this setup.
P.S.
You know that your users will be able to use only SELECT against your
Secondary Database right? I just wanted to be sure you know about this.
Ekrem ?nsoy
"KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
news:1F9B7D48-BCC2-4086-8CC5-CE2E01DE9959@.microsoft.com...[vbcol=seagreen]
> Hi Ekrem,
> The log shipping will be on a set schedule. Currently the business
> requirement is once a day (we are still in the process of establishing a
> specific time of day) for this process. So even if there is a
> disconnection,
> the users on that end will know when the log shipping is occurring, so
> there
> shouldn't be a problem.
> Wouldn't you agree?
>
> "Ekrem ?nsoy" wrote:
>