Attaching a Local SQL Server Database Using sqlcmd

re: Attaching a Local SQL Express Database Using sqlcmd

Tony — Fri, 19 Dec 2008 03:55:37 GMT

Beth,

Please why not use sql server?

Attaching a Local SQL Express Database Using sqlcmd

No1 Microsoft Fan — Fri, 19 Dec 2008 04:00:14 GMT

More goodness from Beth Massi: I've been getting asked a lot lately about how to easily attach a local

re: Attaching a Local SQL Express Database Using sqlcmd

Beth Massi — Fri, 19 Dec 2008 04:18:02 GMT

Hi Tony,

What do you mean exactly? MDF files are SQL Server database files. If you are referring to using SQLCmd instead of SSMS then it's because SQLCmd is easier when you don't have the log file (LDF) available.

Cheers,

-B

re: Attaching a Local SQL Express Database Using sqlcmd

Tony — Fri, 19 Dec 2008 15:04:19 GMT

Beth I mean sql express, in the real working (telecommunications) is not installed in my company. Only sql server is installed on our dev servers.

I think you should target more real world examples.

re: Attaching a Local SQL Server Database Using sqlcmd

Beth Massi — Fri, 19 Dec 2008 18:07:01 GMT

Hi Tony,

Umm... that's what this post is all about. SQL Express and SQL Server work the same way when you want to attach database files. SQL Express allows you to have user connections and work with local database files. This post shows you how to take all the samples where I distribute the databases, and instead attach them to your instances -- whether that is sqlexpress or server (I mention "developer edition or higher" in the second paragraph). Sorry I wasn't clear, I updated the wording in the post.

And SQL Express is very real world. It's a great FREE database for small-medium businesses. And upgrading is just a matter of attaching the database to SQL Server like I showed above.

HTH,

-B

re: Attaching a Local SQL Server Database Using sqlcmd

monkey — Fri, 19 Dec 2008 19:42:02 GMT

I only find it necessary to move my express db files on Windows Millenium Edition 2008 (aka Vista), otherwise I only use express in website development and attach them in place.

The security and deployment defects of Vista are my #1 complaint about this half baked wad of recursive dough eating dough.

re: Attaching a Local SQL Server Database Using sqlcmd

Beth Massi — Fri, 19 Dec 2008 20:09:00 GMT

Hi Monkey,

