Restoring SQL Server 2012 database to a different location and database name

I'm having difficulty trying to restore a SQL Server 2012 database to a new location and name. This used to work fine in SQL Server 2008 but no longer works and it leaves my original database in a (restoring...) state.

The reason I'm trying to do this is so I can have the exact same database as my client for comparative purposes. I restore my own database to another name and then restore my client's database over my original. I'm trying to do this using SQL Server 2012 Management Studio.

Thanks!!!
pabrannPresidentAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why are you choosing to overwrite the database? Isn't to restore into a new database?
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Is there any error that gets logged in the SQL Server log (and/or the Windows event viewer)?

Also, what is the process that you are using to execute the restore? If you can share the command/UI steps that would be nice.
0
 
pabrannPresidentAuthor Commented:
The ErrorGeneral areaFiles areaOptions area
Hopefully you will see what I did. Select the backup file from hard disk.(SQLOrderSys_PB_20150512.bak) file from database (SQLOrdersys)

Select the destination database (SQLOrderSys_C)

Click on "Files" object and choose the destination folder (new folder)
Change the file names by concatenating a "_C" to the mdf and ldf to the "restore as" file

Click on "Options" object and check "Overwrite existing database"

Click "OK" and watch the fail
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Anthony PerkinsCommented:
This has nothing to do with SQL Server 2012 vs 2008. You have a permission problem.  You need to make sure that the SQL Server service startup account has the appropriate permissions in the  e:\Applications\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA_2 folder.
0
 
pabrannPresidentAuthor Commented:
How do I do that?? I was able to successfully do this a few times a couple months ago.. was something changed in a Windows Update or SQL Server update?
0
 
Anthony PerkinsCommented:
Check the account used for SQL Server service.
Check the security tab for the Windows folder.
Make sure the account has the appropriate permissions in that folder.
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
I agree. It's a permissions problem. The solution by Anthony should do the trick.
0
 
pabrannPresidentAuthor Commented:
Full-Control.JPG   I checked the hard drive location where the files are being stored and it appears that I have "full control"/
0
 
Anthony PerkinsCommented:
 I checked the hard drive location where the files are being stored and it appears that I have "full control"/
The key question you are overlooking is "Check the account used for SQL Server service", does it have full control of that folder?
0
 
pabrannPresidentAuthor Commented:
Works fine now, thank you very mucy.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.