May 14, 2014 at 3:39 am
Hi, the question is this ...
I'm busy testing backup and restore of Filegroups ...
We have a 'READ_ONLY' filegroup and a 'PRIMARY' filegroup which is read_write ...
To backup I can use the below for the Primary (read and write)
------
USE MASTER
BACKUP DATABASE [SQL_TEST] FILEGROUP = 'PRIMARY' TO DISK = 'D:\SQL_BACKUPS\SQL_TEST_PRIMARY.BAK'
GO
---- OR this to backup ALL the read_write_filegroups
USE MASTER
GO
BACKUP DATABASE [SQL_TEST] READ_WRITE_FILEGROUPS TO DISK = 'D:\SQL_BACKUPS\SQL_TEST_R_W_new.BAK'
GO
-------
To Backup the Read_only filegroup I can use
-------
USE MASTER
BACKUP DATABASE [SQL_TEST] FILEGROUP = 'READ_ONLY' TO DISK = 'D:\SQL_BACKUPS\SQL_TEST_READ_ONLY.BAK'
GO
-------
If I have more than 1 READ_ONLY FileGroup must I specify them individually as the last statement above or can I backup all the read_only filegroups as I can with the read_write files groups with the READ_WRITE_FILEGROUPS option?
Same with the restore ... with tthe READ_WRITE_FILEGROUPS option I can restore all the read write filegroups ... with the read_only filegroups I can't find a read_only option to restore all the read only filegroups in 1 statement ...
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
May 14, 2014 at 6:13 am
Check the documentation on the BACKUP command. There is no facility for just getting the READ_ONLY file groups. You'll have to go after each one individually. Partial backup documentation says the same thing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 14, 2014 at 6:46 am
Thanks Grant, that is what I got as well, thought I was missing something but thats for confirming my findings 😀
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply