Setup Log shipping from log shipped database

• generalrouting

  SSC Enthusiast

  Points: 177

  More actions

  March 20, 2015 at 5:47 pm

  #305951

  Problem:

  We have log shipping for databases from production to back office (BO) environments for users to run reports. In preparation for moving a back office environment to a different server, we want to setup an addition log shipping environment, let’s call it BO2, same as the BO. One of the challenges we had was the amount of time it would take to get the full backup from production to BO2 due to the size of the database. It would take days just to transfer the full backup.

  Solution:

  Let’s just say the database being logged shipped is DB123. Instead of using the full backup of DB123 from production, we use the mdf and ldf of DB123 from current log shipped database on the BO to setup the additional log shipping on BO2.

  Steps:

  1.Setup the directory structure for the undo file (tuf) on the BO2 server same as the BO server.

  Note: The undo can be moved to different drive after the log shipping is setup.

  2.Stop SQL services on the BO server.

  3.Copy the undo file (tuf), mdf, and ldf files of the log shipped database, DB123, from server BO to server BO2.

  Note: Keep these files as main copies because they will be used multiple times. Do NOT copy files to where you’ll put the new database files.

  4.After the files are copied, start SQL services on BO.

  5.On the BO2 server, create an empty database DB123 with database name, file_id, logical names, physical names exactly the same as the ones on the BO server.

  Note: Make the size of mdf and ldf small so it doesn’t take long to create the database.

  6.Stop SQL Services on the BO2 server.

  7.Overwrite the new mdf and ldf files of the empty database DB123 with the mdf and ldf files that were copied over from BO.

  8.Start SQL services on BO2.

  Note: If the file_id, logical names and physical names are not matching up, the database will be in suspect mode.

  9.Do a backup of DB123 with COPY_ONLY.

  10.Drop DB123 on BO2 after backup is complete.

  11.Use the full backup from previous step to setup log shipping of DB123 on BO2.

  Note: Below was the LiteSpeed syntax that was used

  exec master.dbo.xp_restore_database @database = N'DB123' ,

  @filename = N'F:\SQL Backups\DB123_FULL.slsfull',

  @filenumber = 1,@with = N'STATS = 10',

  @with = N'STANDBY = N''C:\ \MSSQL\Data\ROLLBACK_UNDO_DB123.trn''',

  @affinity = 0,@logging = 0

  12.Stop SQL services on BO2.

  13.Again, overwrite the mdf, ldf and undo file (tuf) of DB123 on server BO2 with mdf, ldf, and undo file (tuf) from BO.

  14.Start SQL services on BO2.

  15.Replay transaction log from PRD

  Note: You can change the location of the undo file by copy the current undo file to the new location and point to new location in “STANDBY” parameter. DO NOT do cut and paste of undo file but do copy and paste.

  It works on Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64) . I have not tried it on SQL Server 2012 yet.

  In steps that stop/start SQL services, you can bring the database offline/online.

  In a way, this is doing detach/attach a log shipped database

  Jonathan H.