Get table from .bak file in SQL

What is the full error message you're getting with the failure ?

It didn't say.  Looks like it got stuck on the upload of restore and is now just sitting there with a green up arrow and (Restoring...) next to it.  I think big brother changed some permissions on me.  Let me check that out before we continue.........

It is stuck and my server admin guy can't find the process to stop it.  Any suggestions??

Use another SQL session to try to kill the SQL process id.

I was able to stop it with RESTORE DATABASE [DBname] WITH RECOVERY.  I'm validating my data right now!!  Scary to say the least.

I think the problem was my rights were removed from the directory that holds the .mdf file and the .bak files.  I had the server admin add me back on.  

I should use code versus the GUI to do this process then and avoid a stall??

It was not the process that was stuck, it was the database left in a "Restoring" state. The stall was because the RESTORE did not complete with the RECOVERY option.  If it was attempting to restore from multiple files, which it would have been doing since it took a tail-log backup, it would have done all but the last files with the NORECOVERY option.  Thus when it failed to read any more files it failed and never set the database back to a state that could be used.

Should I try this process again and if so, do I need to add something for the recovery?

I opened a new DB called RestoreDB.
I clicked on Restore.
I clicked on Source: Device and linked by .bak file (which is from another DB called Deliverables)
I clicked on Destination:Database and chose RestoreDB
I clicked on the file and verify backup media - all is good.
I click OK.

Attempting again this morning....  no luck.

Is it because I am trying to restore the .bak file to a different database than the one that created it?

All I want to do is get a table from the .bak file and retrieve some rows.

Instead of using the GUI, try using a script.

You will need to know the logical file names from your source database.

Let's say my logical file names are prd_data and prd_log, my source database is called prd and the destination database is called dev.

