Solved

Restore fails (2008.bak to 2012.mdf)

Posted on 2015-02-02
25
274 Views
Last Modified: 2015-02-03
I had a .bak file created by SQl Server 2008. Now, in attempt to restore and use this database in 2012 is failing.

Question: Could you please help me to do this?

FYI, I started SMSS as admin. Is it possible to store a file from another folder, for example public folder, instead od .../Backup folder?

RestorFailed
0
Comment
Question by:Mike Eghtebas
  • 12
  • 7
  • 5
  • +1
25 Comments
 
LVL 10

Expert Comment

by:HuaMinChen
Comment Utility
Try to copy the .bak file into this

C:\Program Files\Microsoft SQL Server\MSSQL11.SS2012\MSSQL\Backup

instead.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
the error indicates that it could not replace the database file, this is not about the .bak file.
it means the db is still "in use", so you need to ensure connections are dropped and that you are using with replace OR with move, depending on what exactly you need to do.
if you try to create a copy of the db, you need to specify "with move" (in the GUI you need to specify different file locations for the .mdf and .ldf files), otherwise you would ask sql server to overwrite the original db's files, which obviously it will refuse
0
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
a3,

In the instance I am trying to restore, this database doesn't exist. So therefore, there is no connection to be dropped.
Here the question should be do I need to have a copy of the database in the instance before I attempt to restore a .bak file to it? Or attempt to restore a .bak file will generate the database if it didn’t exist?
Also, I have another question at: http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28608845.html
The problem discussed there could also contribute to the complication here. Both of these databases are installed on the same laptop.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
> do I need to have a copy of the database in the instance before I attempt to restore a .bak file to it?
no
0
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
That is a big relief because I do not have copy of it. So, I guess the resolution to my other question possibly is the key. Do you want to see my screenshots to guide me through?
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
Comment Utility
I don't think the other issue is related to this one.
this one shows clearly that you connected to the instance, trying to restore a db, which fails because the file IS still used by some process (maybe another sql instance still running?)

you may want to check with Process Monitor tool https://technet.microsoft.com/en-us/sysinternals/bb896645
which process is holding the lock on that file
0
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
I will reboot the computer and try it again.

FYI, the .mdf file in question doesn't exist, therefore, open connection could not the problem but restart might help there.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
Here is the screenshots. See the last image on page 2. The external access I referred to had to do with file stream.

The rest of the pages show all my other settings if it will make it easier to trouble shoot this issue.
SQLServer2012Standart-Toshiba-1-EE.docx
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
I think I remember something around this.
if you restore a DB that has FILESTREAM defined, you have to have your instance/db to have EXACLTY the same folders defined for the new server in order for the restore to work...
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
What the following command returns?
RESTORE FILELISTONLY 
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012STD\MSSQL\Backup\ROD_July18.bak'

Open in new window

0
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
Vitor,

After restarting my computer, I ran your code.

/*
RESTORE FILELISTONLY
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012STD\MSSQL\Backup\ROD_July18.bak'

Produced these messages:
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012STD\MSSQL\Backup\ROD_July18.bak'. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
*/

I changed the path to the following (datbases/properties/root directory gave the following path to me:)
RESTORE FILELISTONLY
FROM DISK ='c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS12\MSSQL\Backup\ROD_July18.bak'

This showed me listing of two files. Then I proceeded to restore from this location but I got the following error:
 ====================================================
TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore of database 'ROD_July18' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

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

System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Dashboard.mdf'. (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&LinkId=20476

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

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

=============================================================
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
I took the path from your screenshot but lucky that you could change it for the correct one.
You didn't post here the result of that command.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
also, does this folder exist:
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA
0
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
Here is the screenshot after running the code:Restore
Yes, the file exists in both places. More explanation on why both places shortly.

opps, not both places, brb
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 300 total points
Comment Utility
Ok, you are trying to restore the files to an existing place and the error access is denied is because the files are in use by the SQL Server 2008 R2 instance.
You need to restore the database and move the files to a new location. You need to adapt the following code to do that:
RESTORE DATABASE ROD_July18
 FROM DISK ='c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS12\MSSQL\Backup\ROD_July18.bak'
