Solved

Get table from .bak file in SQL

Posted on 2013-01-08
29
927 Views
Last Modified: 2013-01-10
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!
0
Comment
Question by:Karen Wilson
  • 15
  • 5
  • 5
  • +1
29 Comments
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
What is the full error message you're getting with the failure ?
0
 

Author Comment

by:Karen Wilson
Comment Utility
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.........
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 100 total points
Comment Utility
If the backup you're restoring is not from the same server and db, it almost certainly has different file names and locations that what SQL is expecting, which causes an error.

You really should use a RESTORE DATABASE statement to do restores rather than the GUI.  The gui is flaky and will simply "stall" at times.  Also, with a RESTORE statement, you can see later *exactly* what you did, but not after using the gui.
0
 
LVL 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 100 total points
Comment Utility
Even if you use the GUI to locate the files and provide the basic structue for the RESTORE command, you should select the Script menu option at the top so you see what it is doing and adjust if necessary.
0
 

Author Comment

by:Karen Wilson
Comment Utility
It is stuck and my server admin guy can't find the process to stop it.  Any suggestions??
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Use another SQL session to try to kill the SQL process id.
0
 

Author Comment

by:Karen Wilson
Comment Utility
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??
0
 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
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.
0
 

Author Comment

by:Karen Wilson
Comment Utility
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.
0
 

Author Comment

by:Karen Wilson
Comment Utility
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.
0
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
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;
0
 

Author Comment

by:Karen Wilson
Comment Utility
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...
0
 

Author Comment

by:Karen Wilson
Comment Utility
Wait a minute... I can see one error.
0
 

Author Comment

by:Karen Wilson
Comment Utility
Nope, I keep getting the same error message.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
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

0
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
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.
0
 

Author Comment

by:Karen Wilson
Comment Utility
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?
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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.
0
 

Author Comment

by:Karen Wilson
Comment Utility
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!
0
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
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 ?
0
 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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?
0
 

Author Comment

by:Karen Wilson
Comment Utility
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.
0
 

Author Comment

by:Karen Wilson
Comment Utility
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.
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 300 total points
Comment Utility
You may want to read this Knowledge Base article:

http://support.microsoft.com/kb/304261

By default, SQL Server does not support database files on network drives.

There's a very special set of conditions you need to meet in order to do it and it's all spelled out in that article.
0
 

Author Comment

by:Karen Wilson
Comment Utility
Thanks!  Can Bill make this anymore difficult..........
0
 

Author Comment

by:Karen Wilson
Comment Utility
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
0
 

Author Comment

by:Karen Wilson
Comment Utility
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!
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now