SQL Server - Where can I find information on developing (not designing) a database?

Asked By Patrick Skelton on 13-Aug-13 01:01 PM
Does anyone know where to find good information on developing an MSSQL database from scratch.  I am not looking for information on the design of the database (Normal Forms etc.), but more on the nuts and bolts of how you actually do it.  For example, it is fairly easy to create a database in Visual Studio, but how do I then get this database onto my remote web hosting server?  How do I ensure I can repeat this process if need be?  Can I create a new database with the same schema as an existing one?  Is there an easy way to set up a database to use the security built into .NET?  And a million other questions.

I seem to find information on designing databases everywhere, but I can't find anything good on how you actually work with them on a day-to-day basis.  What are the best practices?  I am a one-man-band, starting work on a project that uses an MSSQL database, and while the database won't be doing anything heavyweight initially, I don't want to get myself into deep water.  I am keen to do things 'right'.

Robbe Morris replied to Patrick Skelton on 13-Aug-13 01:00 PM
SQL Server databases can be backed up and restored to and from .bak files (default file extension).  They can also be attached and detached (MDF data files and LDF log files).  I have an "old" vbscript that works with DTS (older versions of SQL Server) that have pieces that are helpful:

http://www.nullskull.com/articles/20030923.asp
http://www.nullskull.com/a/161/sql-server-dmo--back-up-files-with-vbscript.aspx

In .NET, SQL Server can be managed pretty effectively with the SQL Management Objects via the Microsoft.SqlServer.Management.Smo namespace.  This article has a section towards the bottom that demonstrates how to run sql scripts without the headaches and restrictions that come with using SqlConnection and SqlCommand objects to execute the SQL.

http://www.nullskull.com/a/833/net-setup-deployment--msi-cassini-sql-server-ntfs.aspx

What a lot of guys do is create an empty shell of the database in their production environment.  Set up the appropriate SQL users (if applicable).  Then, they can do a backup/restore or detach/attach (whichever they prefer) and upload files to the hosting company and restore or attach as needed.  Some of this depends on what your hosting company permits.  Of course, you can only do this prior to your first production release.  From there you'll either have to use the hosting company tools to manage your scheme or run ALTER TABLE scripts for schema changes.

This old article still comes in handy for generating a script of all stored procedures that can be created in DEV and executed in production.

http://www.nullskull.com/articles/20030609.asp
Patrick Skelton replied to Robbe Morris on 13-Aug-13 01:15 PM
Lots to get my teeth into there.  Very helpful.  Thank you.