stupid stupid stupid restore problem

  • What's the date of the most recent FULL ?? 5 months ago ?

    To turn on transaction logging, so that you capture changes from now going forward, you need to set the database to FULL RECOVERY. This won't help your current problem, but will help in the future.

    Then backup the transaction logs regularly .... say every hour. You also need to schedule regular FULL backups .... every night, or if the DB is huge and you don't have time, then every weekend, and run DIFFERENTIALS every night.

    Go to Books Online (BOL) and read up on RECOVERY MODEL, and backup & recovery topics, disaster recovery etc...

  • I just saw Gail's post, I guess that's why I can't get this to work >>>

    --this needs to run in 2 steps

    --step1

    GO

    CREATE DATABASE TestRecov_SSC

    GO

    ALTER DATABASE TestRecov_SSC SET RECOVERY FULL WITH NO_WAIT

    GO

    ALTER DATABASE TestRecov_SSC SET RECOVERY FULL

    GO

    USE TestRecov_SSC

    GO

    SELECT * INTO Data FROM master.sys.syscolumns

    GO

    SELECT COUNT(*) FROM Data

    --11268

    GO

    BACKUP DATABASE [TestRecov_SSC] TO DISK = N'C:\TestRecov.bak' WITH NOFORMAT, INIT,

    NAME = N'TestRecov_SSC-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    WAITFOR DELAY '00:00:05'

    GO

    DELETE FROM Data WHERE name like '%e%'

    --8428 rows deleted

    GO

    SELECT GETDATE() AS PointInTimeRestore

    GO

    SELECT COUNT(*) FROM Data

    --2840

    GO

    --step 2 change the stop at value to your current run time and execute (sorry no time to make a temp table and dynamic sql)

    USE master

    GO

    RESTORE DATABASE [TestRecov_SSC] FROM DISK = N'C:\TestRecov.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

    GO

    USE TestRecov_SSC

    GO

    SELECT COUNT(*) FROM Data

    --11268 back in business 5 seconds ago

    GO

    USE master

    GO

    BACKUP LOG [TestRecov_SSC] TO DISK = N'C:\Test_Recov_Logs' WITH NOFORMAT, NORECOVERY, INIT,

    NAME = N'TestRecov_SSC-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    USE master

    GO

    --restore at 2009-02-16 10:50:14.720

    RESTORE DATABASE [TestRecov_SSC] FROM DISK = N'C:\TestRecov.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

    GO

    RESTORE LOG [TestRecov_SSC] FROM DISK = N'C:\Test_Recov_Logs' WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'2009-02-16T10:58:56'

    GO

    USE TestRecov_SSC

    GO

    SELECT COUNT(*) FROM Data

    --still 11268 .... expected 2840

    --cleanup

    USE master

    GO

    DROP DATABASE TestRecov_SSC

  • Ninja's_RGR'us (2/16/2009)


    I just saw Gail's post, I guess that's why I can't get this to work >>>

    You need to backup the log before the restore, not after. After a restore there's nothing much left in the log as it's replaced during the restore process.

    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
  • Ya, I figured as much once I saw what happened with the log restore.

    I know it didn't work, but I could really explain it to someone.

    Thanks for filling the blanks Gail.

    So it's back to you're screwed I guess.

    I just hope it's not an unforgivable error.

  • Just a shot in the dark.... if you have any kind of system level backup you can get your .mdf and .ldf files from...... you could then just detach/attach.

    Tim White

  • 2 Tim 3:16 (2/17/2009)


    Just a shot in the dark.... if you have any kind of system level backup you can get your .mdf and .ldf files from...... you could then just detach/attach.

    Wouldn't the databases have to be detached prior to a filesystem backup being able to access the .mdf/.ldf in order to back them up?

    ie, similarly to how you'd have to detach a database in order to "free" the mdf so you could take a copy of it

    ------------------------------------------------------------------------
    Bite-sized fiction (with added teeth) [/url]

  • I'm trying not to get caught up in the shoulda, woulda, coulda's but only thinking about any and all possibilities to get the data back.

    Maybe there is a copy of the .mfd and .ldf files on a tape he didn't know about. Stranger things have happened.

    as stated - "shot in the dark".

    Tim White

  • I wasn't disagreeing with you, I was curious 🙂

    ------------------------------------------------------------------------
    Bite-sized fiction (with added teeth) [/url]

  • Hi,

    if they were backing up the file system with any kind of an open file agent

    AND

    if there were no (or nearly none) transactions written during the backup

    you might get back a more or less corrupt database.

    After that there are several scenarios to recover, depending on the actual state of the db...

    karl

    Best regards
    karl

  • Magy

    sounds like the best advice is check what machine backup software you currently use. Check if there is an open file agent, if so trawl through your recent tapes looking for any traces of an MDF and LDF. You may get lucky

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Hello,

    I followed this with interest, what was the outcome, did you manage to find a backup somewhere?

    Regards,

    D.

  • No, I never did find a backup. It wasn't too terrible because the system was only a development system. But it definitely taught me a lesson!

  • Magy (2/24/2009)


    No, I never did find a backup. It wasn't too terrible because the system was only a development system. But it definitely taught me a lesson!

    Follow this mantra:

    Backups, Early Backups, Frequent Backups, Verified Backups

    😛

  • Also add one more thing...

    Before any delete or Bulk Update, or DDL statements, Take a Back up...

    -Roy

Viewing 14 posts - 31 through 43 (of 43 total)

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