Then do something like this (replacing drives and directories and file names to match what you have on your server:

RESTORE DATABASE [dev] 
FROM  DISK = N'L:\Backup\Full\prd\prd_backup_201301230000.bak' 
WITH  FILE = 1,  
MOVE N'prd_data' TO N'G:\MSSQL\Data\dev_data.mdf',  
MOVE N'prd_log' TO N'G:\MSSQL\Data\dev_Log.ldf',  
NOUNLOAD,  STATS = 10
GO

Select all Open in new window

Run that and if you still have problems, come back with the exact error messages.

If dev exists, or the files already exist, you will need to put dev into single user mode first:

alter database dev set single_user with rollback immediate;

RESTORE DATABASE [RESTORE]
FROM  DISK = N'\\wstf-apps\wstf-dbs\Environmental\Backups\ECOPurchaseOrders\ECOPurchaseOrders_backup_201212232300.bak'
WITH  FILE = 1,  
MOVE N'Deliverables' TO N'\\wstf-fs01\data\wstf-all\EnvrPub\envrinterfaces\Restore.mdf',  
MOVE N'Deliverables_log' TO N'\\wstf-fs01\data\wstf-all\EnvrPub\envrinterfaces\Restore_log.mdf',  
NOUNLOAD,  STATS = 10
GO

Results -
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'RESTORE' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Not sure what to do next...

Wait a minute... I can see one error.

Nope, I keep getting the same error message.

One, your log file should be an ldf file not an mdf file.
From looking at your .bak file name is the database name not ECOPurchaseOrders?  But you are trying to move Deliverables, is the the table name you are after or the database name?
You have to restore the entire database not just a single table.  There are 3rd party tools like those from Red-Gate that would let you compare against and restore from a .bak file, but there are not native commands to do that.
I use the following code to make a copy of my ProdDB from a .bak file moving it to the existing database named TestDB.  I can then look at or copy data from tables in the TestDB.

ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE [TestDB] FROM  DISK = N'C:\Temp\ProdDB_backup_2013_01_01_203001_8747913.bak' WITH  FILE = 1,  
MOVE N'ProdDB' TO N'M:\Sql_Data\TestDB1.mdf',  
MOVE N'ProdDB_log' TO N'L:\Sql_TLogs\TestDB1_1.ldf',  
NOUNLOAD,  REPLACE,  STATS = 10

ALTER DATABASE [TestDB] MODIFY FILE (NAME=N'ProdDB', NEWNAME=N'TestDB')

ALTER DATABASE [TestDB] MODIFY FILE (NAME=N'ProdDB_log', NEWNAME=N'TestDB_log')


ALTER DATABASE TestDB SET MULTI_USER;

Select all Open in new window

OK, it would appear you need to add the REPLACE option to the restore.

Change NOUNLOAD,  STATS = 10
to NOUNLOAD,  REPLACE, STATS = 10

Be aware of what the REPLACE option does and be very careful with the file names you specify in your restore so that you're not overwriting anything on the target server.

See also:
http://msdn.microsoft.com/en-us/library/ms186858.aspx (the REPLACE Option Impact section)
http://blog.sqlauthority.com/2007/09/27/sql-server-fix-error-3154-the-backup-set-holds-a-backup-of-a-database-other-than-the-existing-database/ (Lots other help forums keep pointing back to this blog entry for this particular problem.

I was playing around with another file ECOPR to see if the problem was the deliverable.bak or not and then copied it over wrong.  

I want to restore a table called tblTaskPlans.  Do I need to put that somewhere?

Deliverables is the name of the DB that contains the table tblTaskPlans

I've got the trial copy from Redgate running right now but my server isn't on my local machine so it's not working.  Do you think this is the problem?

Be very careful before using REPLACE, as you can overwrite an existing db.

If you are restoring to a new/non-existing db name, you shouldn't need REPLACE.

I haven't clicked go on anything since the error.  

That's what I was thinking too...  I'm not replacing anything so why would I need replace.

On redgate, I tried using one of my backup files from the "local" server and I get the same ending where the file can not be accessed for restore.  

Bill Gates needs to make this easier!

You have said that you attempted several times to restore this database.

Does a database of the name you're trying to restore exist on your target server ?

I'm not replacing anything so why would I need replace

but you said in your list of steps:

I opened a new DB called RestoreDB.
I clicked on Restore.

To me, that sounds like you created the DB you are trying to restore to first.
I agree to be very careful if restoring to an existing DB and you can just remove the REPLACE option to be safe if you are not.
And about your trouble with Red-Gate tool, yes it cannot read a file on your local machine to restore or compare on a remote server.  Neither can the Microsoft tools.

We have RedGate Backup as well, and I restore from all different locations, including local dirs and UNC paths.  However, I use their extended procedure for Backups and/or Restores, not the GUI.  Apparently the RedGate GUI has some directory restrictions?

I am back.  

Scott - I found out about Redgate's restrictions with the GUI.  I got it to work ever so perfectly but only for my local server.  Ugh.   Which product has the extended procedure for Backups and/or Restores because I work on several locations as well.  

I think I found the error of my ways with the replace/restore thing.  I did create a DB called Restore on my server prior to running the script and after working with the Redgate product, I don't do that and hence throwing the error on the script.  I also had/have permission problems so I'm still working that issue with the server overlord.

I will work on this and let you know the results.

I have opened the world to these files and now I get this message:

Msg 5110, Level 16, State 2, Line 1
The file "\\wstf-apps\wstf-dbs\Environmental\NDCBackUp\Restore.mdf" is on a network path that is not supported for database files.
Msg 3156, Level 16, State 3, Line 1
File 'Deliverables' cannot be restored to '\\wstf-apps\wstf-dbs\Environmental\NDCBackUp\Restore.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5110, Level 16, State 2, Line 1
The file "\\wstf-apps\wstf-dbs\Environmental\NDCBackUp\Restore_log.ldf" is on a network path that is not supported for database files.
Msg 3156, Level 16, State 3, Line 1
File 'Deliverables_log' cannot be restored to '\\wstf-apps\wstf-dbs\Environmental\NDCBackUp\Restore_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Thanks!  Can Bill make this anymore difficult..........

Okay, I went to my remote computer and I get this error.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'MOVE'.

Here is the code:
RESTORE DATABASE [RESTORE]
FROM  DISK = N'\\wstf-apps\wstf-dbs\Environmental\Backups\Deliverables\Deliverables_backup_201301072300.bak'
WITH  FILE = 1,  
MOVE N'Deliverables' TO N'\\wstf-apps\wstf-dbs\Environmental\NDCBackUp\Restore.mdf',  
MOVE N'Deliverables_log' TO N'\\wstf-apps\wstf-dbs\Environmental\NDCBackUp\Restore_log.ldf',  
NOUNLOAD,  STATS = 10
GO

Thanks to everyone for their help.  James at Red Gate also helped a lot by instructing me on how to "open" the folders for SQL to access and using the Red-Gate program, which is totally cool.  But alas, it didn't work over a network.  Back to Square 1

I connected to my remote computer which is attached to the local server and was able to use the Restore GUI on SQL 2012 to do this procedure.  You cannot restore from over a network unless you do a lot of voo-doo.

I had made the mistake of creating the new DB prior to performing this procedure.  Once I deleted it, things flowed.  I thought I had to create one because the dropdown on the GUI didn't contain the destination DB.  But you can just type it in and not use the dropdown.

It still does not properly recover the old database so I have to kill the restore process by opening a new query and typing in RESTORE DATABASE [Type in the original DB name] WITH RECOVERY and run it.  

Bill Gates and team need to make this less crazy!  Thanks again!

Actually SQL Server has a very straightforward process.

Try restoring in Oracle: it takes a 3 day class just to get started!  And that's w/o a forward recovery, which is also a snap to do in SQL Server.