August 13, 2009 at 10:53 am
I have one production database and it has files and file groups. I placed the most active data tables in one file group called it FG1 and in the PRIMARY file group, I placed security tables, master tables and look up tables and these are not changing very often. Here is the scenario in my mind,
I want to create a new database at secondary server and restore the production database at the secondary with only PRIMARY file group. Now, users can access this database without FG1. Then I want to back up FG1 at production and restore to the secondary continuously with one hour interval. So, users can access this server without disturbing the production server.
Is this scenario possible, if not how we can achieve this using File Groups?
I know we can achieve this by snapshot and log shipping. I am trying to see if we can do this using FILE GROUPS. I appreciate your help. Thanks.
August 13, 2009 at 1:01 pm
Based on your question & as per my understanding I tested in my testinstance. Its worked.
I did the following, check if you expected the one.
Created a test database with PRIMARY and user filegroup.
CREATE Database TestFG
ON PRIMARY
(Name=TestData
,Filename='C:\SQL2008\Data\TestData.mdf'
)
,FILEGROUP TestFG
(Name=TestData_FG
,Filename='C:\SQL2008\Data\TestData_FG.ndf'
)
LOG ON
(Name=TestLog
,Filename='C:\SQL2008\Data\TestLog.ldf'
);
Took backup of the empty database with only PRIMARY filegroup.
BACKUP DATABASE TestFG
FILEGROUP ='PRIMARY'
TO DISK='C:\SQL2008\Backup\TestFg.Bak'
Restored the database with Standby option with different name (TestFG_Bk) and moved only the PRIMARY filegroup physical file with different name. (not moving user filegroup file). Restored successfully.
RESTORE DATABASE TestFG_bk
FROM DISK ='C:\SQL2008\Backup\TestFg.Bak'
WITH MOVE 'TestData' TO 'C:\SQL2008\Data\TestData_bk.mdf'
,MOVE 'TestLog' tO 'C:\SQL2008\Data\TestLog_bk.ldf'
,STANDBY ='C:\SQL2008\Backup\TestFg.undo'
Create a test table with test data on the first (TestFG) database and insert a test data.
Create Table Test
(Name VARCHAR(10))
INSERT INTO Test (Name) VALUES('TestFilegroup')
Take transactional backup.
BACKUP LOG TestFG TO DISK= 'C:\SQL2008\Backup\TestFg.TRN'
Restore the Trn backup on the TestFG_Bk database with Standby option.
RESTORE LOG TestFG_Bk FROM DISK= 'C:\SQL2008\Backup\TestFg.TRN'
WITH STANDBY ='C:\SQL2008\Backup\TestFg_bk.undo'
This test is worker for me. If this is you expected, then you are good to go.
Thanks
Jay.
Thanks
Jay
http://www.sqldbops.com
August 13, 2009 at 1:08 pm
I added another table on the user filegroup as below and took another Trn backup and restored on the target database with standby option and tried to do a SELECT on the table and got the below error.
Create table TestFg
(Name VARCHAR(10))
ON TestFG
SELECT * FROM Testfg
Msg 8653, Level 16, State 1, Line 1
The query processor is unable to produce a plan for the table or view 'Testfg' because the table resides in a filegroup which is not online.
Hope it helps.
Jay.
Thanks
Jay
http://www.sqldbops.com
August 13, 2009 at 1:31 pm
Well, Thanks for the reply. I understand the transaction log back up and restore. I am wondering if I can do that using Online File Group Backup and File Group Restore without going through having the database in standby mode. In other words, if I restore the db with primary file group it is already available for access, later I can restore other file groups over the primary and can I keep do that like log shipping?
August 13, 2009 at 6:21 pm
unless you keep the database in standby or norecoverymode, you can not apply the diff or trn backups on top of it.
Still you can do that thru logshipping, only thing is you've to do the first restore in the secondary with the required filegroup.
Thanks
Jay
http://www.sqldbops.com
August 13, 2009 at 6:55 pm
What are you trying to accomplish overall? It sounds like mirroring might be a good solution for you. Only the active changes will move over.
August 13, 2009 at 9:37 pm
On Server1:
Created a test database with PRIMARY and user filegroup.
CREATE Database TestFG
ON PRIMARY
(Name=TestData
,Filename='C:\SQL2008\Data\TestData.mdf'
)
,FILEGROUP TestFG
(Name=TestData_FG
,Filename='C:\SQL2008\Data\TestData_FG.ndf'
)
LOG ON
(Name=TestLog
,Filename='C:\SQL2008\Data\TestLog.ldf'
);
Took backup of the empty database.
BACKUP DATABASE TestFG
TO DISK='C:\SQL2008\Backup\TestFg.Bak'
Take transactional backup.
BACKUP LOG TestFG TO DISK= 'C:\SQL2008\Backup\TestFg.bak'
Server 2:
Restored the database and moved only the PRIMARY filegroup physical file with different name. (not moving user filegroup file). Restored successfully.
RESTORE DATABASE TestFG_bk
FROM DISK ='C:\SQL2008\Backup\TestFg.Bak'
WITH MOVE 'TestData' TO 'C:\SQL2008\Data\TestData_bk.mdf'
,MOVE 'TestLog' tO 'C:\SQL2008\Data\TestLog_bk.ldf'
,NORECOVERY
Restore the Trn backup on the TestFG_Bk database
RESTORE LOG TestFG_Bk FROM DISK= 'C:\SQL2008\Backup\TestFg.bak'
WITH Recovery
Recover only the secondary filegroup keeping database online.
RESTORE DATABASE TestFG
FILE='testdata_fg'
FROM DISK ='C:\TestFG.Bak'
WITH recovery,MOVE 'TestData_fg' TO 'C:\TestData_fbk.mdf'
HTH!
MJ
August 14, 2009 at 11:20 am
sihaab (8/13/2009)
I want to create a new database at secondary server and restore the production database at the secondary with only PRIMARY file group. Now, users can access this database without FG1. Then I want to back up FG1 at production and restore to the secondary continuously with one hour interval. So, users can access this server without disturbing the production server.
Manu,
on his request, he wants to restore the changes on the secondary continuously with one hour interval, if the database restored WITH RECOVERY then how you can apply the changes continuously?
Thanks
Jay
http://www.sqldbops.com
August 14, 2009 at 11:37 am
I was just trying to show him how "online piecemeal restore" works. I agree with Steve that mirroring can be a good option here.
MJ
August 14, 2009 at 11:50 am
Even I too if he needs only to update the changes on the target, but if he wants to keep the secondary database online for the users then he can't, unless he go for snapshot from the mirrored database.
Thanks
Jay
http://www.sqldbops.com
August 14, 2009 at 11:53 am
Steve,
I agree with you, mirroring is the good idea to setup a secondary read only database. Log shipping could be other choice but you cannot access the database while logs restoring. I am just wondering is that possible with FILEGROUP or not.
August 16, 2009 at 5:12 am
sihaab (8/14/2009)
Steve,I agree with you, mirroring is the good idea to setup a secondary read only database. Log shipping could be other choice but you cannot access the database while logs restoring. I am just wondering is that possible with FILEGROUP or not.
If you setup log shipping in standby mode, u'll be able to query the secondary database when it's not restoring. In mirroring, you cannot access the mirror database at all unless failover happens.
August 16, 2009 at 5:18 am
sihaab (8/13/2009)
In other words, if I restore the db with primary file group it is already available for access, later I can restore other file groups over the primary and can I keep do that like log shipping?
Correct me if i donot understand ur requirement. You want ur primary filegroup to be restored and then secondary filegroup(FG1) to a new server. Then you want only changes done to the FG1 to propagate to the secondary server. If this is your requirement, I dont think it is possible to do selective portion of logs to restore on secondary server. You may want to look at transactional replication wherein you can select the objects which are a part of FG1 to be replicated to the secondary.
August 16, 2009 at 8:19 am
You can't selectively restore logs, but you can continue to restore filegroups (new FG backups) over time.
August 16, 2009 at 8:42 am
ps (8/16/2009)
In mirroring, you cannot access the mirror database at all unless failover happens.
You can, providing it's Enterprise edition. Create a database snapshot on the mirror (has to be synchronised). The snapshot is readable, read-only and reflects the database at the time that the snapshot was created.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply