restoring data from a .bak file

Question

hi

I have accidently deleted 240 records from an online sql server database. Although it is not the end of the world (it will take a couple of days to recreate them) I have a .bak file with the lost data.

I cannot see how to extract the missing data from the .bak file. I cannot just restore the whole database because changes have been made since the backup was taken.

I have created a local database thinking I can restore the .bak file into it. Presumably I am then able to extract the required data from the local backup and then load it into the online database.

Your help would be appreciated

thanks

michael

---

michael dean

Answer 1

Restore the backup in a second database. Be careful when you specifiy the physical files. The must be different than the production database. Consider to restore the database on a different machine.

Now you can copy the rows from the second restored database to your primary one. Depending on the size, you should restore the

Answer 2

http://msdn.microsoft.com/en-us/library/ms186858(v=SQL.105).aspx

---

If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI,MCC, PGD SoftDev (Open), MBCS http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood

Answer 3

hi

Restoring the .bak file is not working for me!
The backup file was created by the hosting company and I downloaded it to my local computer.

They also created and downloaded a .bak database of a customers production database. If it is not working for my database (ournaturalplanet) then perhaps the customers backup .bak files are unusable!!!

Thanks for your help so far.

I am consistently getting the following message:

+++++++++++++++++++++++++++++++++++++++++

TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore failed for Server 'MICHAEL_PC\SQLEXPRESS'.  (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1846+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

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

The backup set holds a backup of a database other than the existing 'ournaturalplanet' database.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3154)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=3154&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

---

michael dean

Answer 4

Hi,

Thanks for the error message.

Can you confirm the version of SQL Server where the backup was created on?

Can you also confirm the version of SQL Server where you are trying to Restore the backup to?

You can find out the version of SQL Server by typing the following command; -

SELECT @@VERSION

Since I believe the issue may relate to you trying to restore a backup on a version of SQL Server which is earlier that the version of SQL Server where the backup was created on.

Kind Regards,

Kieran.

---

If you have found any of my posts helpful then please vote them as helpful. Kieran Patrick Wood MCTS BI,MCC, PGD SoftDev (Open), MBCS http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood

Answer 5

Are your aware of data import or export option? These are the option to migrate data between databases. In your case this would be ideal. This will allow you to select whole table or to write  query to manipulate data to and from databases. 

Hope you will find your way out.

Thanks.

---

Mohammad Saidul Karim

Answer 6

thanks for your help with this.

At first I could not get the database restored, receiving the message 

"backup set holds a backup of a database other than the existing 'ournaturalplanet' database"

What i had not realized was that although the database backup I was sent from the hosting company was called ournaturalplanet.bak, the actual database name within this file was something like 'ournaturalplanet - full backup'!!

Never occurred to me that a different name was within the file. Once I knew this I was able to use the help given here to restore the database table.

michael

---

michael dean