Select Page

Microsoft SQL Server Database Tips

You are here:
Estimated reading time: 5 min

Automatic Database Backups, Database Logins, Design SQL For Performance

Where to download SQL server management studio 2012 and 2008 for free from Microsoft?

Microsoft sql server 2012 management studio client tools (Express) can be downloaded for free from this link and comes in a separate exe as part of the download

Microsoft sql server 2008 management studio client tools (Express) can be downloaded for free from

You should read the prequisites on this link:

Tips on Scripting MS SQL 2005 Databases

In the MS SQL Management  Studio Tool, you can right click on a database and select the option to Generate Scripts.  To generate a script for a database select that database and make sure all the options for scripting indexes, primary keys, etc are all set to true.  As you go through this scripting wizard, select all tables, stored procs, views, schemas, etc.  Finish off generating the script by selecting the option to generate it to a new window in the MS SQL Management  Studio Tool.  This way you can view it and then copy and paste and save it to your local machine in some folder to use later.

To execute the above script, first create a new database in MS SQL Management  Studio Tool if you are on your desk top or log into your Sozo Hosting control panel account and create a new MS SQL database there.  Once you have created an empty database, run the SQL you created in the scripting step above. This will create the tables and stored procedures you selected but not the data. Use the section below to create a SQL data script to run against this new database to fill (import) all the tables with data. For Microsoft help on database scripting, click on this link:

How To Download and Run the Data Publishing Wizard for MS SQL 2005

To script data for a database on your desktop, run the Data Publishing Wizard and select the option to publish data only.  This will create a script file of SQL on your local machine that you can use to import into a new database.

To execute this data SQL script, use the MS SQL Management Studio  Tool, point to an empty database such as one you created in the above section, then execute the SQL data import script to fill all the tables with data from your script. Please click on this link if you do not have the Data Publishing Wizard for SQL Server 2005:   For more help on the Data Publishing Wizard, click on this link:

How To Perform Database Back Ups With Maintenance Plans for MS SQL 2005 and 2008

Using SQL Studio management tool you can set up a maintenance plan to back up all your databases nightly (or whatever schedule you want) onto another drive (preferable one on another server).

We recommend you select the option to do a FULL backup on ALL databases since you could add new databases later and this maintenance plan you set up will start backing up the new databases. Select the option to back up each database in a different folder. When you select the maintenance plan folder in the SQL Studio management tool, select the Maintenance Wizard to set up the automatic database back up plan and set a schedule on how often backups recur. The schedule will fire as long as you have SQL Server agent running. SQL Studio management tool will show you if SQL agent is running if you look on the menus on the lower left. If it is not running, then go to your control panel, then open up services for that server, and scroll down till you see SQL Server Agent. Right click on SQL Server Agent and set it to automatic and start it.

Backing up databases is one thing but you should back up your web apps also which you would have a different process for. For critical apps make sure your back ups go off site.

How To Set Up Database Logins and Security with MS SQL 2005 and 2008

You can set up a different login ids and passwords for each person that will be accessing a database on your system. Set the password with upper and lower characters and at least 1 number. Then in the customer database, add the login id with the same login id that that you added in the security section. Here is the steps the SQL Studio management tool (this tool you can download free from microsoft):

1) Add the new database

2) Click on the security folder at the bottom left of your menu, the folder that is NOT inside a database folder. Right click on the Security/Login folder and click on the NewLogin option. Add the new login id . After selecting the SQL Server Authentication radio box option and entering a unqiue password, usually you only need the enforce password policy check box checked. Point the default database to the new database you just added in step 1 and select the correct language. Click ok.

3) Open up the Security/Login folder INSIDE the new database you just added and right click on user folder and select the Add User menu option. Add the same user id in step 2. For the roles for a web access type client, normally you check all the check all the role check boxes EXCEPT these 3: db_backupoperator, and the 2 db_deny… check boxes. Click Ok and now you are done.

Tip For Writing SQL To Perform For Scalability

You should be using SQL Studio management tools such as a profiler and the query tool to analyze the Execution Plan Statistics of your SQL queries so start with the big queries first. If you are using Microsoft SQL Server Management Studio, run a query in the query window, then click on the “QUERY” menu at the top, and select the submenu “DISPAY ESTIMATED EXECUTION PLAN” option. This will bring up a new execution plan tab for you to look at all your SQL queries to make sure they perform. Look not only at the subtree costs but also for table scans. (Table scans are bad for performance). Correct the SQL with new table designs or new indexes and run the same steps above to optimze. Best to use the ACTUAL EXECUTION PLAN when you are finally done.

Here is a link to learn more on Execution Plans:

How TO Shrink the Log File For SQL Server 2000 and 2005 Databases

Please click on this link: and scroll down on that page for tips since the process is different than shrinking the log file for SQL Server 2008 databases as described below.

How To Shrink the Log File for SQL Server 2008 Databases

All application owners (you and your team) are responsible for clearing your database log file so you dont max it out. To solve this issue, you could run this SQL below manually or you could add the proc below to your code in your admin login screen of your web application so it runs the SQL and clears the log file when ever you login.  The better solution for dedicated server customers(scheduling a stored procedure uses the SQL agent) is to schedule the proc below to automatically run once a day or once a week.


— Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE your_database_name


— Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (2, 1); — here 2 is the file ID for transaction log file,you
can also mention the log file name (dbname_log)

— Reset the database recovery model.

ALTER DATABASE your_database_name



Was this article helpful?
Dislike 0
Views: 23