| Andrew Edwards 2002-08-02, 7:25 am |
| Keith,
Great write up.
I need to do log shipping to an off-site reporting server. The problem =
is that I need to have the database accessible during normal business =
hours. Obviously if I have to kill users every 15 minutes(my current =
schedule for backup) to do the log restore then I really don't have a =
good window.
My initail thought is to track the last backup done, then restore up to =
"FILE=3D1" in the log backup device.
Any ideas on how I could track this and use it in the script.
Andrew Edwards
"Keith Kratochvil" <keith.kratochvil.back2u@novusprintmedia.com> wrote =
in message news:eAB6Fdh5BHA.1456@tkmsftngp02...
> --here is my "canned" response:
> /*By the way, you should be able to use job names instead of job =
ids....this
> should make the entire process easier to read!*/
> /*I have recently updated my jobs to use job names....but I have not =
updated
> this example*/
>=20
>=20
> 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.
>=20
> 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.
>=20
> repeat steps 3 and 4 as often as needed.
> You can bring your secondary server online if you restore WITH =
RECOVERY.
>=20
> You can also automate the entire process.
> This is the method that I used:
>=20
> 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)
>=20
>=20
> Primary server:
> --backup database job (nightly)
> BACKUP DATABASE foo to dmp_foo WITH INIT
> exec Secondary.msdb.dbo.sp_start_job @job_id =3D
> '75A9FD81-BGFF-12D4-A469-064654DE', @server_name =3D 'Secondary'
> GO
>=20
> 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 =3D '\\Primary\D$\dmp_foo.BAK'
> WITH FILE =3D 1, NOUNLOAD , STANDBY =3D 'E:\mssql7\logs\undo.ldf', =
STATS =3D
> 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
>=20
>=20
> Now, throughout the day, I backup the logs on the primary server:
> BACKUP LOG foo to dmp_foo_LOG WITH INIT
> exec Secondary.msdb.dbo.sp_start_job @job_id =3D
> '75A9GEA1-BCFF-11D4-A469-0008186E7D0A', @server_name =3D 'Secondary'
> /*I also zip and copy the log file to another server at this point.*/
>=20
>=20
> 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 =3D '\\Primary\d$\dmp_foo_LOG.BAK'
> WITH FILE =3D 1, STANDBY =3D 'E:\mssql7\logs\undo.ldf'
>=20
>=20
> If you ever want to bring your secondary server online, you need to =
restore
> WITH RECOVERY
> RESTORE LOG foo
> FROM DISK =3D '\\Primary\d$\dmp_foo_LOG.BAK'
> WITH FILE =3D 1, NOUNLOAD , RECOVERY
>=20
>=20
> /*
> an article written on the subject that holds your hand a bit more than =
my
> simple write up does
> http://www.sql-server-performance.c...og_shipping.asp
> */
>=20
>=20
> /*
> also, here are some MS KB articles on the subject:
> http://support.microsoft.com/defaul...b;EN-US;q275146
> =
http://www.microsoft.com/technet/tr...D/TechNet/prod=
techn
> ol/sql/reskit/sql2000/part4/c1361.asp
>=20
> */
>=20
> --
> Keith, SQL Server MVP
>=20
> "Leon Chapman" <lchapman@teletrack> wrote in message
> news:39f701c1e615$1a46bdf0$36e
f2ecf@tkmsftngxa12...
> > If anyone can help me with this one I would really
> > appreciate it. I'm currently running sql server 2k on
> > win2k server. I need to set up a reporting server using
> > log shipping. The problem is the wizard doesn't allow me
> > to schedule the updates when I would like (all at once
> > during the night as opposed to each file on a schedule) so
> > I'll have to script the log restores. I've done a log-
> > restore script before, but not in a windows environment.
> > In that case, a "shell" had to be created to restore the
> > necessary transaction logs so I'm just guessing I'll need
> > something similar here. Does anyone know of any book or
> > website where I could go to get some ideas on how to
> > create a script that would load the necessary logs or is
> > there a generic script that everyone uses as a template?
> > I don't really need to "ship" the logs since the drive is
> > shared, I only need to restore them all at once during the
> > night. Thanks.
> >
> > Leon
>=20
>=20
|