[RESOLVED] Deploy database

[tylerm]

I just finished beta testing my new application which uses a SQL Server 2000/5 database. I now need to create an installer to deploy the database itself so administrators can add it to their server.

How do I do this?

[d3gerald]

with regards to sql server, i think you need to create a new deployment setup for it to install it in its server

[RobDog888]

For a sql server 2000 db you have a couple of options.

You can create a empty backup of all the tables and views etc. Then have the dba restore your db to their server.

You can have the dba add a blank empty db to their sql server and you can execute some sql scripts to generate your db objects.

For sql server 2005 I'm not sure if it has a deployment utility but if it doesnt then it will be like for 2000.

And of course you will need the appropriate permissions to be granted on the db objects too.

[szlamany]

See this post - from a sticky at the top of the DB forum here...

http://www.vbforums.com/showpost.php...28&postcount=5

It shows how to attach to a MS SQL 2005 .MDF with a connection string that makes it as easy as opening an ACCESS database (as long as SSX is already installed!)...

[RobDog888]

Thats easier but it also states that you get "most" of the abilities which leaves me wondering what is left out when you simply attach the db using htis method vs installing the db from within EM.

[szlamany]

I would imagine that most of what you lose is the "server" tracking the DB.

Since you never attach - there is no MASTER DB entries regarding the DB - so backup and whatever else might be logged in the MASTER DB never know about your DB.

I'm guessing the run-time experience is basically the same.

I'll look further into this and post back.

[RobDog888]

In that case you would probably also loose any security permissions too as there is no users assigned to the db?

[szlamany]

I was always unclear on how the security transfers.

When I grab a DB using BACKUP from one of my customer machines and put it on my laptop I can still see all the logins from that domain. I see WINDOWS GROUPS and off-domain logins - all under the USERS tab in EM for that DB.

When I go to the SECURITY>LOGINS tab on my laptop SQL instance I do not see those logins listed - so they are really in the DB and not in the SERVER at that moment...

[RobDog888]

Yes, if you backed up your db with security, users, and groups, but then the SID links will be broiken if its out of the domain and you will have issues.