Solved

Restore fails (2008.bak to 2012.mdf)

Posted on 2015-02-02
25
293 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
ID: 40585490
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]
ID: 40585598
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
ID: 40585627
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]
ID: 40585631
> 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
ID: 40585640
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
ID: 40585648
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
ID: 40585651
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
ID: 40585654
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]
ID: 40585658
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 46

Expert Comment

by:Vitor Montalvão
ID: 40585673
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
ID: 40585727
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 46

Expert Comment

by:Vitor Montalvão
ID: 40585744
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 142

Expert Comment

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

Author Comment

by:Mike Eghtebas
ID: 40585760
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 46

Accepted Solution

by:
Vitor Montalvão earned 300 total points
ID: 40585771
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
ID: 40585785
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
ID: 40585794
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 46

Expert Comment

by:Vitor Montalvão
ID: 40585797
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
ID: 40585804
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 46

Expert Comment

by:Vitor Montalvão
ID: 40585814
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
ID: 40585824
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 46

Expert Comment

by:Vitor Montalvão
ID: 40585864
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
ID: 40585897
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 46

Expert Comment

by:Vitor Montalvão
ID: 40585900
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
ID: 40587083
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

947 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

19 Experts available now in Live!

Get 1:1 Help Now