I'm running as admin on Vista and user instances connect just fine. If you're doing web development then there are some issues when the ASPNET user is auto-attaching to the user instance. If ASPNET does not have write permissions on the database files, the connection fails. (Also see this post for a specific Vista issue with an easy fix: http://blogs.msdn.com/joestagner/archive/2008/03/12/sql-express-failed-generate-a-user-instance.aspx)

However I think attached databases are easier to work with when developing. For attached databases I wrote about how to enable remote connections on Vista here:

http://blogs.msdn.com/bethmassi/archive/2008/09/17/enabling-remote-sql-express-2008-network-connections-on-vista.aspx

I do find Vista to be a little more pesky when developing on it because of it's security model but in general it's been fine with respect to using Visual Studio 2008 and SQL Express.

Cheers,

-B

re: Attaching a Local SQL Server Database Using sqlcmd

monkey — Fri, 19 Dec 2008 21:45:39 GMT

My gripe about Windows Millenium Edition 2008: The Sequel has to do with the deployment of application databases and the broken linkage mechanism. This isn't a problem for Cassini website development, but if you try to deploy a windows solution that uses 32 & 64 bit versions of SQL Express, Vista will fail to locate & link your database properly.

It is better the be the balmer than the balmy, apparently the most when they are handing out pay; hence the world economy blue screen.

re: Attaching a Local SQL Server Database Using sqlcmd

Beth Massi — Fri, 19 Dec 2008 21:58:37 GMT

Thanks for the feedback Monkey. Though I'm not sure what problem you're running into so it's hard for me to suggest a solution. It sounds like IIS security related to the ASPNET user in which there is a weird fix that requires you to delete the SQLEXPRESS temporary directory.

Do you have a link handy that explains the issue?

TIA,

-B

re: Attaching a Local SQL Server Database Using sqlcmd

monkay — Fri, 19 Dec 2008 22:43:41 GMT

Do you know how to change the database name setting in the mdf? Case: copying & renaming a database file to be attached with a new name. This was not previously a problem, either there was no signature or the attach event updated the reference, but now it throws an error.

re: Attaching a Local SQL Server Database Using sqlcmd

Walter — Fri, 27 Feb 2009 15:49:29 GMT

Hi Beth - love the videos.

I'm trying to attach the OMS database and I get the error:

"File activation failure. The physical file name "C:\Users\bethma\Documents\Visual Studio 2005\Projects\FormsOverData\OrderManager2\OrderManager\OMS_log.ldf" may be incorrect.

The log cannot be rebuilt when the primary file is read-only.

Msg 1813, Level 16m State 2, Server...

Could not open new database 'BethMassi1OMS'. Create Database is aborted."

I checked the MDF file and it is not read only.

Can you help me with this?

re: Attaching a Local SQL Server Database Using sqlcmd

ahmad@isoftwarehouse.net — Fri, 27 Feb 2009 17:29:39 GMT

hallo ....

how add automatic attach database, to sqlexpress in setupdeployment in vb.net

re: Attaching a Local SQL Server Database Using sqlcmd

Beth Massi — Fri, 27 Feb 2009 20:48:51 GMT

Hi Walter,

You'll need to make sure the name of the dataabse is OMS.mdf for those samples when you attach it using sqlcmd.

HTH,

-B

re: Attaching a Local SQL Server Database Using sqlcmd

Bill — Wed, 11 Mar 2009 22:00:35 GMT

Thanks Beth.

Just what I needed.

Bill

re: Attaching a Local SQL Server Database Using sqlcmd

Abraham zilva — Tue, 07 Apr 2009 23:59:52 GMT

how can i attach a file.mdf file from sqlexpress back to visual studio2008. (i attached file.mdf to sql express from visual studio to add the schemas needed for user rolls, but now i can't get it back to visual studio)

re: Attaching a Local SQL Server Database Using sqlcmd

Haroon Shaikh — Thu, 30 Apr 2009 19:53:41 GMT

can you please tell me the attaching and detaching of mdf files by sqlcmd in sqlexpress using a batch files

re: Attaching a Local SQL Server Database Using sqlcmd

Paul — Thu, 28 May 2009 15:45:51 GMT

Beth,

I got this after doing the last "GO"

Msg 948, Level 20, State 1, Server BUG-FUJITSU\SQLEXPRESS, Line 1

The database 'OMS' cannot be opened because it is version 655. This server suppo

rts version 612 and earlier. A downgrade path is not supported.

Msg 1813, Level 16, State 2, Server BUG-FUJITSU\SQLEXPRESS, Line 1

Could not open new database 'OMS'. CREATE DATABASE is aborted.

Please help.

Thank you.

re: Attaching a Local SQL Server Database Using sqlcmd

Beth Massi — Tue, 02 Jun 2009 18:27:01 GMT

Hi Paul,

This message means you are trying to attach a SQL 2008 database to a SQL 2005 instance. The version of the OMS database you have must be the 2008 version. You can install SQL 2008 Express for free or you can download the 2005 version of the OMS database from this 2005 sample here:

http://code.msdn.microsoft.com/Project/Download/FileDownload.aspx?ProjectName=vbvideosdata&DownloadId=69

HTH,

-B

re: Attaching a Local SQL Server Database Using sqlcmd

Pedro — Thu, 29 Oct 2009 17:08:04 GMT

Hello Beth, first of all let me congrat you on your videos... are making my learning a lot less painful...

Now my question is...

I made a SQL database connection on my application... Now I want to deploy it and make it a multi-user dababase... I want to have jsut one instance of the database where all people connect... How can I configure the connection string to match the target computer ? I managed to get SQL to be shared by TCP/IP ... how can i make it install and i dont know pop up a connection menu...that will configure all objects in the database..

Thanks, Pedro

re: Attaching a Local SQL Server Database Using sqlcmd

Beth Massi — Wed, 04 Nov 2009 21:05:18 GMT

Hi Pedro,

Once you have the database running on the remote machine you change the connection string in your application to point to the remote database. Make sure you store connections strings in your application Settings (app.config) file and then you can change it without having to recompile the application. If you're using the designers to model your database then they will create a connection sting in the app.config.

For more information :

http://msdn.microsoft.com/en-us/library/a65txexh.aspx

HTH,

-B

re: Attaching a Local SQL Server Database Using sqlcmd

Georges — Wed, 09 Dec 2009 13:06:14 GMT

Beth , please i need to install sql express and attach the mdf file to it automatically without installing management studio i know you can help me its very urgent . thank you a lottttttt in advance

re: Attaching a Local SQL Server Database Using sqlcmd

Beth Massi — Wed, 09 Dec 2009 16:50:33 GMT

Hi Georges,

I'm not sure why you can't use the sqlcmd above. More info is here:

http://msdn.microsoft.com/en-us/library/ms165673.aspx

If you have a single-user app, you may also want to look into user instances, which will auto attach when your application runs:

http://msdn.microsoft.com/en-us/library/ms254504.aspx

HTH,

-B

re: Attaching a Local SQL Server Database Using sqlcmd

georgenaffah — Fri, 11 Dec 2009 13:51:20 GMT

Dear beth everything is working fine but still one issue , when you install sql server management studio , before it launches it shows you the connect to server dialog box , from where you can see the server name example :george/sqlexpress , how can i get the name of the computer automatically , if i want to install sqlexpress and attach a database to it automatically and on a different computer . Please Helpppp thank you in advance for your support

re: Attaching a Local SQL Server Database Using sqlcmd

georgenaffah — Fri, 11 Dec 2009 14:20:17 GMT

Beth I have created a batch file with the following script :

sqlcmd -S .\SQLEXPRESS -george -i sqlscript\createattach.sql

pause

that runs the createattach sql script that is :

USE [master]

CREATE DATABASE [Data2] ON

( FILENAME = N'C:\Program Files\week Publishers\Sader Civil Cassation\Cassation Data\Data\Data2.mdf' ),

( FILENAME = N'C:\Program Files\week Publishers\Sader Civil Cassation\Cassation Data\Data\Data2_log.ldf' )

FOR ATTACH

if exists (select name from master.sys.databases sd where name = N'Data2' and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )

USE [master]

CREATE LOGIN [sadd] WITH PASSWORD=N'1p@ssw0rd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

EXEC master..sp_addsrvrolemember @loginame = N'sader', @rolename = N'sysadmin'

USE [master]

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

what shall i do after ? help

re: Attaching a Local SQL Server Database Using sqlcmd

Beth Massi — Fri, 11 Dec 2009 17:09:18 GMT

Hi georgenaffah,

As I mention in the article above:

"You can use the .\ (dot-backslash) syntax to indicate the local server default instance"

Regarding your script, I don't know what you're trying to do afterwards. You should check out the reference I mention above: http://msdn.microsoft.com/en-us/library/ms165673.aspx

re: Attaching a Local SQL Server Database Using sqlcmd

georgenaffah — Sat, 12 Dec 2009 06:58:33 GMT

Dear Beth

all what i want to do is to install sql server express 2005 on a new machine then use the script to attach the database automatically , help me please locate where to write the .\ in order to create the server instance automatically . thank you in advance for all your support .