December 29, 2015 at 12:01 pm
Hi All,
I'm in some desperate help regarding partitioning in SQL 2008 R2 please...
We had a table which was 800gb, partitioned by day across about 100 filegroups.
We had to change an id column from into to big int, we scripted up the change and executed it.
It has dropped the table and then ran out of disk space 7 hours later.
Now we are left with a partition scheme, partition function and all of the files are still there on the hard drive.
I have recreated the table and the indexes but have no idea what to do next...
How do I get the data from the files back in the table...
I was new to partitioning 1 year ago, learnt about it using Brent Ozar and this has been running fine since, now I have the issue I cant figure out what to do.
Any help appreciated its pretty urgent.
thanks
Steve
December 29, 2015 at 2:14 pm
steven.oates (12/29/2015)
How do I get the data from the files back in the table...
You don't. Like any dropped object, restore your last backup as a new database, restore any log backups necessary to get the DB to the point in time you want, then copy the data over.
To have destroyed the table, the script must have had a drop table in there somewhere (maybe accidentally not commented out) as otherwise the change would just have rolled back
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 29, 2015 at 2:40 pm
We raised a ticket with our support team however they are saying the database hasnt been backing up since 23rd October (obviously this will be dealt with seperately) due to diskspace.
I've managed to recreate the table and indexes (aligned and non-aligned) and load in data for 26th into correct partition.
The issue i have now id i have 100+ filegroups on the disk but need to know how to add them back into the table
can this be done?
Steve
December 29, 2015 at 3:06 pm
What, exactly, is the situation and what precisely happened?
You don't add filegroups to tables, tables are stored on filegroups, so the question doesn't make much sense.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 29, 2015 at 3:35 pm
One way to get the data back is by stopping all activity on your database and read the datapages directly.
You'll have to parse the data pages by yourself.
However because you don't know which datapages belonged to your table your script has to go through every datapage and try to match it.
Hopefully this table didn't have any newer datatypes like geography or row overflow data.
Getting this back will take a specialist, will be very expensive, will take a lot of hours and there's no guarantee that you'll be able to get everything back.
I've done something like this before and was able to get only about 20% of data back. but that was because of hardcore corruption and not table dropping.
But the principle is the same.
_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
December 29, 2015 at 4:29 pm
Sorry I apologise for not making much sense, I'm a sql developer / accidental dba not much experience in partitioning...
Essentially we had to change a data type of a column in the partitioned table, but the column was a non aligned, non clustered index on the primary filegroup.
Therefore one of my developers scripted up the change and execuated it (without consulting with me) let in run for 7 hours and it ran out of diskspace.
I'm assuming it did have to drop the table and then recreate it but never got to the recreate step.
I've managed to rebuild the table on the partition scheme and load in data for boxing day but all oter days (even though they show correct partition and filegroup) are empty.
If i look on the disk the files are there and if i look at the database, the files are correctly associated to their respective file group.
Hopefully this partition helper result set helps explain (26th has data all other days empty):
pf_namepartition_scheme_namepartition_numberpartition_filegrouppf_type_descpf_fanoutboundary_value_on_rightobject_namerange_valuenum_rowsreserved_mb_all_indexesnum_indexes
DailyAlbertPFDailyAlbertPS1DailyAlbertFG1RANGE1281FactAgentAlbertPortalSessionEntriesNULL00.0000004
DailyAlbertPFDailyAlbertPS3DailyAlbertFG156RANGE1281FactAgentAlbertPortalSessionEntries2015-09-15 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS4DailyAlbertFG157RANGE1281FactAgentAlbertPortalSessionEntries2015-09-16 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS5DailyAlbertFG158RANGE1281FactAgentAlbertPortalSessionEntries2015-09-17 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS6DailyAlbertFG159RANGE1281FactAgentAlbertPortalSessionEntries2015-09-18 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS7DailyAlbertFG160RANGE1281FactAgentAlbertPortalSessionEntries2015-09-19 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS8DailyAlbertFG161RANGE1281FactAgentAlbertPortalSessionEntries2015-09-20 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS9DailyAlbertFG162RANGE1281FactAgentAlbertPortalSessionEntries2015-09-21 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS10DailyAlbertFG163RANGE1281FactAgentAlbertPortalSessionEntries2015-09-22 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS11DailyAlbertFG164RANGE1281FactAgentAlbertPortalSessionEntries2015-09-23 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS12DailyAlbertFG165RANGE1281FactAgentAlbertPortalSessionEntries2015-09-24 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS13DailyAlbertFG166RANGE1281FactAgentAlbertPortalSessionEntries2015-09-25 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS14DailyAlbertFG167RANGE1281FactAgentAlbertPortalSessionEntries2015-09-26 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS15DailyAlbertFG168RANGE1281FactAgentAlbertPortalSessionEntries2015-09-27 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS16DailyAlbertFG169RANGE1281FactAgentAlbertPortalSessionEntries2015-09-28 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS17DailyAlbertFG170RANGE1281FactAgentAlbertPortalSessionEntries2015-09-29 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS18DailyAlbertFG171RANGE1281FactAgentAlbertPortalSessionEntries2015-09-30 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS19DailyAlbertFG172RANGE1281FactAgentAlbertPortalSessionEntries2015-10-01 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS20DailyAlbertFG173RANGE1281FactAgentAlbertPortalSessionEntries2015-10-02 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS21DailyAlbertFG174RANGE1281FactAgentAlbertPortalSessionEntries2015-10-03 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS22DailyAlbertFG175RANGE1281FactAgentAlbertPortalSessionEntries2015-10-04 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS23DailyAlbertFG176RANGE1281FactAgentAlbertPortalSessionEntries2015-10-05 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS24DailyAlbertFG177RANGE1281FactAgentAlbertPortalSessionEntries2015-10-06 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS25DailyAlbertFG178RANGE1281FactAgentAlbertPortalSessionEntries2015-10-07 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS26DailyAlbertFG179RANGE1281FactAgentAlbertPortalSessionEntries2015-10-08 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS27DailyAlbertFG180RANGE1281FactAgentAlbertPortalSessionEntries2015-10-09 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS28DailyAlbertFG181RANGE1281FactAgentAlbertPortalSessionEntries2015-10-10 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS29DailyAlbertFG182RANGE1281FactAgentAlbertPortalSessionEntries2015-10-11 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS30DailyAlbertFG183RANGE1281FactAgentAlbertPortalSessionEntries2015-10-12 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS31DailyAlbertFG184RANGE1281FactAgentAlbertPortalSessionEntries2015-10-13 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS32DailyAlbertFG185RANGE1281FactAgentAlbertPortalSessionEntries2015-10-14 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS33DailyAlbertFG186RANGE1281FactAgentAlbertPortalSessionEntries2015-10-15 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS34DailyAlbertFG187RANGE1281FactAgentAlbertPortalSessionEntries2015-10-16 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS35DailyAlbertFG188RANGE1281FactAgentAlbertPortalSessionEntries2015-10-17 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS36DailyAlbertFG189RANGE1281FactAgentAlbertPortalSessionEntries2015-10-18 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS37DailyAlbertFG190RANGE1281FactAgentAlbertPortalSessionEntries2015-10-19 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS38DailyAlbertFG191RANGE1281FactAgentAlbertPortalSessionEntries2015-10-20 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS39DailyAlbertFG192RANGE1281FactAgentAlbertPortalSessionEntries2015-10-21 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS40DailyAlbertFG193RANGE1281FactAgentAlbertPortalSessionEntries2015-10-22 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS41DailyAlbertFG194RANGE1281FactAgentAlbertPortalSessionEntries2015-10-23 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS42DailyAlbertFG195RANGE1281FactAgentAlbertPortalSessionEntries2015-10-24 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS43DailyAlbertFG196RANGE1281FactAgentAlbertPortalSessionEntries2015-10-25 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS44DailyAlbertFG197RANGE1281FactAgentAlbertPortalSessionEntries2015-10-26 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS45DailyAlbertFG198RANGE1281FactAgentAlbertPortalSessionEntries2015-10-27 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS46DailyAlbertFG199RANGE1281FactAgentAlbertPortalSessionEntries2015-10-28 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS47DailyAlbertFG200RANGE1281FactAgentAlbertPortalSessionEntries2015-10-29 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS48DailyAlbertFG201RANGE1281FactAgentAlbertPortalSessionEntries2015-10-30 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS49DailyAlbertFG202RANGE1281FactAgentAlbertPortalSessionEntries2015-10-31 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS50DailyAlbertFG203RANGE1281FactAgentAlbertPortalSessionEntries2015-11-01 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS51DailyAlbertFG204RANGE1281FactAgentAlbertPortalSessionEntries2015-11-02 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS52DailyAlbertFG205RANGE1281FactAgentAlbertPortalSessionEntries2015-11-03 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS53DailyAlbertFG206RANGE1281FactAgentAlbertPortalSessionEntries2015-11-04 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS54DailyAlbertFG207RANGE1281FactAgentAlbertPortalSessionEntries2015-11-05 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS55DailyAlbertFG208RANGE1281FactAgentAlbertPortalSessionEntries2015-11-06 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS56DailyAlbertFG209RANGE1281FactAgentAlbertPortalSessionEntries2015-11-07 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS57DailyAlbertFG210RANGE1281FactAgentAlbertPortalSessionEntries2015-11-08 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS58DailyAlbertFG211RANGE1281FactAgentAlbertPortalSessionEntries2015-11-09 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS59DailyAlbertFG212RANGE1281FactAgentAlbertPortalSessionEntries2015-11-10 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS60DailyAlbertFG213RANGE1281FactAgentAlbertPortalSessionEntries2015-11-11 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS61DailyAlbertFG214RANGE1281FactAgentAlbertPortalSessionEntries2015-11-12 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS62DailyAlbertFG215RANGE1281FactAgentAlbertPortalSessionEntries2015-11-13 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS63DailyAlbertFG216RANGE1281FactAgentAlbertPortalSessionEntries2015-11-14 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS64DailyAlbertFG217RANGE1281FactAgentAlbertPortalSessionEntries2015-11-15 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS65DailyAlbertFG218RANGE1281FactAgentAlbertPortalSessionEntries2015-11-16 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS66DailyAlbertFG219RANGE1281FactAgentAlbertPortalSessionEntries2015-11-17 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS67DailyAlbertFG220RANGE1281FactAgentAlbertPortalSessionEntries2015-11-18 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS68DailyAlbertFG221RANGE1281FactAgentAlbertPortalSessionEntries2015-11-19 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS69DailyAlbertFG222RANGE1281FactAgentAlbertPortalSessionEntries2015-11-20 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS70DailyAlbertFG223RANGE1281FactAgentAlbertPortalSessionEntries2015-11-21 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS71DailyAlbertFG224RANGE1281FactAgentAlbertPortalSessionEntries2015-11-22 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS72DailyAlbertFG225RANGE1281FactAgentAlbertPortalSessionEntries2015-11-23 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS73DailyAlbertFG226RANGE1281FactAgentAlbertPortalSessionEntries2015-11-24 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS74DailyAlbertFG227RANGE1281FactAgentAlbertPortalSessionEntries2015-11-25 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS75DailyAlbertFG228RANGE1281FactAgentAlbertPortalSessionEntries2015-11-26 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS76DailyAlbertFG229RANGE1281FactAgentAlbertPortalSessionEntries2015-11-27 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS77DailyAlbertFG230RANGE1281FactAgentAlbertPortalSessionEntries2015-11-28 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS78DailyAlbertFG231RANGE1281FactAgentAlbertPortalSessionEntries2015-11-29 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS79DailyAlbertFG232RANGE1281FactAgentAlbertPortalSessionEntries2015-11-30 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS80DailyAlbertFG233RANGE1281FactAgentAlbertPortalSessionEntries2015-12-01 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS81DailyAlbertFG234RANGE1281FactAgentAlbertPortalSessionEntries2015-12-02 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS82DailyAlbertFG235RANGE1281FactAgentAlbertPortalSessionEntries2015-12-03 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS83DailyAlbertFG236RANGE1281FactAgentAlbertPortalSessionEntries2015-12-04 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS84DailyAlbertFG237RANGE1281FactAgentAlbertPortalSessionEntries2015-12-05 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS85DailyAlbertFG238RANGE1281FactAgentAlbertPortalSessionEntries2015-12-06 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS86DailyAlbertFG239RANGE1281FactAgentAlbertPortalSessionEntries2015-12-07 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS87DailyAlbertFG240RANGE1281FactAgentAlbertPortalSessionEntries2015-12-08 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS88DailyAlbertFG241RANGE1281FactAgentAlbertPortalSessionEntries2015-12-09 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS89DailyAlbertFG242RANGE1281FactAgentAlbertPortalSessionEntries2015-12-10 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS90DailyAlbertFG243RANGE1281FactAgentAlbertPortalSessionEntries2015-12-11 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS91DailyAlbertFG244RANGE1281FactAgentAlbertPortalSessionEntries2015-12-12 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS92DailyAlbertFG245RANGE1281FactAgentAlbertPortalSessionEntries2015-12-13 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS93DailyAlbertFG246RANGE1281FactAgentAlbertPortalSessionEntries2015-12-14 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS94DailyAlbertFG247RANGE1281FactAgentAlbertPortalSessionEntries2015-12-15 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS95DailyAlbertFG248RANGE1281FactAgentAlbertPortalSessionEntries2015-12-16 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS96DailyAlbertFG249RANGE1281FactAgentAlbertPortalSessionEntries2015-12-17 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS97DailyAlbertFG250RANGE1281FactAgentAlbertPortalSessionEntries2015-12-18 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS98DailyAlbertFG251RANGE1281FactAgentAlbertPortalSessionEntries2015-12-19 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS99DailyAlbertFG252RANGE1281FactAgentAlbertPortalSessionEntries2015-12-20 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS100DailyAlbertFG253RANGE1281FactAgentAlbertPortalSessionEntries2015-12-21 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS101DailyAlbertFG254RANGE1281FactAgentAlbertPortalSessionEntries2015-12-22 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS102DailyAlbertFG255RANGE1281FactAgentAlbertPortalSessionEntries2015-12-23 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS103DailyAlbertFG256RANGE1281FactAgentAlbertPortalSessionEntries2015-12-24 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS104DailyAlbertFG257RANGE1281FactAgentAlbertPortalSessionEntries2015-12-25 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS105DailyAlbertFG258RANGE1281FactAgentAlbertPortalSessionEntries2015-12-26 00:00:00.0001464320513.8359374
DailyAlbertPFDailyAlbertPS106DailyAlbertFG259RANGE1281FactAgentAlbertPortalSessionEntries2015-12-27 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS107DailyAlbertFG260RANGE1281FactAgentAlbertPortalSessionEntries2015-12-28 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS108DailyAlbertFG261RANGE1281FactAgentAlbertPortalSessionEntries2015-12-29 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS109DailyAlbertFG262RANGE1281FactAgentAlbertPortalSessionEntries2015-12-30 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS110DailyAlbertFG263RANGE1281FactAgentAlbertPortalSessionEntries2015-12-31 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS111DailyAlbertFG264RANGE1281FactAgentAlbertPortalSessionEntries2016-01-01 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS112DailyAlbertFG265RANGE1281FactAgentAlbertPortalSessionEntries2016-01-02 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS113DailyAlbertFG266RANGE1281FactAgentAlbertPortalSessionEntries2016-01-03 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS114DailyAlbertFG267RANGE1281FactAgentAlbertPortalSessionEntries2016-01-04 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS115DailyAlbertFG268RANGE1281FactAgentAlbertPortalSessionEntries2016-01-05 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS116DailyAlbertFG269RANGE1281FactAgentAlbertPortalSessionEntries2016-01-06 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS117DailyAlbertFG270RANGE1281FactAgentAlbertPortalSessionEntries2016-01-07 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS118DailyAlbertFG271RANGE1281FactAgentAlbertPortalSessionEntries2016-01-08 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS119DailyAlbertFG272RANGE1281FactAgentAlbertPortalSessionEntries2016-01-09 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS120DailyAlbertFG273RANGE1281FactAgentAlbertPortalSessionEntries2016-01-10 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS121DailyAlbertFG274RANGE1281FactAgentAlbertPortalSessionEntries2016-01-11 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS122DailyAlbertFG275RANGE1281FactAgentAlbertPortalSessionEntries2016-01-12 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS123DailyAlbertFG276RANGE1281FactAgentAlbertPortalSessionEntries2016-01-13 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS124DailyAlbertFG277RANGE1281FactAgentAlbertPortalSessionEntries2016-01-14 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS125DailyAlbertFG278RANGE1281FactAgentAlbertPortalSessionEntries2016-01-15 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS126DailyAlbertFG279RANGE1281FactAgentAlbertPortalSessionEntries2016-01-16 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS127DailyAlbertFG280RANGE1281FactAgentAlbertPortalSessionEntries2016-01-17 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS128DailyAlbertFG281RANGE1281FactAgentAlbertPortalSessionEntries2016-01-18 00:00:00.00000.0000004
DailyAlbertPFDailyAlbertPS2DailyAlbertFG30RANGE1281FactAgentAlbertPortalSessionEntriesNULL00.0000004
December 29, 2015 at 6:16 pm
As far as I've understood all this, there is no standard easy method to get this data back with what you have.
You'll have to reverse engineer the data files mdf, ndf's to connect the correct schema to the correct data pages.
If you end up not finding another way send me a private message and I can try to see if I can help you out with some advanced methods.
_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
December 30, 2015 at 2:35 am
steven.oates (12/29/2015)
Therefore one of my developers scripted up the change and execuated it (without consulting with me) let in run for 7 hours and it ran out of diskspace.
Can you post the exact script? Without it we're guessing.
I'm assuming it did have to drop the table and then recreate it but never got to the recreate step.
If it's the script I suspect, it would have created a new table, copied the data across and dropped the old table. If the copy failed due to lack of space, the drop could easily have run afterwards, if there was no error handling.
If i look on the disk the files are there and if i look at the database, the files are correctly associated to their respective file group.
Yup, but that doesn't mean anything. If the table has been dropped, the table has been dropped and is gone. There's no undo in SQL Server.
At this point, I think you have two options.
1) Restore from backup, accept the loss of two months of data
2) Take the DB offline NOW, copy all the files out and then get someone (like Mladen, I can do it as well, but am busy until the 18th Jan) to try and scrape what data they can out of the raw files with undocumented methods. You may get some data back, you may not, this will cost a lot of money. This also requires that nothing's been written to the filegroups where the table used to be.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 30, 2015 at 7:30 am
First, any DBA who tells you the database hasn't been backed up for months; I just wouldn't take their word for it, and they could be wrong. Before proceeding any further, confirm for yourself when the last backup occurrred by using the script in the article linked below.
Script to retrieve SQL Server database backup history and no backups
Also, your chance of success is parsing usable data from the files depends on whether it uses encryption or Clustered ColumnStore.
Post the create scripts for these tables.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply