x

not able to take backup of a database

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......

more ▼

asked Mar 11, 2010 at 05:05 AM in Default

avatar image

starterm
31 2 2 5

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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).

Source: http://msdn.microsoft.com/en-us/library/ms190241.aspx

more ▼

answered Mar 11, 2010 at 05:30 AM

avatar image

CirqueDeSQLeil
5.5k 11 13 20

I have backup which is taken before long time i.e. 4 months back. Now if i restore the database from old backup, i will loose the newly updated data in database. Already i dropPED the full text index on the table and finally deleted the table also. But still i can see entry in sys.master_files saying the file is offline and its type is FULLTEXT.In what way shall i proceed ?

Mar 11, 2010 at 06:48 AM starterm
(comments are locked)
10|1200 characters needed characters left

Based on your comment, there is a second question and a second answer - IMO.

Try

ALTER DATABASE ... REMOVE FILE

Check MSDN or BOL for the syntax.

more ▼

answered Mar 11, 2010 at 02:41 PM

avatar image

CirqueDeSQLeil
5.5k 11 13 20

If i remove the corrupted file, i will loose the data which is related to the file

Mar 18, 2010 at 02:59 AM starterm
(comments are locked)
10|1200 characters needed characters left

Unfortunately, you can't do much. You're going to lose data someway.

Do you not have any file / file group backups?

If so, restore the db from the last FULL backup to a new db name and then restore the file / filegroup backups to see if that resolves the problem.

If you can't do that, then try backing up each filegroup individually (except the one that's offline). And restore those backups to the new database. You'll have to manually recreate the data and objects that exist in the corrupt filegroup onto the new database.

more ▼

answered Mar 22, 2010 at 08:01 AM

avatar image

Brandie Tarvin
1

Thanks for the reply

I dont have file or file group backups. I just have backup which was taken 4 months back.

Mar 24, 2010 at 02:36 AM starterm
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x247

asked: Mar 11, 2010 at 05:05 AM

Seen: 1590 times

Last Updated: Mar 11, 2010 at 05:05 AM

Copyright 2017 Redgate Software. Privacy Policy