This morning i have found the way to detach SharePoint databases from SQL Server 2008 Express Edition and then attach all databases to our new SQL Server 2008 R2 Enterprise edition. But then i found problem on re-attach the databases to SQL Server Express edition. It returns me an error as shown on the image below.
TITLE: Microsoft SQL Server Management Studio
------------------------------Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
Parameter name: nColIndex
Actual value was -1. (Microsoft.SqlServer.GridControl)
------------------------------
BUTTONS:
OK
------------------------------
Then after hours looking for the solution, finally i found the article by googling it, saying that this is one of the SQL Management Studio bugs. So all you need to do is using Transact-SQL to attach the database.
Below is the T-SQL script that i used to re-attach my SharePoint database and worked very well.
EXEC sp_attach_db @dbname = N'Your_SharePoint_DBName',@filename1 = N'[drive]:\DB_Attach_Location\SharePointDB_File.mdf',
@filename2 = N'[drive]:\DB_Attach_Location\SharePointDB_Log.LDF';
Example:
EXEC sp_attach_db @dbname = N'SharePoint_AdminContent_cc5d5fea-c432-4746-930e-cfc2138cffd3',
@filename1 = N'E:\Data\MSSQL10.SHAREPOINT\MSSQL\DATA\SharePoint_AdminContent_cc5d5fea-c432-4746-930e-cfc2138cffd3.mdf',
@filename2 = N'E:\Data\MSSQL10.SHAREPOINT\MSSQL\DATA\SharePoint_AdminContent_cc5d5fea-c432-4746-930e-cfc2138cffd3_log.LDF';
@filename1 = N'E:\Data\MSSQL10.SHAREPOINT\MSSQL\DATA\SharePoint_AdminContent_cc5d5fea-c432-4746-930e-cfc2138cffd3.mdf',
@filename2 = N'E:\Data\MSSQL10.SHAREPOINT\MSSQL\DATA\SharePoint_AdminContent_cc5d5fea-c432-4746-930e-cfc2138cffd3_log.LDF';
Once you run the query you may notice the new database has been attached successfully. Hope it helps.
11 comments:
Thanks!
Had the same problem! Any clue on why this happens?
Reattaching the DB solved all of them.
M
hi Mario, probably the problem was with the security/privileges, because when i attached it by using Domain Admin and added the Domain Admin as dbcreator in the SharePoint instance i had no problem at all.
Try to add your account as dbcreator in SharePoint DB server. Go to Security > Server Roles, add your account in dbcreator group.
Mei.
Thank you so much for the great help!!! Brilliant!!!
Run Management Studio as administrator by right clicking on it's icon in Start Menu and selecting 'Run as administrator'. You need to do this even if you are an administrator on the PC.
Thank you. This was a huge help.
thz to u !!!
it is really helpfull to me ...
:D
Thanks a lot Mei!!
Xavier
Hello Mel, How can you attach the T-SQL script to sharepoint database please
I solved this problem with my SQLEXPRESS. You need run SSMS as Administrator (even if your actualy account is added to administarator group you need use Administarator account directly ) or connect to DB as SA user (on my Pc was disabled). Then you can manage you SQL Server ( Attach option should work now), you can activate SA account or you can add your actual login to sysadmin if you like use it with full access to your SQL Server.
Do Consider this if you are stupid like me :)
Cause: Please note that old versions of SSMS (SQLServer Management Studio) are not forward compatible. Using 2012 SSMS you can connect to SQLServer 2005/2008 databases as it is backward compatible.
It looks like Microsoft will not fix the forward compatiable.
Solution: Upgrade all your client tools to highest version, and then you won't be missing out on any functionality or getting error messages.
lovely find sir...
really very helpful.....
Post a Comment