log shipping question

log shipping question

Post by Paul Ibiso » Fri, 07 Dec 2001 01:32:48



Is it possible to use Log Shipping from Enterprise SQL 2000 to Standard SQL
2000? - need to buy the cheapest licences possible!
Thanks
Paul
 
 
 

log shipping question

Post by Narayana Vyas Kondredd » Fri, 07 Dec 2001 02:14:57


BOL says:
In the Add Destination Database screen, select a server name.
The server must be registered and running Microsoft? SQL ServerT 2000
Enterprise Edition to appear in the drop-down list.
--
HTH,
Vyas,
SQL Server FAQ, articles, code samples, Books,

http://vyaskn.tripod.com/


Quote:> Is it possible to use Log Shipping from Enterprise SQL 2000 to Standard
SQL
> 2000? - need to buy the cheapest licences possible!
> Thanks
> Paul


 
 
 

log shipping question

Post by Paul Ibiso » Fri, 07 Dec 2001 02:15:38


It does - thanks!



> BOL says:
> In the Add Destination Database screen, select a server name.
> The server must be registered and running Microsoft? SQL ServerT 2000
> Enterprise Edition to appear in the drop-down list.
> --
> HTH,
> Vyas,
> SQL Server FAQ, articles, code samples, Books,

> http://vyaskn.tripod.com/



> > Is it possible to use Log Shipping from Enterprise SQL 2000 to Standard
> SQL
> > 2000? - need to buy the cheapest licences possible!
> > Thanks
> > Paul

 
 
 

log shipping question

Post by Keith Kratochvi » Fri, 07 Dec 2001 02:25:50


Hopefully you have not given up yet....

It is a common misconception that you need SQL2k or the Back Office Resource
Kit to use log shipping.
If you read up on warm standby servers within Books Online, you will get
lots of good information.

It is easy to "roll your own" log shipping
the basics are
1. full database backup on primary server
2. restore database to secondary server WITH STANDBY
3. backup transaction log on primary server
4. restore transaction log to secondary server WITH STANDBY.

repeat steps 3 and 4 as often as needed.
You can bring your secondary server online if you restore WITH RECOVERY.

You can also automate the entire process.
This is the method that I used:

On my primary server, I have set up a linked server to the secondary server
(I replaced the server names with Primary and Secondary here)

Primary server:
--backup database job (nightly)
BACKUP DATABASE foo to dmp_foo WITH INIT


GO

The job on the primary server starts this job on the Secondary server:
--restore the database
exec usp_KillTheUsersConnected 'foo' --kills the users connected to the
database
go
RESTORE DATABASE foo
FROM  DISK = '\\Primary\D$\dmp_foo.BAK'
WITH  FILE = 1,  NOUNLOAD ,  STANDBY = 'E:\mssql7\logs\undo.ldf', STATS =
10,  REPLACE ,
MOVE N'foo_Data' TO N'F:\MSSQL7\data\foo.MDF',
MOVE N'foo_Log' TO N'E:\mssql7\logs\foo.ldf'
go

Now, throughout the day, I backup the logs on the primary server:
BACKUP LOG foo to dmp_foo_LOG WITH INIT


/*I also zip and copy the log file to another server at this point.*/

The job listed above starts this job on the secondary server:
exec usp_KillTheUsersConnected 'foo' --kills the users connected to the
database
go
RESTORE LOG foo
FROM  DISK = '\\Primary\d$\dmp_foo_LOG.BAK'
WITH FILE = 1, STANDBY = 'E:\mssql7\logs\undo.ldf'

If you ever want to bring your secondary server online, you need to restore
WITH RECOVERY
RESTORE LOG foo
FROM  DISK = '\\Primary\d$\dmp_foo_LOG.BAK'
WITH  FILE = 1,  NOUNLOAD ,  RECOVERY

--
Keith, SQL Server MVP


Quote:> Is it possible to use Log Shipping from Enterprise SQL 2000 to Standard
SQL
> 2000? - need to buy the cheapest licences possible!
> Thanks
> Paul

 
 
 

