Partitioned table dropped! HELP!

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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_name partition_scheme_name partition_number partition_filegroup pf_type_desc pf_fanout boundary_value_on_right object_name range_value num_rows reserved_mb_all_indexes num_indexes

    DailyAlbertPF DailyAlbertPS 1 DailyAlbertFG1 RANGE 128 1 FactAgentAlbertPortalSessionEntries NULL 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 3 DailyAlbertFG156 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-09-15 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 4 DailyAlbertFG157 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-09-16 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 5 DailyAlbertFG158 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-09-17 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 6 DailyAlbertFG159 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-09-18 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 7 DailyAlbertFG160 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-09-19 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 8 DailyAlbertFG161 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-09-20 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 9 DailyAlbertFG162 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-09-21 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 10 DailyAlbertFG163 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-09-22 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 11 DailyAlbertFG164 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-09-23 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 12 DailyAlbertFG165 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-09-24 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 13 DailyAlbertFG166 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-09-25 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 14 DailyAlbertFG167 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-09-26 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 15 DailyAlbertFG168 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-09-27 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 16 DailyAlbertFG169 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-09-28 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 17 DailyAlbertFG170 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-09-29 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 18 DailyAlbertFG171 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-09-30 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 19 DailyAlbertFG172 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-01 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 20 DailyAlbertFG173 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-02 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 21 DailyAlbertFG174 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-03 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 22 DailyAlbertFG175 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-04 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 23 DailyAlbertFG176 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-05 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 24 DailyAlbertFG177 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-06 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 25 DailyAlbertFG178 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-07 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 26 DailyAlbertFG179 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-08 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 27 DailyAlbertFG180 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-09 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 28 DailyAlbertFG181 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-10 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 29 DailyAlbertFG182 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-11 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 30 DailyAlbertFG183 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-12 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 31 DailyAlbertFG184 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-13 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 32 DailyAlbertFG185 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-14 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 33 DailyAlbertFG186 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-15 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 34 DailyAlbertFG187 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-16 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 35 DailyAlbertFG188 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-17 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 36 DailyAlbertFG189 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-18 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 37 DailyAlbertFG190 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-19 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 38 DailyAlbertFG191 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-20 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 39 DailyAlbertFG192 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-21 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 40 DailyAlbertFG193 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-22 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 41 DailyAlbertFG194 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-23 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 42 DailyAlbertFG195 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-24 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 43 DailyAlbertFG196 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-25 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 44 DailyAlbertFG197 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-26 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 45 DailyAlbertFG198 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-27 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 46 DailyAlbertFG199 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-28 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 47 DailyAlbertFG200 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-29 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 48 DailyAlbertFG201 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-30 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 49 DailyAlbertFG202 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-10-31 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 50 DailyAlbertFG203 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-01 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 51 DailyAlbertFG204 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-02 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 52 DailyAlbertFG205 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-03 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 53 DailyAlbertFG206 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-04 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 54 DailyAlbertFG207 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-05 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 55 DailyAlbertFG208 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-06 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 56 DailyAlbertFG209 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-07 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 57 DailyAlbertFG210 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-08 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 58 DailyAlbertFG211 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-09 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 59 DailyAlbertFG212 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-10 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 60 DailyAlbertFG213 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-11 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 61 DailyAlbertFG214 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-12 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 62 DailyAlbertFG215 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-13 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 63 DailyAlbertFG216 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-14 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 64 DailyAlbertFG217 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-15 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 65 DailyAlbertFG218 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-16 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 66 DailyAlbertFG219 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-17 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 67 DailyAlbertFG220 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-18 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 68 DailyAlbertFG221 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-19 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 69 DailyAlbertFG222 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-20 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 70 DailyAlbertFG223 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-21 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 71 DailyAlbertFG224 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-22 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 72 DailyAlbertFG225 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-23 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 73 DailyAlbertFG226 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-24 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 74 DailyAlbertFG227 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-25 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 75 DailyAlbertFG228 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-26 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 76 DailyAlbertFG229 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-27 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 77 DailyAlbertFG230 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-28 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 78 DailyAlbertFG231 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-29 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 79 DailyAlbertFG232 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-11-30 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 80 DailyAlbertFG233 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-01 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 81 DailyAlbertFG234 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-02 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 82 DailyAlbertFG235 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-03 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 83 DailyAlbertFG236 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-04 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 84 DailyAlbertFG237 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-05 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 85 DailyAlbertFG238 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-06 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 86 DailyAlbertFG239 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-07 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 87 DailyAlbertFG240 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-08 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 88 DailyAlbertFG241 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-09 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 89 DailyAlbertFG242 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-10 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 90 DailyAlbertFG243 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-11 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 91 DailyAlbertFG244 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-12 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 92 DailyAlbertFG245 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-13 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 93 DailyAlbertFG246 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-14 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 94 DailyAlbertFG247 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-15 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 95 DailyAlbertFG248 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-16 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 96 DailyAlbertFG249 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-17 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 97 DailyAlbertFG250 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-18 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 98 DailyAlbertFG251 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-19 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 99 DailyAlbertFG252 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-20 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 100 DailyAlbertFG253 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-21 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 101 DailyAlbertFG254 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-22 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 102 DailyAlbertFG255 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-23 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 103 DailyAlbertFG256 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-24 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 104 DailyAlbertFG257 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-25 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 105 DailyAlbertFG258 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-26 00:00:00.000 1464320 513.835937 4

    DailyAlbertPF DailyAlbertPS 106 DailyAlbertFG259 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-27 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 107 DailyAlbertFG260 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-28 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 108 DailyAlbertFG261 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-29 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 109 DailyAlbertFG262 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-30 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 110 DailyAlbertFG263 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2015-12-31 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 111 DailyAlbertFG264 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2016-01-01 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 112 DailyAlbertFG265 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2016-01-02 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 113 DailyAlbertFG266 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2016-01-03 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 114 DailyAlbertFG267 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2016-01-04 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 115 DailyAlbertFG268 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2016-01-05 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 116 DailyAlbertFG269 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2016-01-06 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 117 DailyAlbertFG270 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2016-01-07 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 118 DailyAlbertFG271 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2016-01-08 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 119 DailyAlbertFG272 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2016-01-09 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 120 DailyAlbertFG273 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2016-01-10 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 121 DailyAlbertFG274 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2016-01-11 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 122 DailyAlbertFG275 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2016-01-12 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 123 DailyAlbertFG276 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2016-01-13 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 124 DailyAlbertFG277 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2016-01-14 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 125 DailyAlbertFG278 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2016-01-15 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 126 DailyAlbertFG279 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2016-01-16 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 127 DailyAlbertFG280 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2016-01-17 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 128 DailyAlbertFG281 RANGE 128 1 FactAgentAlbertPortalSessionEntries 2016-01-18 00:00:00.000 0 0.000000 4

    DailyAlbertPF DailyAlbertPS 2 DailyAlbertFG30 RANGE 128 1 FactAgentAlbertPortalSessionEntries NULL 0 0.000000 4

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

    https://www.mssqltips.com/sqlservertip/1601/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 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply