Restoring a dropped table

Question

Post reply

Restoring a dropped table

Viewing 15 posts - 16 through 30 (of 34 total)

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

hammad772001 SSC Enthusiast Points: 170 More actions · Answer 1

Thanks Guys, I finally got the solution 🙂 I'm adding it in case anyone else is looking to do the same.

1. I took a Full Database Backup.

2. I created a table and added some rows in it.

3. I stopped the SQL Server Service.

4. I manually deleted the MDF File belonging to my Database.

5. I started the SQL Server service again.

6. The Database failed to open as expected.

7. I executed the following command in sequence.

BACKUP LOG CMS_TEST TO DISK = 'D:\Backups\trylogtail.trn' WITH NO_TRUNCATE

RESTORE DATABASE CMS_TEST FROM DISK = 'D:\Backups\try.bak' WITH NORECOVERY

RESTORE LOG CMS_TEST FROM DISK = 'D:\Backups\trylogtail.trn' WITH RECOVERY

8. Now my Database was back online. I checked the table which I created after taking the backup. It was there. 🙂

Thanks to all of you for your comments and support.

Regards,

Hammad

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 2

Glad you figured it out. Thanks for posting the solution details.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

hammad772001 SSC Enthusiast Points: 170 More actions · Answer 3

One more thing I want to ask about - in Oracle we can safeguard the safety of archive log files by specifying more than one location for archive logs. In that case identical copies of archive log files are placed in each location, and if one location is missing, the archive logs are available from other locations. Can the same be done for Transaction Log files in SQL Server, like creating and maintaining identical copies of Transaction Log files? Or if we add another file group and add a log file in it, can it be used to replace the primary log file in case it is missing?

Hope my question is clear.

Regards,

Hammad

Jorge Segarra SSCrazy Points: 2571 More actions · Answer 4

You can stripe backups in SQL Server by adding additional files:

However if you try to do a restore without all of the files from the backup set you'll get this:

Alternatively you can perform a backup with Copy Only option, which allows you to take a backup of the database without breaking the existing backup chain.

=============================================================
/* Backups are worthless, Restores are priceless */

Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Not the logs themselves (log backups can be mirrored)

If you have two log files, SQL uses them sequentially. There's little-no reason to have multiple log files in SQL.

For the log backups you can mirror them.

BACKUP LOG { database_name | @database_name_var }

TO <backup_device> [ ,...n ]

MIRROR TO <backup_device> [ ,...n ]

That puts an entire copy of the backup files in each location, so if one is damaged the other can be used.

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

hammad772001 SSC Enthusiast Points: 170 More actions · Answer 6

Thanks,

So it means that we can only mirror the backup copies of the Transaction Logs, but not the LDF files themselves. So in case MDF and LDF is missing (assuming due to a disk crash), log backups were taken 1 hour before the crash, then we can only retrieve the data until the last log backup by restoring the database backup and applying the last log backup on it. All the transactions after the last log backup are lost.

Please correct me if I'm wrong.

Regards,

Hammad

Mike McQuillan SSCertifiable Points: 6000 More actions · Answer 7

Hammad

Another way to answer your original question is to use database snapshots, which makes the process a bit easier. A snapshot is a read-only copy of the database at the point in time the snapshot was taken.

If you take a snapshot of the database each day at 4pm, for example, you would not need to restore the backup, logs etc. You could simply copy the table data out of the snapshot and back into the principal database.

Of course, this does mean you can only recover up to the time the snapshot was taken, you cannot apply logs to further update the table. The best method to use depends on how much data you are willing to risk/lose.

Mike.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

mike.mcquillan (6/17/2011)
Of course, this does mean you can only recover up to the time the snapshot was taken, you cannot apply logs to further update the table. The best method to use depends on how much data you are willing to risk/lose.
Mike.

Mike

A snapshot is updated as the database is updated, so if you suffer a disk failure, you can recover from the snapshot right up to the point of failure. What it won't help you with is where data has been corrupted or accidently modified, since such changes are also propagated to the snapshot.

John

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

John Mitchell-245523 (6/17/2011)
A snapshot is updated as the database is updated, so if you suffer a disk failure, you can recover from the snapshot right up to the point of failure.

If you have a snapshot and the source database becomes unavailable, the snapshot immediately becomes unavailable too. It's great for recovering accidentally dropped tables or changed data, useless for recovering from missing files.

Also, the snapshot is a point-in-time view of the DB as of the time that the snapshot was created (as Mike indicated), so if the snapshot was created at 10am and a table dropped ar 2pm, the snapshot will show the table as it was at 10am, not just before it was dropped.

You're describing how database mirroring works, not database snapshots.

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

Mike McQuillan SSCertifiable Points: 6000 More actions · Answer 10

Mike McQuillan

SSCertifiable

Points: 6000

June 17, 2011 at 3:10 am

#1340246

Hi guys

You are right - my bad. Sorry everybody!

Mike.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

mike.mcquillan (6/17/2011)
You are right - my bad. Sorry everybody!

Don't apologies, you're right.

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

Mike McQuillan SSCertifiable Points: 6000 More actions · Answer 12

Cheers Gail, I realise that on reading the responses properly.

That's the last time I scan something and respond whilst I'm on a conference call...

Mike.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 13

Yes, sorry - I got a bit confused. Not with mirroring, but the way that a snapshot actually works. Modified pages are copied to the snapshot, but before they're modified, not after, so the snapshot (as its name suggests) can be used to go back to the point in time at which it was taken.

John

hammad772001 SSC Enthusiast Points: 170 More actions · Answer 14

I guess snapshot in SQL Server is more like undo data in oracle which can be used to revert a table to a point in time (flashback). I will try this and share findings.

Regards,

Hammad

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 15

Not really. It's a point-in-time, read-only view of a database.

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