How to add database file to a Existing Configured Log Shipping database in SQL Server with demo

How to add database file to a Existing Configured Log Shipping database in SQL Server with demo

Demo:

How to add database file to a Existing Configured Log Shipping database in SQL Server

Now we can see primary Server information as see below

Primary Server Name: S Q L2012 ENTPEDT

Database Name: Test db E N T P EDT

you can see s y s.master_files path location is same in Primary server database

We can see Secondary Server details

Secondary Server Name: S Q L2012ENTPEDT

Secondary Database Name: Testdb E N TPTEST

we can see after restored database and configured log shipping data files relocated it is different in S y s.master_files and s p_help db as see below

Now we Need to add one file in Primary server as see below

You can see we are going to add one N D F file as See below

After added N D F file it is showing in both system and system S P as see below

Now we need to execute backup job to reflect these changes from Primary server to Secondary server as see below

Backup job went succeeded as see below

Now we need to execute copy job as see below in Secondary Server

in Secondary server copy job went succeeded as below

When you execute Restore job in Secondary server as see below

It throws Error as see below Because those file not exists on Secondary 

To see Error log /History in job to know error cause

it gives error as those transaction log file could not able to restore from primary to secondary because those file is not available 

So to get detail information just copy transaction log file from error log as see below

just open query window on Secondary and just paste it on Restore File List only command to know whether which transaction log backup capture the new L D F file or not

we can use Restore log with no recovery MOVE option but it will gives error as see below

RESTORE log db name FROM Disk='path location\db name_201211 25 15 02 55.t r n'
with no recovery,
MOVE 'new N D F name'
TO 'destination file location\new N D F file name'

But below we have just give from s p _help db from source path location so it gives error as see below

We have to give original Destination path from S y s . master_files location with file name as see below then it will run successfully

after success restore DB went restoring mode

So we need bring db to read only/stand by mode so we need to go the below steps

choose the rest of the transaction log file as see below (Note: we can 't restore  backup file when db in stand by /read only mode)

choose read only /stand by mode with rest of the transaction log file

It went success and db went read only/stand by mode as see below

Now we need to run restore job to get success any way '

we can see reports after fixed error

also see another example 

If the database file path is identical on both servers then this type of issue will not appear. You can avoid this type of issue by making sure that both the primary and secondary databases have the same file paths available.

In the following steps, I will walk through a scenario where the paths did not exist on both servers.

Steps to Add a New Database File to a Log Shipped Database

In our example, the primary server uses the E: drive and the secondary server uses the D: drive.  This is what our issue resulted from.

Step 1Add a new database file to the primary database.  Run the statement below to add a new database file to your primary database.

ALTER DATABASE Manvendra ADD FILE (NAME = Manvendra_test, 
FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MANVENDRA\MSSQL\DATA\Manvendra_test.ndf', 
SIZE = 1000MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
Go

Step 2Once you have added the new database file, then run the logshipping backup job to capture these transactions. Once the backup is completed run the logshipping copy job to copy this backup file to the destination folder to restore it on the standby database.

Step 3Run the logshipping restore job on the secondary server to restore the recently created backup file to apply the changes we made on the primary server. Unfortunately, the restore job will fail to complete with the errors below:

2012-11-25 20:34:17.35 *** Error: The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MANVENDRA\MSSQL\DATA\Manvendra_test.ndf'.
File 'Manvendra_test' cannot be restored to 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MANVENDRA\MSSQL\DATA\Manvendra_test.ndf'. Use WITH MOVE to identify a valid location for the file.

If you have configured your log shipping on a single box between two different instances of SQL Server then you will get the above error.

If you configured log shipping on two different machines and the drive/path on which you have created your new database file on the primary server does not exist on the secondary server then this is the error you will get:

 *** Error: The operating system returned the error '(The path specified does not exit or it is not accessible)' 

Step 4This above error is what we were getting through the alerts because someone had added a database file to the primary database without checking if the path existed on the secondary server. When you encounter this type of issue, first check the last restored file on the secondary database. We can get this info by running the below SQL statement or by running one of the built-in reports in SSMS. You can run the report by clicking on your instance name then right-click and choose reports then select 'standard reports' then run the 'transaction logshipping status' report. This will give you a detailed report about the health of all logshipping databases on that instance.

SELECT secondary_database,
last_restored_date,
last_restored_file
FROM dbo.log_shipping_secondary_databases 

Step 5Now you can easily find which log file you need to restore next. Once you have identified the exact log backup file which needs to be restored, we will verify whether the newly created database file is captured in this backup file or not. You can also find this info in the SQL Server error logs. Run the RESTORE FILELISTONLY statement to see the newly added database file.

RESTORE FILELISTONLY FROM DISK='\\SERVERNAME\logshipping_secondary\Manvendra_20121125150255.trn'

Now you can see that your newly created file is captured in the backup file.

Step 6Now go ahead and restore the correct sequence of log backups on the secondary server to bring logshipping databases in sync using the norecovery and move options as shown below..

RESTORE log Manvendra FROM Disk='\\SERVERNAME\logshipping_secondary\Manvendra_20121125150255.trn'
with norecovery,
MOVE 'Manvendra_test'
TO 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Manvendra_test.ndf'

Step 7Once you manually restored the backup successfully, run your logshipping restore job to restore all pending log backups if there were any. Now you can run Step 4 again to check the last restored file or run the logshipping status report to see the health of the database.

You have now fixed this issue and the new database file has been added to both databases.

Ref:

https://www.mssqltips.com/sqlservertip/2824/how-to-add-database-file-to-a-log-shipped-database-in-sql-server/