| Author |
Topic |
|
pelegk2
Aged Yak Warrior
723 Posts |
 Posted - 2010-06-14 : 03:17:57
|
| i have a db, in which i create a FileGroup on a diffrend Physical disk, and put tables on.if the disk with the FG dosen't work, how can i bring back the DB to work (i mean the orignal DB withought the FG)? |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-06-15 : 20:00:36
|
| You would need to first place the FG on a working disk using either DETACH/ATTACH or BACKUP/RESTORE. Either of these approaches allows you to map the logical files to physical files. At this point, you should be up and running. If you still need to actually remove the FG, you need to:1) Create similar tables in the original file group (PRIMARY?)2) Move data from the original tables to the new tables.3) Delete the tables in the FG4) Rename the new table that you just populated in the original file group to use the original table names5) Remove the FG using an ALTER DATABASE queryDoesn't really seem worth the effort to remove the FG, does it?=======================================A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007) |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-06-15 : 20:13:28
|
| You can simplify the process a bit:1. If the table has a clustered index, use ALTER INDEX or CREATE INDEX...WITH DROP_EXISTING and place it on the PRIMARY filegroup1a. If it does NOT have a clustered index, use CREATE INDEX and place it on PRIMARY2. Repeat for all tables you wish to move. If you have non-clustered indexes on the filegroup you'll need to ALTER them and move them to PRIMARY3. Use DBCC SHRINKFILE with the EMPTYFILE option to ensure the file(s) in the filegroup have no remaining data or indexes4. Use ALTER DATABASE to remove the files and filegroup |
|
|
|
pelegk2
Aged Yak Warrior
723 Posts |
Posted - 2010-06-16 : 02:48:08
|
| i think you miss understand me.i have a db, and a FG on it (not the Primary one)the FG is placed in another disk then the Primary one.what can i do if the Hard Disk with the Primary group stoped from working (for example bad sectors on the Hard Disk which i cant access any more).how in this case i can continue to work with the Primary DB? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-06-16 : 06:32:25
|
| If that occurred you will almost certainly end up with a corrupted database and won't be able to move data to another filegroup. It's definitely not the basis for a recovery strategy. |
|
|
|
pelegk2
Aged Yak Warrior
723 Posts |
Posted - 2010-06-16 : 06:59:13
|
| isnt there a way to attach the DB withought the FG? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-06-16 : 09:32:46
|
| Perhaps (read: not likely), but again, that is not a reliable recovery method for disk failure. You need to take regular backups and test them for restorability. You can also use a restored backup to perform DBCC checks for data corruption, in case you cannot do them regularly in production.I recommend you read Paul Randal's blog, especially his articles on backup and restore:http://sqlskills.com/BLOGS/PAUL/category/BackupRestore.aspxHe has an article in there on a proper restore strategy. He also covers DBCC CHECKDB in depth since, well, he wrote the code for it when he worked at Microsoft. |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-06-16 : 12:16:36
|
| Rob,I like your idea about rebuilding the primary key into the desired file group. I had completely overlooked that approach. Thanks!=======================================A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
|
FileGroups