Sql-server – Associate BAK file with SQL Server database

backuprecoveryrestoresql serversql-server-2012

One of our users deleted a lot of data from one of our database applications, and I need to find a way to restore it. I have a bak file and full recovery mode, but there is a catch.

This bak file is not recognized as belonging to this database, so I am not given the option of a timeline restore. I can restore the bak as is, but it does not contain all of the data I need. My only other option seems to be somewhat expensive (~$2000) recovery tools which can recover the data from the transaction logs.

Is there a way to convince SQL Server that this bak file really does belong to this database?

Do I have any other options?

Best Answer

This bak file is not recognized as belonging to this database

It may just be that you haven't performed a tail log backup on the target database. But you should restore the backup and all the log files to a new database first to determine if it has the data you need. Then you can either proceed with the restore, or manually copy the data from the new database.

It was my assumption that I could use the LDF in conjunction with a BAK to reconstitute the database at any given point. Does it not work that way?

No. The LDF will be overwritten in a restore. You must move the transaction log records from the LDF to a log backup first. That's why RESTORE over an existing database will fail if you haven't taken a Tail Log backup: otherwise you would lose data.

So before you start the restore sequence, ensure you've taken a Log Backup of the database. This will move all the log records currently in the .LDF file into the log backup file and make them available for use in a point-in-time restore.

Related Solutions

Sql-server – SQL Server backup, attach to BAK file

First there's no way using the native tools to mount a backup file and read the data in it. That said, what you can do is write your version information into the description of the backup using the DESCRIPTION keyword. Then read this information using the RESTORE DATABASE WITH HEADERONLY to get the information and make sure that they have the correct version.

Sql-server – SQL Recover from .bak file with NOINIT

That's because by not specifying the FILE parameter of RESTORE DATABASE, it is defaulting to 1 which will be the 1st backup set on that media. See the section on this page of Specifying a Backup Set.

Instead of:

restore database YourDb
from disk = 'C:\yourpath\backupfile.bak';
go

You will want to do:

restore database YourDb
from disk = 'C:\yourpath\backupfile.bak'
with file = <n>;
go

Where N is the backup set number in the media. N can be obtained from running RESTORE HEADERONLY on the media. The column you will be concerned with is Position to use with the FILE option.

Best Answer

Related Solutions

Sql-server – SQL Server backup, attach to BAK file

Sql-server – SQL Recover from .bak file with NOINIT

Related Question