“The Only Thing That Is Constant Is Change”

Restore job failing for LOG SHIPPING

Posted by database-wiki on October 15, 2012

=> The primary database ENTERPRISE-DB is in the clustered instance with name ENTERPRISE-SERVER.

=> The ENTERPRISE-DR server hosts the secondary database. This is also DR server.

=> We have added 4 secondary files to the primary server after which the secondary server went into suspect mode. The Transaction Log backup is not being applied because ENTERPRISE-DB secondary database is in suspect mode.

=> Collecting the ERROR from the secondary instance and checked why the database went into suspect mode.

Looking at the ERRORLOG:


2012-09-15 18:49:25.81 spid59    FCB::CreateFile() failed with error 3 for file O:\EDATA37\EDATA37.NDF.

2012-09-15 18:49:31.85 spid59    Starting up database ‘ENTERPRISE-DB’.

2012-09-15 18:49:31.87 spid59    Bypassing recovery for database ‘ENTERPRISE-DB’ because it is marked IN LOAD.

2012-09-15 18:49:32.56 spid59    Starting up database ‘ENTERPRISE-DB’.

2012-09-15 18:49:32.57 spid59    Bypassing recovery for database ‘ENTERPRISE-DB’ because it is marked IN LOAD.

2012-09-15 18:49:32.81 spid59    udopen: Operating system error 3(The system cannot find the path specified.) during the creation/opening of physical device O:\EDATA37\EDATA37.NDF.

2012-09-15 18:49:32.81 spid59    FCB::Open failed: Could not open device O:\EDATA37\EDATA37.NDF for virtual device number (VDN) 41.

2012-09-15 18:49:32.81 spid59    Device activation error. The physical file name ‘O:\EDATA37\EDATA37.NDF’ may be incorrect.


=> So the path O:\EDATA37, O:\EDATA38, O:\EDATA39, O:\EDATA40 did not existing in the secondary server and the alter database did not create those secondary files.

=> Since NDF were missing the secondary database EDP went into suspect mode.

=> We created O:\EDATA37, O:\EDATA38, O:\EDATA39, O:\EDATA40 on the secondary server.

=> we did sp_resetstatus ‘ENTERPRISE-DB’ and restarted the secondary instance so that the database ‘ENTERPRISE-DB’ can recover.

=> Once the Secondary instance came online the restore job started to work and log shipping is back on track.

Best practices for LOG SHIPPING:


Adding File To Log shipping Database
1) I created two databases called PrimaryDB and Secondary DB
2) Configured Logshipping between primarydb and secondarydb (Secondary in Readonly Mode)
3) Ensure Logshipping is configured and running smooth between the servers
4) Stop the logshipping and disable logshipping jobs
5) Add data file (ndf) to primaryDB and make sure same patch is located in the secondary server where the secondary database is located.
6) Run the LSBackup manually
7) Run the copy job manually (ensure the file copied to target server)
8) Run the below command to verify whether this trn file consists additional data file or not
Restore filelistonly from disk=’C:\temp\PrimaryDBLogs\PrimaryDB_20120419115948.trn’
9) Run the below restore command to add the data file to secondaryDb on target server

Use Master

RESTORE log [SecondaryDB] FROM Disk=’C:\temp\PrimaryDBLogs\PrimaryDB_20120419115948.trn’
WITH MOVE ‘Secondary_File’
TO ‘C:\Appl\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Secondary_File2.ndf’,
10) Verify file added or not using the below command

select * from secondaryDb..sysfiles

11) Enable the backup, copy and restore jobs.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: