Migrating from MDF File to SQL Server 2005?

I created a web site using the "personal web site" starter kit with Visual Studio 2005. It created an ASPNET MDF file as well as a Personal.MDF in my App_Data directory.

I want to migrate this site to a production server that does not have SQL Express, but does have a full version of SQL Server 2005.

How do I migrate both the database format (tables and SPs) as well as the data to the SQL Server?

I can't find anything that looks like Enterprise Manager or Query Analyzer on my system, and installing the "native client tools" doesn't do anything except install configuration utilities (no clients).

Inside of Visual Studio 2005, I can bring up both databases (one from the MDF file(s) and one using a connection string to the server), but there doesn't appear to be any way to copy from one to the other.

There must be an easy way to do this, right?

Many thanks in advance!

Hello cambler,

You can simply do a straight-forward Attach Database function in SQL Server 2005 Management Studio. You can simply copy the MDF and LDF files from your App_Data folder from your PC to your production server running SQL Server 2005. Then, attach the database pointing to the copy of the MDF file. You need to detach the database files first on your development PC before you can copy the MDF and LDF files as the service is making use of these files.

For more information, visit http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

|||

I am trying to utilize the SQL Server Management Studio to open an MDF file, so I can perform tasks such as importing CSV files. I guess that I need to attach to a an MDF file, but I don't see the that option available.

If there is a nother approach, please advise.

Thanks,

|||From SQL Server Management Studio, you can simply right-click on the Databases folder and select Attach. Then point to the location of your MDF file. That should do the trick.sql

Migrating from 2005 to 2005 Mobile

Hello All,

I have a database created on SQL Server 2005 and now I would like to migrate the table structure to a SQL Server 2005 Mobile database. Generating the scripts and running them against the mobile database generates a lot of error that I suppose are caused by not supported features or keywords on the mobile engine.

I would like to know what are the best procedure for doing such migration. Note that this has nothing to do with replicating, since the SQL Server 2005 database will be "discontinued" after the migration.

Thanks in Advance,

Igor Kondrasovas

you have a variety of options

1) you can create a new SQL Mobile database, add a subscription to it that subscribes to a publication which consists of the entire SS2005 database, and then merge replicate

2) you can script the DDL of the SS2005 database and run each statement a line at a time from a simple CF application on device to recreate the skeletal database

I walk you through how to do option 1 in the MSDN webcast I gave in Nov 2005.

http://msreadiness.com/WS_abstract.asp?eid=15003229

Darren

|||

Hello Darren,

Thanks for the help.

I was thinking that there I could just do that using the Management Studio.

I found out a tool called DataPort Wizard that is capable of doing such think, including table data very easily. I hope Microsoft will provide a way for doing that the same way this tool does (very easy by the way).

The other thing is that if you decide to create you own Mobile Database you cannot easily define contrainsts (as foreign keys) using the "Table Creation GUI". Only defining Primary Keys is Allowed. Besides, if you have foreign keys defined you cannot see them on the Colums definition on Management Studio, as you can see on the SQL Server 2005 database. Please correct me if I'm wrong.

Best Regards,

Igor Kondrasovas

|||

You are correct - you cannot do much in terms of defining constraints or altering tables visually in VS2005 or SS2005, but to be honest, the SQL to do this from code (or in SS2005 Mgmt Studio Query Window) is not difficult.

Darren

Migrating cubes from SQL Server Analysis Services 2000 to MS SSAS 2005

Hello

I am fairly new to SQL Server and wanted to know the best way of converting a .CAB cube created in SSAS 2000 to SSAS 2005 and then associate it with a database.

Rgds

Ankur

Backups done in AS2000 cannot be restored in AS2005. Your best course of actions is to restore the .cab backup on AS2000 system, and then migrate it to AS2005 system.

Migrating a database

