November 12, 2009 at 11:23 pm
Hi All,
I have .mdf, .log files all those files are online, I conform with the following querry
from sys.database_files df left outer join sys.filegroups fg on df.data_space_id = fg.data_space_id
[/center][/left]
But i am uable to take back up, Please help me, it's important.
Note: I dont have full back up., Please help me, it's important.
Note: I dont have full back up.
Error Message:
Backup failed for server 'Employee'
System.data.sqlclient. Sqlerror: The backup of the file or folegroup 'Emplyeetest' 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)
Emplyeetest is My MDF file name.
November 13, 2009 at 8:15 am
Can you access the database through the GUI ?? See all the tables & data ?
November 15, 2009 at 9:22 pm
Hi,
Yes, I am able to access the data base through GUI.
I had done all the operations on the data base except Data base backup.
Thanks,
Suresh
November 16, 2009 at 10:01 am
Hi Suresh, Do you have any full-text indexes attached to this database? It appears another member was experiencing similar issues, dropping the FT indexes and rebuilding seemed to resolve their issue. I'm not sure if this is possible for you or not, but might be worth looking into.
March 11, 2010 at 1:37 am
Hi,
Even i am stucked up with the same problem. I have full text catalog for the database. So i drop the full text index on the table. Even though i found one of the file(catalog file) is offline where i queried the system table sys.master_files. when i tried to take the backup of database it gives 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)
Please help how to take backup of this database by making the file online
Thanks in advance
March 11, 2010 at 1:42 am
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).
You will need to restore the file from backup. If you do not have a backup, according to the article, you will not be able to get the file back online.
http://msdn.microsoft.com/en-us/library/ms190241.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 17, 2010 at 12:16 am
I have backup of the database but it was taken before 3 months. Now if restore the file from backup to make it online, the updated values in the file will be lost, then the database is not uptodate. So, how shall i proceed?
March 17, 2010 at 4:43 am
suresh.maddali (11/12/2009)
Hi All,I have .mdf, .log files all those files are online, I conform with the following querry
select fg.name as FilegroupName, fg.type_desc as FileGroupType, df.name as FileName, df.physical_name, df.state_descfrom sys.database_files df left outer join sys.filegroups fg on df.data_space_id = fg.data_space_id
[/center][/left]
But i am uable to take back up, Please help me, it's important.
Note: I dont have full back up.
You cannot do a File / Filegroup backup without first doing a FULL database backup. SQL Server will not allow it. All backup types are only able to be run AFTER you do a FULL database backup.
Do a FULL backup, then run your File / Filegroup backup and it should run successfully.
March 17, 2010 at 4:47 am
starterm (3/17/2010)
I have backup of the database but it was taken before 3 months. Now if restore the file from backup to make it online, the updated values in the file will be lost, then the database is not uptodate. So, how shall i proceed?
Umm, this is a very bad situation. Right now your only options are to take a current FULL backup of the database and restore it as a differently name database. Then restore your original DB with the file from backup, then import all your data from the second DB that doesn't exist in the original DB.
Then change your backup plan strategy so this doesn't happen to you again. You should be doing FULL backups at least once a month, if not once a week. I do FULLs daily, but our DBs are relatively small. If your database doesn't take longer than an hour to backup, then do one daily, not once every blue moon.
March 17, 2010 at 10:46 am
Brandie Tarvin (3/17/2010)
starterm (3/17/2010)
I have backup of the database but it was taken before 3 months. Now if restore the file from backup to make it online, the updated values in the file will be lost, then the database is not uptodate. So, how shall i proceed?Umm, this is a very bad situation. Right now your only options are to take a current FULL backup of the database and restore it as a differently name database. Then restore your original DB with the file from backup, then import all your data from the second DB that doesn't exist in the original DB.
Then change your backup plan strategy so this doesn't happen to you again. You should be doing FULL backups at least once a month, if not once a week. I do FULLs daily, but our DBs are relatively small. If your database doesn't take longer than an hour to backup, then do one daily, not once every blue moon.
Agreed on the full backups and frequency.
We backup our 500Gb dbs daily. Size is relative, and thus the max size to backup daily could be different for different people.
I think Brandie has provided a good solution to try. Give it a go, and let us know how things work out for you.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 17, 2010 at 11:48 pm
Thanks for the reply, from your post i understood in the below way, please correct if i am wrong, I should take old backup which i have taken before 3 months and restore it with new name and later i should import updated data of tables from the corrupted database i.e. currently on which i am using for my development to the newly restored database from the oldbackup.
If i should follow above method, how shall i import updated data of all tables and new objects like procedures and functions which i have created in the corrupted database into the newly restored database?
March 18, 2010 at 4:07 am
Starterm,
Go ahead and start a new topic for this issue. Your problem seems to be different than the OP. The OP is having trouble making a File backup. Your problem seems to be with a restore.
Starting a new thread will help avoid confusion with two separate threads of advice and solution. Please don't piggy-back off of someone else's and confusing the issue.
Once you start your new topic, I'll be happy to re-iterate my advice and clarify what I mean.
March 18, 2010 at 11:43 pm
Hi,
I posted new question in "ASK SSC". under backup tag.
March 19, 2010 at 5:09 am
Ask SSC??? What's the URL to the post? I'm not seeing it in the SQL Server 2005 -> Backups forum.
March 22, 2010 at 12:16 am
please find the below link
http://ask.sqlservercentral.com/questions/4734/not-able-to-take-backup-of-a-database
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply