October 4, 2014 at 10:50 pm
Hi
I created a database in sqlserver 2005 . now I wants to attach it in sqlserver 2008. But I would be face to error:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
An exception occurred while executing a Transact-SQL statement or batch.
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'F:\C#\data base\mydb.mdf'. (Microsoft SQL Server, Error: 5123)
Thanks very much
October 4, 2014 at 11:24 pm
armm1388 (10/4/2014)
HiI created a database in sqlserver 2005 . now I wants to attach it in sqlserver 2008. But I would be face to error:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
An exception occurred while executing a Transact-SQL statement or batch.
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'F:\C#\data base\mydb.mdf'. (Microsoft SQL Server, Error: 5123)
Thanks very much
This is a file permission issue, you must give full control (properties-security) to the 2008 service account on the file.
😎
October 5, 2014 at 7:12 am
Open management studio as "Admin" and you'll be able to do so.
Thanks.
October 6, 2014 at 12:14 am
This was removed by the editor as SPAM
October 6, 2014 at 1:47 am
why not just back it up on 2005 and restore it on 2008?
create any sql logins on the 2008 server and relink the orphaned users
and while you're at it upgrade the database compatability level too.
One script with all that in is something I reuse on a daily basis as we migrate application databases from 2005 to 2008.
October 6, 2014 at 2:56 am
When upgrading a database to a newer version, don't forget to execute the following steps:
- change compatibility level to the current level (if no old syntax is used)
- change verification to CHECKSUM (for better registration of PAGE corruption)
- remove all statistics (new version could create different statistics)
- update usage (DBCC UPDATEUSAGE ('dbsDatabaseName') WITH NO_INFOMSGS, COUNT_ROWS)
- enable auto create- and update- statistics
Ans as P Joones already mentioned: a backup/restore is a more fail-safe option comapred to detach/attach. With this last option there are some caveats with permissions if I recall correctly. Using backup/restore you also keep the source database available in case you need to rollback (after or just before the backup you could set the database to READ-ONLY to prevent modification of the data during the migration)
October 6, 2014 at 5:02 am
Please check the following things which each can be the source of your problems:
•Check the exact database names and file paths.
•Disable your Anti Virus Software, this could "lock" your database as well.
•Check if backup software is "locking" de Database.
•Give NTFS security permissions for the MDF and LDF files to "Everyone".
( and on that directory or (mapped) disk letter
•Move the files to a sub directory, root directories can give errors sometimes.
•Rename the database files.
•Try using Windows Authentication instead of SQL Server Authentication or the other way around.
( while logging on to the Management Studio GUI )
•Logon on the server ( and Management Studio under another Administrative account.
•Restart the SQL services ( preferred after working hours )
•Open a new Query and use the "sp_Attach_DB" command to attach the database.
( remember, the GUI ain't always everything )
Each of the above mentioned possible solutions can do the trick. Please try re-attaching the DB after each possible solution.
October 8, 2014 at 12:14 am
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply