Hi Team, I have a database RECOV which has primary filegroup and i have added one more filegroup Test_group and added two secondary files sec_file_3.ndf,sec_file_4.ndf to this new filegroup. By using the following query i made one of the secondary file offline.
ALTER DATABASE RECOV MODIFY FILE ( NAME = sec_file_3, offline)
Now when i am trying to take backup of the database it is giving the following error
System.Data.SqlClient.SqlError: The backup of the file or filegroup "sec_file_3" is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)
How to make the sec_file_3.ndf online?
Please help how to rectify the above problem for taking the backup of database
Thanks in advance......
asked Mar 11, 2010 at 05:05 AM in Default
A file should only be set offline when the file is corrupted, but it can be restored. A file set to offline can only be set online by restoring the file from backup. For more information about restoring a single file, see RESTORE (Transact-SQL).
If you have never performed a filegroup backup, or a full backup with this file - you will not be able to bring it back online. If you have performed a backup - you can restore the database and get the file back to online.
Another possible option is to drop the file and recreate the file (not preferrable if the file contains data).
answered Mar 11, 2010 at 05:30 AM
Based on your comment, there is a second question and a second answer - IMO.
Check MSDN or BOL for the syntax.
answered Mar 11, 2010 at 02:41 PM