Rules

Secret ingredients to quality software

Edit
Info

Sample - Do you avoid dropping a user's database when you attempt to create a database?

Created on 27 Nov 2012 | Last updated by Rebecca Liu on 19 Nov 2014 07:16 AM (over 6 years ago)

If you have an SQL script that runs as part of your install you should always make sure that it does not drop the database first. When you typically auto-generate a script file from some of the SQL applications (such as Enterprise Manager) it will automatically attempt to drop a database if it already exists. This is bad practice as a company may already have a large investment in the data already in the database and dropping it may cause them to lose this investment.

If you know which machine the database is going to be installed from within your application you should first check that it doesn't already exist and prompt the user accordingly to let them know that they should first manually delete the database. For example the install of the SQL Reporting Services setup handles this problem in an appropriate and simple manner (although some additional help could be provided).

Good example – an application should never automatically delete a database, not even a sample database

If you cannot be sure of the machine that the database is going to be installed on then you should make use of third party .sql script execution managers such as SSW SQL Deploy to ensure that when you attempt creation of databases where the database already exists then things will run smoothly.


We open source. This page is on GitHub