I recently had a problem which required a full factory reset on my desktop. Unfortunately, the one thing I forgot to back up was my 1000 record SQL Database (which is stored on a separate drive). Now, after re-installing SQL Express on my newly CLEANED pc I cannot reattach my database to the new server. If I had backed it up prior to the reset, then all would need to do is restore it into the new server but, now I do not have that option. My question is, how do I (or can I) attach the database to the new server? Or (horrors thinking about it) am I destined to rebuilding the entire database?
What error message do you get when you try to attach the database? Do you have both the data file(s) and transaction log file(s)? These typically have a .MDF and .LDF file extension.
What method are you using to attach the files?
robert_volk, I get the following error message when I try to attach the database using the Attach command under Databases. Also, both the .mdf and .log files are present
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'D:\Data Files 092015\DVD_List.mdf'. (Microsoft SQL Server, Error: 5123)
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-5123-database-engine-error
I checked my permissions and am included in the database and log file.
Previously when I moved the database to a new computer, I created a backup and then imported it into the new system. As I stated in the original thread n the confusion, I forgot to create a backup file
This is an access denied error - the reason for that is the service account running SQL Server doesn't have permissions to the location where the mdf/ldf files are located.
If you did not specify an account and did not change the options, then the default accounts would be the NT SERVICE accounts created for that instance. Find those - and grant them permissions and you should then be able to attach that database.
2 Likes
jeffw8713, thank yoi for the response, that's all I needed. Attached without any problems. Good to know, but hopefully will not need it again.
Glad it was a simple fix - thank you for the update.