Thursday, April 21, 2011

How to Add Secondary files to the Logshipping database

One of my friend asked me how to add a file to a log shipped database without reconfiguring log shipping. I tested the following in SQL 2008 Enterprise edition server to find out the information and it worked fine :

1. I configured log shipping between the databases info system
2. Stopped and disabled all the backup, copy and restore jobs.
3. Added a secondary file named Infi_data1.ndf to the log shipped info system database in primary server
4. Manually took a transaction log backup for the database after adding the file
5. Manually copied the transaction log backup to secondary server
6. Manually restored that particular transaction log backup using the WITH MOVE option and WITH NO RECOVERY clause in secondary server

Basically while using the WITH MOVE option I would mention the newly created secondary file i had recently added to the log shipped database.

The following is the script to add a new file to the info system database and restoring the log backup in secondary server using no recovery or using standby option
ALTER DATABASE info system
ADD FILE
(
NAME = Infi_data1,
FILENAME ='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Infi_data1.ndf',
SIZE = 5MB,
MAXSIZE = 15,
FILEGROWTH = 10%
)

GO

RESTORE filelistonly from disk='D:\Database\SQLDBA\Infisys.trn'

GO

RESTORE log Info system FROM Disk='D:\Database\SQLDBA\Infisys.trn'
WITH MOVE 'Infi_data1' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.KATMAI\MSSQL\DATA\Infi_data1.ndf',
norecovery