Sometimes due to disk space issue or with some other issue, we may need to add additional file to logshipping primary database, in that case the logshipping will fail from that point onwards. We will receive the below error message.
* Error: Could not apply log backup file 'C:\Temp\PrimaryDBLogs\PrimaryDB_20120419115948.trn' to secondary database 'SecondaryDB'.(Microsoft.SqlServer.Management.LogShipping) ***
Error: The file 'C:\Appl\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Secondary_File.ndf' cannot be overwritten. It is being used by database 'PrimaryDB'.
File 'Secondary_File' cannot be restored to 'C:\Appl\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Secondary_File.ndf'. Use WITH MOVE to identify a valid location for the file.
Steps
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
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',
STANDBY = N'C:\Appl\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_SecondaryDB.BAK'
10) Verify file added or not using the below command
select * from secondaryDb..sysfiles
11) Enable the backup, copy and restore jobs.
No comments: