Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

Restore fails (2008.bak to 2012.mdf)

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
Mike Eghtebas
Asked:
Mike Eghtebas
  • 12
  • 7
  • 5
  • +1
2 Solutions
 
HuaMinChenBusiness AnalystCommented:
Try to copy the .bak file into this

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

instead.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
> 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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
also, does this folder exist:
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Those files are in the directory provided by the RESTORE FILELISTONLY command: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 12
  • 7
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now