April 2, 2012 at 5:24 am
We have a log shipping database that we need to add a secondary datafile to the Primary File group, due to space issues. Is there any way to take the database out of Standby/Read - Only and then put it back in Standyby without doing a full restore from backup.
If so what would the commands be.
The Production database that is shipping the logs does not need to have a new data file added to it.
I have read that there is no need to change anything about the log shipping if a datafile is added to the Primary Filegroup.
Thanks,
Keith
April 2, 2012 at 6:10 am
No way that I'm aware of.
The probability of survival is inversely proportional to the angle of arrival.
April 2, 2012 at 6:44 am
can you share the links which you have read on this so that we can check them out, as this cannot be done, so I am thinking it might be detailing you can add a file on the pirmary and it ships through to the secondary but there is no way to do it just on the secondary
April 2, 2012 at 7:14 am
Can you explain a little more about your situation? Do you not have the same amount of disk space available on disk for your principal server vs your log shipping server?
In a log shipping or mirroring environment you can't have different data files between the principal and secondary.
Creating a new data file on the primary will automatically create that new data file on the secondary/mirror when the log is restored, assuming that the drive exists and has space available. If the drive doesn't exist or have space available then the mirror / log shipping will fail. If you need to have data files associated with different disks between your principal and secondary then you will to specify the location of the files when you restore your mirror / log shipping database.
April 2, 2012 at 7:32 am
I don't want to add a Secondary Filegroup, only a secondary file to the Primary Filegroup.
I don't need to add a secondary file on the production database, only on the database where the
log are being applied for log shipping. If SQL Server sees 2 data files as one large I don't see why having 2 data files on the Standby/Read-Only database would be an issue.
Here is an excerpt from the link below.
You can have a second physical file that's part of your primary file group. This allows you to spread the data over multiple drive letters. You can do this on the fly, and when the first file, sucah as E:\....\DATA.MDF fills up, it will spill over automatically into F:\....\DATA_2.NDF. There is no logical splie, SQL still sees it as one big file and tables and indexes are randomly spread across both, but you get a performance improvement because you have 2 drives instead of 1
http://www.sqlmag.com/forums/aft/80428
Thanks,
Keith
April 2, 2012 at 7:41 am
to add the second file you will need to restore the secondary db, doing this will break logshipping, to get logshipping working again you need to restore from full backup, as production only have 1 file, it will only restore 1 file, so doing it will be a waste of time and resources
if you want to add a second file, you will need to do it on the production databases, this will then be replicated in the log to the secondary databases
the other option is to increase the size of your disk on the secondary server
April 2, 2012 at 11:08 am
If I add a secondary data file to the Primary Filegroup in Production, how will the Secondary db know where to create the secondary file? The drive letters for the SAN on the Produciton server are not the same as on the Secondary db server.
Thanks,
Keith
April 2, 2012 at 1:24 pm
When you restore the secondary, use "WITH MOVE <Logical File Name> to <Physical File Name>" to point the new data file to a different physical location.
April 2, 2012 at 11:42 pm
Saronabound (4/2/2012)
If I add a secondary data file to the Primary Filegroup in Production, how will the Secondary db know where to create the secondary file? The drive letters for the SAN on the Produciton server are not the same as on the Secondary db server.Thanks,
Keith
The restore job would fail if there is any issue with the path/file.
Check the job history of the first failed restore job.
Identify the log backup file which failed to restore.
Restore it "manually" WITH MOVE and NORECOVERY option.
After that log shipping should continue without any issue.
April 3, 2012 at 5:32 am
Lots of good input.
Here is what I plan to do:
Primary Datbase: A
Secondary Database: B
On Primary Instance
Alter database A ADD FILE (NAME 'a2', FILENAME 'G:\MSSQL\Data\a.ndf')
TO FILEGROUP Primary
GO
On Secondary Instance
Alter database A_Log ADD FILE (NAME 'a2', FILENAME 'G:\MSSQL\Data\a.ndf')
TO FILEGROUP Primary
GO
After Log Shipping fails
Do the following on Secondary Instance
RESTORE DATABASE A_Log From Disk='E:\MSSQL\BACKUP\a.trn' WITH MOVE 'a2' TO 'G>\:\MSSQL\Data\a.ndf',
NO RECOVERY
Does this sound correct?
Thanks all for your input.
Keith
April 3, 2012 at 5:48 am
nope, you will need to fully reinitialise logshipping from scratch with a new full backup for the changes to take effect, you cant do this (below) as you cannot modify the log to tell it where to restore it to
RESTORE DATABASE A_Log From Disk='E:\MSSQL\BACKUP\a.trn' WITH MOVE 'a2' TO 'G>\:\MSSQL\Data\a.ndf',
NO RECOVERY
April 3, 2012 at 6:23 am
I was trying to avoid doing a full restore, but if I have to, I have to, no big deal.
Thankss
April 3, 2012 at 6:41 am
You can tell SQL Server where to palce the new file using RESTORE LOG using the WITH MOVE clause. You would have to handle the restore of the log file manually. You could pause log shipping, create the new file on the production system, take t-log backup, restore the t-log backup to the log shipped database using the MOVE and NORECOVERY options, restart log shipping.
April 3, 2012 at 6:43 am
well you learn something new every day, I didnt think this was possible
April 3, 2012 at 7:00 am
Thanks, that is what I wanted to do.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply