Transfering objects between database files (.mdf) and SQL Databases

Hello all,

Hope I explain this correctly. i'm developing a ASP.NET web app for a client.
This web app, once it's uploaded to webserver, will use a SQL 2005 DB for its data, membership, roles, etc.

But in the meantime I have a separate site which I upload the client's site to for approval by that client before posting to production. For local development on my laptop I use database files (.mdf) in the App_data folder and upload that to my client approval site.

My question is this: once I'm ready to upload the web app and data to my production server I'll want to transfer all the objects (tables, queries, stored procedures, etc.) from the database file (.mdf) to my SQL 2005 DB. Sounds like a simple SSIS package to transfer the objects will from the database file to the SQL 2005 DB would work great. But when using SSIS, it only lets me connect to servers to retrieve objects from databases and not database files. I'm hoping there's a way to be able to do this cause I'd like to be able to build web apps that use database files and transfer the objects to production servers when I'm ready. If anyone could assist with how I would go about transferring objects from database files (.mdf) to SQL DBs and vice versa that would be great!

Thanks,

Strick

If you're using SQL Server 2005 Express, then the local .mdf file is a SQL Server 2005 database. You can just copy the file (along with the .ldf file) to the SQL Server and attach it using SQL Server Management Studio.

That's the thing. I have no intent on using the database file in production. The production server is already establish so copying the entire file isn't an option. I just need to be able to run a ssis package to transfer all the objects from the database file to the production database. Problem I'm having just involves creating a connection to the mdf file. SSIS makes u select a server when using SQL Server or SQL Express.

Thanks,

Strick

Why not attach the .mdf file to your local SQL Server instance. Then you can use SSIS to transfer it. Detach it after use.

Hi,

I can certainly do that locally on my laptop but my client approval site is on a server that I don't have control of. I don't want to pay extra to get access to a SQL Server when all my clients are doing is viewing and saying whether they like or not. That is why I'm using SQL database files. I can upload my clients web site up to my approval site using the SQL database files in app_data folder and not have to pay for usage of a SQL Server. But after I get the ok from my client, I need to transfer the objects in the SQL database file to the Production Server where I do have SQL 2005.

Thanks,

Strick

Quote:

---

Originally Posted by stricknyn

I need to transfer the objects in the SQL database file to the Production Server where I do have SQL 2005.

---

Do you want to overwrite the database (schema and data) on the production server, or do you just want to apply differences in the schema?

We use a package of tools called DBGhost by a company called Innovartis (http://www.dbghost.com/.
We use it to build and package SQL scripts into an executable file that we send to our clients. The file is generated by using DBGhost Packager Plus. This tool compares the SQL scripts with the production server and applies only the differences.
If you don't want to spend $1995, you can always go for the cheaper version, DB Ghost Packager ($295), which adds the SQL scripts to an executable, but this tools does compare with existing databases, it just overwrites both schema and data.
Or, if you have full control of the schema at every client, you can always use DB Ghost Schema Compare to generate diff scripts which has to be applied to the production server manually.

My advice is to spend a few dollars buying a well tested tool, instead of trying to make something yourself. You'll most likely end up with using a lot more money in wasted hours when trying to make everything yourself.

Hope this helps.

I'll be overwriting the (schema and data). While I understand in some cases spending the money on a vested tool makes sense in some cases, I think this is something ssis should be able to do (transfer objects from database file to server). And it really would only take like 20 min to build a package to transfer the objects. It's just that ssis doesn't give you the option to connect to a database file to transfer objects.

Thanks for all your help!

Strick