WITH MOVE 'Dashboard_Dat' TO 'NewLocation\Dashboard.mdf',
MOVE 'Dashboard_Log' TO 'NewLocation\Dashboard.ldf'

Open in new window

0
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
For instance MSSQLSERVER2012STD (Standard version) the .bak file although in the file browser it doesn't show in restore interface (see attached image).

BTW, by mistake I am connected to MSSQLSERVER2012STD attaching from SQLEXPRESS12. I will reconnect to SQLEXPRESS12 to try Vitor's new solution.
Restore2.png
0
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
The backup was created by:  SQL Server 2008 R2
Now, I want to restore it to: SQL Server 2012 express or standard (whichever works).
My SQL Server 2008 R2 is not functioning at this point (http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28608845.html)

Your script produced:
Msg 5105, Level 16, State 2, Line 1
A file activation error occurred. The physical file name 'NewLocation\Dashboard.mdf' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Msg 3156, Level 16, State 3, Line 1
File 'Dashboard_dat' cannot be restored to 'NewLocation\Dashboard.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5105, Level 16, State 2, Line 1
A file activation error occurred. The physical file name 'NewLocation\Dashboard.ldf' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Msg 3156, Level 16, State 3, Line 1
File 'Dashboard_log' cannot be restored to 'NewLocation\Dashboard.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.

Open in new window

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
As I told above  "You need to adapt the following code to do that". NewLocation need to be replaced by a path defined by you (EXAMPLE: C:\whatever\whatever2\....).
0
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
I am searching for Dashboard_Log and Dashboard_Dat in my computer. I do not have them.  All I have is the .bak file. If I am not misunderstood Guy Hengel, just using this .bak file, we could generate the database.

This post has been revised.

That said, I can get one month older version of these two files from a friend if necessary.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Those files are in the directory provided by the RESTORE FILELISTONLY command: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA
0
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
Yes, I located them:
NewLocation
Now executing the revised code gives me some messages:
RESTORE DATABASE ROD_July18
 FROM DISK ='c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS12\MSSQL\Backup\ROD_July18.bak'
WITH MOVE 'Dashboard_Dat' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Dashboard.mdf',
MOVE 'Dashboard_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Dashboard.ldf'

mwsages:
Msg 3634, Level 16, State 1, Line 1
The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Dashboard.mdf'.
Msg 3156, Level 16, State 8, Line 1
File 'Dashboard_dat' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Dashboard.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 3634, Level 16, State 1, Line 1
The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Dashboard.ldf'.
Msg 3156, Level 16, State 8, Line 1
File 'Dashboard_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Dashboard.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.

Open in new window


I will try to copy them to public folder and try it again.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
No, you can't move them to the same place otherwise you wouldn't need the MOVE option. You need to move those files to a new folder.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
Vitor,

Thank you for the grate help although have not quite there yet. It is past 2:3 am and I am not able to think clearly. My last try was:

RESTORE DATABASE ROD_July18
 FROM DISK ='c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS12\MSSQL\Backup\ROD_July18.bak'
WITH MOVE 'Dashboard_dat' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Dashboard.mdf',
MOVE 'Dashboard_dat' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Dashboard.ldf'

message:
Msg 4353, Level 16, State 1, Line 1
Conflicting file relocations have been specified for file 'Dashboard_dat'. Only a single WITH MOVE clause should be specified for any logical file name.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Open in new window

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
I can see you need to sleep :)
Second MOVE should be 'Dashboard_LOG' and not 'Dashboard_DAT' . Don't forget to give a new path. The one you have now is already in use by the SQL Server 2008R2 database.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
Hi Vitor,

with a little detour, I copied Dashboard.mdf and Dashboard.ldf into my SQLEXPRESS12 and attached them. This will allow me to see what I have in them. Probably they have newer data. Just to be in safe side.

Later on I will continue with the restoring from  ROD_July18.bak if necessary.

Thanks,

Mike
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Backup 24 69
t-sql money data type decimal places 4 25
t-sql splitting name column 5 22
BULK INSERT most recent CSV 19 17
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

771 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

9 Experts available now in Live!

Get 1:1 Help Now