We have a database running under SQL Server 7 on a Win NT server. We
have recently added Win2K servers, created a new domain and installed
SQL Server 7 on one of the servers. I would like to be able to log into
either domain from a workstation, use Enterprise Manager to access
either database and use the migration tools to copy the database from
the NT server to the 2K server.
We have created a trust relationship that allows me to log into either
domain, but when I try to use Enterprise Manager to create a
registration to the database on the other domain, or try to use the
migration tool to copy the database, I get an error saying that my user
account cannot log into the other system. ( I have the same user name
and password on both domains and domain admin privileges).
Can this be done directly? How?
Do I need "linked servers"? -- It doesn't like my account to create this
either.
Do I need a "remote server"?
Reading the documentation creates more questions than answers. I would
appreciate someone pointing me in the right direction. ThanksWhat is the exact error you are seeing and which domain are you logging
into, the new one or the other one.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||When I'm logged into the NT domain and trying to register SQL Server on the 2K
server, I get "login failed for user 'patenaude'".
When I'm logged into the 2K domain and trying to register SQL server on the NT
server, I get " loged failed for user 'null'. Not associated with a trusted
SQL Server connection."
I get similar messages trying to set up a linked server.
"Rand Boyd [MS]" wrote:
> What is the exact error you are seeing and which domain are you logging
> into, the new one or the other one.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.|||When you are logged into the Windows NT domain are you trying to register
the SQL Server using NT or SQL authentication. The error indicates SQL
authentication. If so does the 'patenaude' SQL login exist? Is the password
correct?
When you are logging into the Win 2K domain, the error indicates that you
cannot be validated on the domain controller. That is where the null is
coming from. Check the security log of the domain controller for this
domain for errors related to this account. Also if you are logged on to the
workstation using a local account you will see this error.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I changed the system on the 2K server to use NT authentication, so now I get
the same error message about my user account not being valid. (The user id is
good, the password is good.) My workstation is a member of the NT domain and
the trust relationship is set up to the point where I can log into either
domain. The user name is the same on both domains. I have tried registering
across the domain from both directions, using the default log-in with NT
authentication, using the specific login with my user name and using the
specific log-in with each of the domains added ( NTdomain\user & 2Kdomain\user)
I get the same results each time. I looked in the event logs on the servers and
there is no mention of the attempt in any of the logs.
"Rand Boyd [MS]" wrote:
> When you are logged into the Windows NT domain are you trying to register
> the SQL Server using NT or SQL authentication. The error indicates SQL
> authentication. If so does the 'patenaude' SQL login exist? Is the password
> correct?
> When you are logging into the Win 2K domain, the error indicates that you
> cannot be validated on the domain controller. That is where the null is
> coming from. Check the security log of the domain controller for this
> domain for errors related to this account. Also if you are logged on to the
> workstation using a local account you will see this error.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.

Migrated from SQL 2k5 Express to SQL Server - Profile doesn't work

I recently created an application that stores 3 variables in the web.config in the "Profile"configuration:

<profile>
<properties>
<add name="Department" defaultValue="" />
<add name="UserName" defaultValue="Anonymous" allowAnonymous="true"/>
<add name="AL" defaultValue="" />
</properties>
</profile>

By doing this my web.config automatically configured itself to create a database in my app_data folder and also filled in all the table rows as necessary. Then I moved the code to my sql server running SQL Server 2005 Standard and found out that it won't generate that database in either the app_data folder or the SQL Server Data directory. I've gotten so desperate to get this database created that I've (temporarily) given "Everyone" full permissions to both directories. I then double checked that my named pipes to that server were enabled, and then also tried moving my database from my working sql express app from a different computer to the server. I still cannot get it to work.

If I have no database in the folders that worked on sql express i get this error:

"SQLExpress database file auto-creation error:
The connection string specifies a local Sql Server Express ..."

If I have a database in the folder it says it can't communicate with it or can't find it.

My web.config shows:

<add name="LocalServer" connectionString="server=SERVERNAME\West;database=ASPNETDB.MDF;uid=UserName;pwd=Password;" PoviderName="System.Data.SqlClient" />

Is there a way to successfully generate this database with sql server? Please Help!!!

I figured the problem out...

For those of you interested, here is the solution. I found on google groups early on when i had this problem, but it didn't work and I never payed attention to the error I got, I just assumed it couldn't work. Then I figured i'd give it another shot. The trick is to make sure you don't include the line breaks in your web.config when replaceing the suggested code. Anyway, below is the link (solution provided by Brad Hammond):

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/5370c080936fda9b/5d39f0c6bf9f45dd?lnk=st&q=%22sql+server+2005%22+profile&rnum=9#5d39f0c6bf9f45dd

My machine.config solution:
<add name="LocalSqlServer" connectionString="data source=.\West;Initial Catalog=ASPNETDB;User ID=UserName;password=Password" />

