SQLTeam.com | Weblogs | Forums

Restore Database with some rows only


#1

Friends,

I am using SQL 2008 and I took backup on 31-07-2015 and rows are keep on falling in that said DB until today 06-08-2015.

Now i want to restore that 31-07-2015 backup but with the values which are fallen from 01-08-2015 to 06-08-2015.

Is there any method to restore?

Please guide me.

Thanks & Regards,


#2

Restore to a new named, temporary, database. Then use INSERT / UPDATE to copy rows from the temporary database back to your main database.

We restore to a database named

    RETORE_MyDatabaseName_yyyymmdd

which, for us, does two things:

  1. Our backup routine does NOT add any database named RESTORE_* to its list of databases-to-back-up (we can manually add it if we must, but I can't remember that we ever have done!)

  2. At some future date we can decide that "RESTORE_xxx_20140101" must be old enough that we can drop it now!! So the housekeeping gets done later, if not sooner.


#3

If I am reading this correctly - then you will need the following to restore successfully:

  1. Your backup dated 2015-07-31
  2. All transaction log backups taken from 2015-07-31 through 2015-08-06 (all day)

Once you have those, restore the backup to a temporary database with NORECOVERY. Restore each transaction log backup in order with NORECOVERY. After all files have been restored - RESTORE with RECOVERY.

Validate the database has the data and no further errors...backup the database - and restore from that new backup over your corrupted database.

If the database is in simple recovery model - or you don't have all transaction log backups - you will not be able to restore to current point in time.

Note: to insure no further data is added to the current database - perform a tail-log backup with NORECOVERY. That will take the current database offline after the log backup has completed and prevent any further data being added.


#4

Thanks for your valuable suggestions.

Guna