Avatar of Karen Wilson
Karen Wilson
Flag for United States of America asked on

Get table from .bak file in SQL

I am trying to get rows from a table in a DB in a .bak file.

I opened a new DB called RestoreDB.
I clicked on Restore.
I clicked on Source: Device and linked by .bak file
I clicked on Destination:Database and chose RestoreDB
I clicked on the file and verify backup media - all is good.
I click OK.
The tail-Log backup of the operation completed successfully.  The restore of the RestoreDB failed.

I'm not sure what I should try next...  any suggestions?

Thanks!
Microsoft SQL Server

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
Steve Wales

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

ASKER
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.........
SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Karen Wilson

ASKER
It is stuck and my server admin guy can't find the process to stop it.  Any suggestions??
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Scott Pletcher

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

ASKER
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??
Chris Luttrell

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Karen Wilson

ASKER
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.
Karen Wilson

ASKER
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.
Steve Wales

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

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;
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Karen Wilson

ASKER
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...
Karen Wilson

ASKER
Wait a minute... I can see one error.
Karen Wilson

ASKER
Nope, I keep getting the same error message.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Luttrell

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;

Open in new window

Steve Wales

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.
Karen Wilson

ASKER
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?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Scott Pletcher

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.
Karen Wilson

ASKER
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!
Steve Wales

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 ?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Luttrell

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.
Scott Pletcher

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?
Karen Wilson

ASKER
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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Karen Wilson

ASKER
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.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Karen Wilson

ASKER
Thanks!  Can Bill make this anymore difficult..........
Karen Wilson

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Karen Wilson

ASKER
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!
Scott Pletcher

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.