Thank you Brad, Hope this helps other people out!

Migrate existing standalone to cluster

While away on vacation my company had some software installed that required a
new instance of SQL Server 2K to be created on our existing active/passive
cluster.
Because the techs doing the install did not know the password for our domain
cluster user account they created the standalone instance on the db server.
What I need to know is how do I go about migrating it to a clustered instance
instead of a standalone so as to take advantage of the failover capabilities?
i.e. I already have a clustered server with one clustered instance. They
installed a standalone instance alongside and now I need to migrate the new
instance to another named clustered instance.
Is it possible to go with the existing clustered instance and simply move
the DB's to the cluster? What's driving the need for two instances?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:F9743B37-FC4D-4909-A4CA-608078BB5A3E@.microsoft.com...
While away on vacation my company had some software installed that required
a
new instance of SQL Server 2K to be created on our existing active/passive
cluster.
Because the techs doing the install did not know the password for our domain
cluster user account they created the standalone instance on the db server.
What I need to know is how do I go about migrating it to a clustered
instance
instead of a standalone so as to take advantage of the failover
capabilities?
i.e. I already have a clustered server with one clustered instance. They
installed a standalone instance alongside and now I need to migrate the new
instance to another named clustered instance.
|||Oh you'll love this...
The software we purchased for our new document management system *cough*
Documentum *cough*, is designed so as to require a case-sensitive database.
I know...D-U-M Dum...And because all of our other databases are NOT
case-sensitive (and there's no way in h*ll I would change that) we had to
create a new instance that was.
"Tom Moreau" wrote:

> Is it possible to go with the existing clustered instance and simply move
> the DB's to the cluster? What's driving the need for two instances?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Cary" <Cary@.discussions.microsoft.com> wrote in message
> news:F9743B37-FC4D-4909-A4CA-608078BB5A3E@.microsoft.com...
> While away on vacation my company had some software installed that required
> a
> new instance of SQL Server 2K to be created on our existing active/passive
> cluster.
> Because the techs doing the install did not know the password for our domain
> cluster user account they created the standalone instance on the db server.
> What I need to know is how do I go about migrating it to a clustered
> instance
> instead of a standalone so as to take advantage of the failover
> capabilities?
> i.e. I already have a clustered server with one clustered instance. They
> installed a standalone instance alongside and now I need to migrate the new
> instance to another named clustered instance.
>
|||That's not justification enough. You can create a case-sensitive database
within an instance that is not case sensitive.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:A8F1525E-3D96-4842-AFBD-87160EC34CB8@.microsoft.com...
Oh you'll love this...
The software we purchased for our new document management system *cough*
Documentum *cough*, is designed so as to require a case-sensitive database.
I know...D-U-M Dum...And because all of our other databases are NOT
case-sensitive (and there's no way in h*ll I would change that) we had to
create a new instance that was.
"Tom Moreau" wrote:

> Is it possible to go with the existing clustered instance and simply move
> the DB's to the cluster? What's driving the need for two instances?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Cary" <Cary@.discussions.microsoft.com> wrote in message
> news:F9743B37-FC4D-4909-A4CA-608078BB5A3E@.microsoft.com...
> While away on vacation my company had some software installed that
> required
> a
> new instance of SQL Server 2K to be created on our existing active/passive
> cluster.
> Because the techs doing the install did not know the password for our
> domain
> cluster user account they created the standalone instance on the db
> server.
> What I need to know is how do I go about migrating it to a clustered
> instance
> instead of a standalone so as to take advantage of the failover
> capabilities?
> i.e. I already have a clustered server with one clustered instance. They
> installed a standalone instance alongside and now I need to migrate the
> new
> instance to another named clustered instance.
>
|||Not that it will matter much anyway, since it is already installed, but how
do you go about making a case-sensitive db inside of an insensitive instance?
And I still need to know how to change the standalone to a clustered...
"Tom Moreau" wrote:

> That's not justification enough. You can create a case-sensitive database
> within an instance that is not case sensitive.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Cary" <Cary@.discussions.microsoft.com> wrote in message
> news:A8F1525E-3D96-4842-AFBD-87160EC34CB8@.microsoft.com...
> Oh you'll love this...
> The software we purchased for our new document management system *cough*
> Documentum *cough*, is designed so as to require a case-sensitive database.
> I know...D-U-M Dum...And because all of our other databases are NOT
> case-sensitive (and there's no way in h*ll I would change that) we had to
> create a new instance that was.
> "Tom Moreau" wrote:
>
>
|||To create a database with a case-sensitive collation, just use the COLLATE
option:
create database MyDB
collate Latin1_General_CS_AI
As for the other bit, you'll have to create the new virtual server (and
named instance) in the same manner as you created the other virtual server
and instance. However, you can specify the collation for the new instance
at that time. Once that's done, then backup and restore the DB's from the
standalone to the new instance. Finally, remove the standalone.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:FBFC5A77-4A41-4B6E-B076-370E9111321B@.microsoft.com...
Not that it will matter much anyway, since it is already installed, but how
do you go about making a case-sensitive db inside of an insensitive
instance?
And I still need to know how to change the standalone to a clustered...
"Tom Moreau" wrote:

> That's not justification enough. You can create a case-sensitive database
> within an instance that is not case sensitive.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Cary" <Cary@.discussions.microsoft.com> wrote in message
> news:A8F1525E-3D96-4842-AFBD-87160EC34CB8@.microsoft.com...
> Oh you'll love this...
> The software we purchased for our new document management system *cough*
> Documentum *cough*, is designed so as to require a case-sensitive
> database.
> I know...D-U-M Dum...And because all of our other databases are NOT
> case-sensitive (and there's no way in h*ll I would change that) we had to
> create a new instance that was.
> "Tom Moreau" wrote:
>
>
|||Creating a database in an instance with a collation that is differnt than
the default server collation, as Tom suggested, is certainly one approach
you can explore. If everything is under your control, you can make this
work. A vendor package like Ducumentum, however, may crap out becasue of the
likely collation conflict with the tempdb database. So test it out!
Linchi
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:FBFC5A77-4A41-4B6E-B076-370E9111321B@.microsoft.com...[vbcol=seagreen]
> Not that it will matter much anyway, since it is already installed, but
> how
> do you go about making a case-sensitive db inside of an insensitive
> instance?
> And I still need to know how to change the standalone to a clustered...
>
> "Tom Moreau" wrote:
|||Question regarding what Linchi wrote about the temp DB being an issue. We
are currently in the same situation with a mulitnode cluster that will house
a peoplesoft instance (different collation than the other instances) but have
concerns on how failover will happen with this different collation instance
vs the collation instances that are indentical. Will failover happen just as
if all the collations were the same or do we have to take special precautions?
"Linchi Shea" wrote:

> Creating a database in an instance with a collation that is differnt than
> the default server collation, as Tom suggested, is certainly one approach
> you can explore. If everything is under your control, you can make this
> work. A vendor package like Ducumentum, however, may crap out becasue of the
> likely collation conflict with the tempdb database. So test it out!
> Linchi
> "Cary" <Cary@.discussions.microsoft.com> wrote in message
> news:FBFC5A77-4A41-4B6E-B076-370E9111321B@.microsoft.com...
>
>
|||Multiple collations are no different on a cluster than on a stand-alone box.
Failover will work exactly the same regardless of the system or anyuser
database collation. Collations have been separated from the system code
page since SQL 7.0
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Todd" <Todd@.discussions.microsoft.com> wrote in message
news:32F54FD8-2882-4125-8202-D6CE442C83E5@.microsoft.com...[vbcol=seagreen]
> Question regarding what Linchi wrote about the temp DB being an issue. We
> are currently in the same situation with a mulitnode cluster that will
> house
> a peoplesoft instance (different collation than the other instances) but
> have
> concerns on how failover will happen with this different collation
> instance
> vs the collation instances that are indentical. Will failover happen just
> as
> if all the collations were the same or do we have to take special
> precautions?
> "Linchi Shea" wrote:

Migrate ACCESS to MSSQL

Hi,
I created a table and filled data to it in ACCESS 2002. How can I migrate the ACCESS table data to MSSQL 2000 Server? The table in MSSQL DB has primary key and also has records. I want to append the ACCESS data (2 columns) to the existing table with the s
ame columns names in MSSQL.
How can I do it?
Thanks
Tom,
you can use the upsizing wizard or DTS. Here are some links to help you:
http://builder.com.com/5100-6388-5059624.html
http://support.microsoft.com/default.aspx?kbid=285829
http://support.microsoft.com/default...NoWebContent=1
HTH,
Paul Ibison
|||Most folks prefer using DTS to the upsizing wizard... However if you do use
the upsizing wizard, go back and ensure that the sql column data types are
appropriate... My experience with the wizard is that it makes the columns
sizes too large.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tom" <kerocow@.yahoo.com> wrote in message
news:221ADD36-EA26-45DA-A953-D700866C06F6@.microsoft.com...
> Hi,
> I created a table and filled data to it in ACCESS 2002. How can I migrate
the ACCESS table data to MSSQL 2000 Server? The table in MSSQL DB has
primary key and also has records. I want to append the ACCESS data (2
columns) to the existing table with the same columns names in MSSQL.
> How can I do it?
> Thanks

Migrate ACCESS to MSSQL

Hi
I created a table and filled data to it in ACCESS 2002. How can I migrate the ACCESS table data to MSSQL 2000 Server? The table in MSSQL DB has primary key and also has records. I want to append the ACCESS data (2 columns) to the existing table with the same columns names in MSSQL.
How can I do it
ThanksTom,
you can use the upsizing wizard or DTS. Here are some links to help you:
http://builder.com.com/5100-6388-5059624.html
http://support.microsoft.com/default.aspx?kbid=285829
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q237/9/80.ASP&NoWebContent=1
HTH,
Paul Ibison|||Most folks prefer using DTS to the upsizing wizard... However if you do use
the upsizing wizard, go back and ensure that the sql column data types are
appropriate... My experience with the wizard is that it makes the columns
sizes too large.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tom" <kerocow@.yahoo.com> wrote in message
news:221ADD36-EA26-45DA-A953-D700866C06F6@.microsoft.com...
> Hi,
> I created a table and filled data to it in ACCESS 2002. How can I migrate
the ACCESS table data to MSSQL 2000 Server? The table in MSSQL DB has
primary key and also has records. I want to append the ACCESS data (2
columns) to the existing table with the same columns names in MSSQL.
> How can I do it?
> Thanks

Migrate ACCESS to MSSQL

Hi,
I created a table and filled data to it in ACCESS 2002. How can I migrate th
e ACCESS table data to MSSQL 2000 Server? The table in MSSQL DB has primary
key and also has records. I want to append the ACCESS data (2 columns) to th
e existing table with the s
ame columns names in MSSQL.
How can I do it?
ThanksTom,
you can use the upsizing wizard or DTS. Here are some links to help you:
http://builder.com.com/5100-6388-5059624.html
http://support.microsoft.com/default.aspx?kbid=285829
port/kb/articles/Q237/9/80.ASP&NoWebContent=1" target="_blank">http://support.microsoft.com/defaul...&NoWebContent=1
HTH,
Paul Ibison|||Most folks prefer using DTS to the upsizing wizard... However if you do use
the upsizing wizard, go back and ensure that the sql column data types are
appropriate... My experience with the wizard is that it makes the columns
sizes too large.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tom" <kerocow@.yahoo.com> wrote in message
news:221ADD36-EA26-45DA-A953-D700866C06F6@.microsoft.com...
> Hi,
> I created a table and filled data to it in ACCESS 2002. How can I migrate
the ACCESS table data to MSSQL 2000 Server? The table in MSSQL DB has
primary key and also has records. I want to append the ACCESS data (2
columns) to the existing table with the same columns names in MSSQL.
> How can I do it?
> Thanks

Microsoft SQL Server Management Studio Express Problem

Microsoft SQL Server Management Studio Express

I have restored the database into it with 100tables and stored procedures.

I have also created the new database in it.

But,i don't know how to add the existing database in to Visual Studio.Net 2005 .I m using ASP.Net.

Where are the database stored ??

I don't want to create the new database,.

Can anyone pls tell me ...

Hi,

To add an existing database .mdf file to SQL Server, you can use Attach. Here are the steps.

1. Copy the .mdf file and .ldf(if exists) to another folder.
2. Right click on Databases, and select Attach from the popup menu.
3. In the Attach Databases dialog box, click Add button and select the .mdf file.
4. Click OK to finish, and the database will be attached to your database list in Management Studio.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!