Monday, February 13, 2012

Attaching an .mdf database to SQL server 2005

Hi

I need your help in attaching an .mdf database (Created in SQL Server 2005 in another computer) to SQL server 2005. I tried the original way (right click>attache), but all I get is the following:

TITLE: Microsoft SQL Server Management Studio

----------

Attach database failed for Server 'QC0045'. (Microsoft.SqlServer.Smo)

For help, click:http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

----------

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

----------

Could not find row in sysindexes for database ID 7, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.

Could not open new database 'PurchasingDB'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)

For help, click:http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476

----------

BUTTONS:

OK

I have searched the internet for days trying to fix this problem. I don't know what's the problem exactly!!

What is worse is that the links above, which are provided by Microsoft, don't work!!

Can you please reply ASAP?

Try this

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\BulkCopy.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient"

|||

Hi again,

Thanks for replying. I'm a beginner and I don't know were to place this line!

can you please tell me??

|||

Place you .mdf file in Root folder and use this connection string.

You can add this connection string in web.config

<configuration>

<connectionStrings>
<clear />

<add name="BulkCopy"
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\BulkCopy.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>

</configuration>

You can access connection string like this

ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings["PubsData"];
DbConnection connection = new SqlConnection(pubs.ConnectionString);

|||

I have created a script of the database on the old machine and used it on the new one.

what I got was:

------------------------------------------------

Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 92
Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]

Valid values of database compatibility level are 60, 65, 70, or 80.

------------------------------------------------

and I searched the net to know that this machine is using SQL Sever 2000 "It was previosly downloaded"
http://sqljunkies.com/Forums/ShowPost.aspx?PostID=11505

What I need is SQL Server 2005 SP2, right?? and shall I download it or upgrade the one I have?

----------------

My product info.
Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

|||

Look at the following link

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2033218&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=583331&SiteID=1

http://searchdatamanagement.techtarget.com/originalContent/0,289142,sid91_gci1155060,00.html

|||

Thanks very much

The firs link is exactly what I'm looking for.

but cant find out how to connect to 2005

can you pplease tell me how to do so?

Thanks again

|||

Take a look...

http://www.sqlmag.com/Article/ArticleID/49643/sql_server_49643.html

Hope this help!!!

|||

Nothing.

I'm going to uninstall everything and install only the SQL server 2005 again.

I anybody faced the same problem I recoment the this link as a solution
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2033218&SiteID=1

Thanks anyway

|||

After installing SQL Server 2005 again. attaching database is working fine. :)

Thanks again for the help

No comments:

Post a Comment