log shipping question

Post by Jasper Smit » Fri, 07 Dec 2001 06:01:31


Snap.
I've done the same in 7 Standard because I wasn't keen on the BORK solution
We zip all ours as well because its over a slow WAN
We have twice had to recover db's for different sites on the standby and
ran one site for 2 days off it whilst its server was rebuilt.
It's a fairly simple process and runs well 99% of the time (main problem is
the WAN
sometimes goes down because our routers are a bit twichy)

HTH
Jasper Smith

"Keith Kratochvil" <Keith.please email only when

> Hopefully you have not given up yet....

> It is a common misconception that you need SQL2k or the Back Office
Resource
> Kit to use log shipping.
> If you read up on warm standby servers within Books Online, you will get
> lots of good information.

> It is easy to "roll your own" log shipping
> the basics are
> 1. full database backup on primary server
> 2. restore database to secondary server WITH STANDBY
> 3. backup transaction log on primary server
> 4. restore transaction log to secondary server WITH STANDBY.

> repeat steps 3 and 4 as often as needed.
> You can bring your secondary server online if you restore WITH RECOVERY.

> You can also automate the entire process.
> This is the method that I used:

> On my primary server, I have set up a linked server to the secondary
server
> (I replaced the server names with Primary and Secondary here)

> Primary server:
> --backup database job (nightly)
> BACKUP DATABASE foo to dmp_foo WITH INIT


> GO

> The job on the primary server starts this job on the Secondary server:
> --restore the database
> exec usp_KillTheUsersConnected 'foo' --kills the users connected to the
> database
> go
> RESTORE DATABASE foo
> FROM  DISK = '\\Primary\D$\dmp_foo.BAK'
> WITH  FILE = 1,  NOUNLOAD ,  STANDBY = 'E:\mssql7\logs\undo.ldf', STATS =
> 10,  REPLACE ,
> MOVE N'foo_Data' TO N'F:\MSSQL7\data\foo.MDF',
> MOVE N'foo_Log' TO N'E:\mssql7\logs\foo.ldf'
> go

> Now, throughout the day, I backup the logs on the primary server:
> BACKUP LOG foo to dmp_foo_LOG WITH INIT


> /*I also zip and copy the log file to another server at this point.*/

> The job listed above starts this job on the secondary server:
> exec usp_KillTheUsersConnected 'foo' --kills the users connected to the
> database
> go
> RESTORE LOG foo
> FROM  DISK = '\\Primary\d$\dmp_foo_LOG.BAK'
> WITH FILE = 1, STANDBY = 'E:\mssql7\logs\undo.ldf'

> If you ever want to bring your secondary server online, you need to
restore
> WITH RECOVERY
> RESTORE LOG foo
> FROM  DISK = '\\Primary\d$\dmp_foo_LOG.BAK'
> WITH  FILE = 1,  NOUNLOAD ,  RECOVERY

> --
> Keith, SQL Server MVP



> > Is it possible to use Log Shipping from Enterprise SQL 2000 to Standard
> SQL
> > 2000? - need to buy the cheapest licences possible!
> > Thanks
> > Paul

 
 
 

1. Log Shipping question

Yes, you can save the backup files under any name etc you want. You can then restore
from latest db backup, subsequent log backups and possibly up to point in time
(STOPAT). SQL Server is not sensitive about name of file etc (although you won't get
help from backup history tables when doing restore in EM).

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil at: http://www.sqlserverfaq.com
Please reply to the newsgroup only, not by email.

2. problem with regional options

3. urgent newbie log shipping question!!!

4. Getting the DATA TYPE from syscolumns: Code Sample #4:db_tab2.ec

5. Log shipping questions

6. HELP! - Cannot view SQL 7.0 database stats

7. Log Shipping Question with SQL2000 Enterprise Edition

8. Merge Replication works only one way

9. Help with Log Shipping Question?

10. log shipping question

11. new log shipping question

12. Log Shipping Question

13. Failover, Log Shipping Urgent Question... Please Help