Copy database to LocalDB?

[Amesville]

Hi Folks

I have a SQL Server database that I need to move into a LocalDB for distribution with an application (VB.NET) I've written. Unfortunately I'm no DBA and the half-dozen or so times I've attempted to do this I cocked it up completely.

The DB was originally developed in 2008 R2 Express, I've since backed it up and restored it to 2012 express. It includes a number of stored procedures as well as about 30 tables. I have SQL Server Management Studio 2012 and LocalDB installed on the development machine. So far the only way I've been able to do anything with LocalDB is to create and define things one step at a time through a command line interface.

I have the DB all set up to be distributed, I just need to get it into LocalDB so I can send it out with the App. Can anyone help me figure out how to do this?

Thanks

Craig

 

[SQLBill]

I'm not sure I'm clear on what you are trying to do.

It seems you had a database (you don't say the name of it) on SS2008 R2, you upgraded it to SS2012 and now you basically need it named LocalDB....is that correct? I would make a backup of the database, then just restore it with the new name.

However, if you are trying to merge it with an existing database named LocalDB, I don't know of any easy way to do that.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Amesville]

Hi Bill

Sorry for not being clear. SQL Server 2012 Express offers a new feature called LocalDB, which runs as an executable program and not a service as SQL Server normally does. It can be easily bundled with an application and allow access to a database created in SQL Server without the usual requirements, just needs a slightly different type of connection string. It doesn't have the robustness of a full SQL Server install and I don't know it it supports more than one user, but for my needs it should work perfectly - if I can figure out how to set it up.

Unfortunately part of the reason I'm unclear on this is that I am not sure what to do. My understanding is that LocalDB should be set up in management studio so it contains the named database (in my case called "HereToThere"). So Where you would normally have an instance of SQLEXPRESS with a database inside it called HereToThere, you would have an instance called LocalDB with a database inside it called HereToThere.

I assumed the best way to do this would be to try to backup and restore the database to the new instance, but I have not been able to get that to work and I don't know why. I'm able to backup and restore to any other SQL Server instance. In fact the only way I have been able to interact with the LocalDB instance is by the command line where I can only issue one command at a time.

I am hoping I can find someone who can help me understand how to get my database installed in this LocalDB instance so I can roll out my application. At this point it is all I am waiting for.

Craig

 

[SQLBill]

Unfortunately, I won't be able to help as I don't use SS2012 yet. Good luck and I hope someone here can assist you.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[fredericofonseca]

You don't need to have a localdb instance per se.. you install the localdb software and this can then be used by any application that uses a localdb connection string. for example "Data Source=(localdb)\v11.0;Integrated Security=true;AttachDbFileName=C:\MyData\Database1.mdf"

this may help you - and books online has lots of more info on it.

http://blogs.msdn.com/b/sqlexpress/archive/2011/10/27/net-framework-4-now-supports-localdb.aspx

http://blogs.msdn.com/b/sqlexpress/archive/2011/11/28/using-localdb-in-visual-studio-2010.aspx

http://blogs.msdn.com/b/sqlexpress/archive/2011/07/12/introducing-localdb-a-better-sql-express.aspx

further info

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

From what I can see you need to ship
LocalDB.Msi
database files (.mdf/.ldf)
.NET 4.0.2 or newer - this is a must. Previous versions will not know how to work with LocalDB

and set your connection strings accordingly to where you have the database files located






Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Amesville]

Thank you so much Frederico - I will begin looking over the document you posted right away.