We help IT Professionals succeed at work.

RESTORE A BACKUP IN SQL 2012 from SQL 2008

CMChalcraft
CMChalcraft asked
on
249 Views
Last Modified: 2017-03-12
I am upgrading to SQL 2012 and I want to restore a copy of our live system which is currently SQL 2008.

 I have created this script :

use master

go
 

restore database Test_BP_Contracts_Main

-- LOCATION OF BACKUP FILE
--C:\BrittonPrice\SQL Backups

-- COMMAND TO RESTORE
From disk='C:\BrittonPrice\SQL Backups\SQL01-BP_Contracts_Main-20170310.131140.666-FULL.bak' with replace


But when I run it I get this error message :

Msg 3634, Level 16, State 1, Line 3
The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\BP_Contracts_Main.mdf'.
Msg 3156, Level 16, State 8, Line 3
File 'BP_Contracts_Main' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\BP_Contracts_Main.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 3634, Level 16, State 1, Line 3
The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\BP_Contracts_Main_1.ldf'.
Msg 3156, Level 16, State 8, Line 3
File 'BP_Contracts_Main_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\BP_Contracts_Main_1.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 3
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

How can I get this to work?

Regards

Chris
Comment
Watch Question

Nakul VachhrajaniSenior Manager
CERTIFIED EXPERT

Commented:
It looks like you are trying to restore on the same server as the source database is currently active in. Hence, SQL Server is trying to overwrite the files, which it can't because they are in use.

The other option is that the SQL Server service does not have sufficient rights to restore the database at the default locations.

You can do one of two things here:
1. Restore the database on a different location (different folder and specify the path in your restore script)
2. Restore with a different file name

In both these cases, the recommendations from SQL Server (using the WITH MOVE command) are correct.

Below is a sample of what your restore statement should look like. Please review and adjust the logical database file names and the paths before going ahead with the restore.

--Notice I changed file names from "BP_Contracts_..." to "Test_BP_Contracts_..."
--If you want to use a different location, update the path appropriately
RESTORE DATABASE [Test_BP_Contracts_Main]
FROM DISK = 'C:\BrittonPrice\SQL Backups\SQL01-BP_Contracts_Main-20170310.131140.666-FULL.bak'
WITH 
MOVE 'BP_Contracts_Main'
  TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test_BP_Contracts_Main.mdf',
MOVE 'BP_Contracts_Main_log'
  TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestBP_Contracts_Main_1.ldf',
REPLACE;

Open in new window

CMChalcraftFinance Director

Author

Commented:
I realise now that i was not clear enough in my first statement.

Our main system is on SQL 2008.

We want to upgrade to SQL 2012.

What I want to do is restore a copy of our live system which is  SQL 2008 onto my test SQL 2012 system.

I installed SQL 2012 on Friday but have not made any other changes. All I have done so far is to create an empty database in SQL 2012 called Test_BP_Contracts_Main.
Senior Manager
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
I see it more of an permissions issue. SQL Server service is not having enough permissions on the folder where your .mdf and .ldf files are located.

Identify the default data path by seeing data file of master database.

Place your .mdf and .ldf files at that path and try restore again specifying those file paths in your restore statement.
Nakul VachhrajaniSenior Manager
CERTIFIED EXPERT

Commented:
Yes, it does appear to be a permissions issue because the SQL 2012 instance would not have the required permissions on the default paths used by SQL 2008.
CMChalcraftFinance Director

Author

Commented:
Your are totally correct. I did what you suggested and it has worked. Thanks very much.

Regards

Chris C
CMChalcraftFinance Director

Author

Commented:
How do I close this question?
CMChalcraftFinance Director

Author

Commented:
Very helpful and very quick.
Nakul VachhrajaniSenior Manager
CERTIFIED EXPERT

Commented:
Glad I could help. Thank-you for the points and the grade :)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.