CMChalcraft
asked on
RESTORE A BACKUP IN SQL 2012 from SQL 2008
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-2017 0310.13114 0.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::Validat eTargetFor Creation' on 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER \MSSQL\DAT A\BP_Contr acts_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\DAT A\BP_Contr acts_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::Validat eTargetFor Creation' on 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER \MSSQL\DAT A\BP_Contr acts_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\DAT A\BP_Contr acts_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
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
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::Validat
Msg 3156, Level 16, State 8, Line 3
File 'BP_Contracts_Main' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER
Msg 3634, Level 16, State 1, Line 3
The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::Validat
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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
ASKER
Your are totally correct. I did what you suggested and it has worked. Thanks very much.
Regards
Chris C
Regards
Chris C
ASKER
How do I close this question?
ASKER
Very helpful and very quick.
Glad I could help. Thank-you for the points and the grade :)
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.
Open in new window