June 16, 2010 at 11:50 am
Hi all ,
I am trying logshipping in local machine and create two instances of sql server 2000 .
But at the end it stuck in initialize destination server nothing happened.
At the step of add destination database i put database sr1 as because it written it create new database
is it the reason it cant initialize database?
Plz let me know how will i do it? What will i finally get i can see the primary one is backed up.
DO i have to manual backup and restore this database i think so as automatic is not possible.
And what about share folder ? at one step of maintenance plan wizard it asked for share folder to put the
backup file .
My assumption is at final result after log shipping automatically a new database will create and when in primary database a new query submitted i can at least read those submitted query from 2nd database in 2nd instances of this am i correct ?
Can someone plz guide me? I am newbie at this.
Plz help
July 8, 2010 at 12:44 pm
Hi,
In order to setup log shipping in sql server follow the following steps:
On the production server
1) Change the recovery model of the database being logged shipped to FULL.
2) Create an SQL Job and name it Log Shipping - Full Backup:
A) Add the following steps:
1) Take a full database backup
2) Copy the full backup to the standby (DR) server
3) Kill any user that are logged into the logged shipped database on the standby (DR) server
4) Restore the full backup on to the standby (DR) server,. Sample script:
RESTORE DATABASE MyDatabase
FROM DISK = '\\MyComputerShare\LogShipping\MyBackup_Full.bak'
WITH StandBY = '\\MyComputerShare\LogShipping\MyUndoFile.undo.dat', replace,
MOVE 'MyData_Data' TO 'X:\MSSQL2K\MSSQL\data\MyData_Data.MDF',
MOVE 'MyLog_Log' TO 'Y:\MSSQL2K\MSSQL\data\MyData_Log.LDF'
GO
B) Schedule the job to run on a daily bases every 12 hours. Optional you could schedule this once a week or a month.
3) Create an SQL Job and name it Log Shipping - Trans Backup:
A) Add the following steps:
1) Take a transactional database backup
2) Copy the transactional backup to the standby (DR) server
3) Kill any user that are logged into the logged shipped database on the standby (DR) server
4) Restore the trans backup on to the standby (DR) server,. Sample script:
RESTORE LOG MyDatabase FROM DISK='\\MyComputerShare\LogShipping\MyBackup_Tran.trn' WITH STANDBY= '\\MyComputerShare\LogShipping\MyUndoFile.undo.dat'
B) Schedule the job to run on a daily bases every 15 minutes. This depends on what your companies loss of data is.
Finally Run the Log Shipping - Full Backup and wait for it to complete. Next run the Log Shipping - Trans Backup. This will ensure that we are in sync and the log shipping process can start.
Note: Make sure you do clean up of backup folders in order to free up some space.
I hope this was helpful for you.
Thanks,
Hagop
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply