Data Management

Get five free SQL Server scripts for novice DBAs

These five basic scripts can help an IT professional streamline the administration of Microsoft SQL Server.

In addition to the scripts below, you can take your SQL Server skills to the next level with our Quick Guide: Network Administrator's SQL Server. This collection of resources addresses some of the pressing concerns facing IT pros responsible for ensuring the performance, availability, and security of their organizations' SQL Server systems.

If you are a new SQL Server database administrator (DBA), a developer who has been recently given new DBA responsibilities for a SQL server, or an administrator who has to manage a SQL server as part of your network, then we have some free scripts that can help you. In order to get up to speed with some important SQL Server tasks, here are five scripts (two of them are from other sources) that can make your job a little easier.


Download

You can also download this article in Microsoft Word and PDF format to make it easier to import these scripts into your SQL Server system.


1. Back up databases

As far as I am concerned, one of the most important jobs of a DBA is the ability to back up and restore databases in the event of a failure. The following script shows you how to quickly back up databases (and the next script shows you how to restore them).

The script shown in Listing A will back up all databases on your SQL server except the databases listed in and name not in. This script comes from the SQLServerCentral.com Script Library.

2. Restore script

The simple script shown Listing B in allows you to restore your databases from any given directory. Simply replace the database name PUBS with your database name and change the location of the backup files to match your backup files.

3. Transferring logins from one server to another

The script in Listing C can be downloaded from Microsoft's Web site, or you can copy it from our listing. Simply paste this script in Query Analyzer and run the script to install. Next, run sp_help_revlogin only, and the output will be all of your SQL Server logins in encrypted format. Next, copy the output to the new server and execute in Query Analyzer. Your logins will be recreated. Remember to delete the stored procedure from your original database so that no one else may create this output.

4. sp_MSforeachdb

The SQL Server gem in Listing D allows you to replace some cursors with a few lines. It is a must for any new DBA. With this stored procedure, you can quickly run a checkdb or other quick administrative tasks.

5. Rebuild indexes

The SQL Server script inListing E will allow you to rebuild all of your indexes as long as you input the correct fill factor. The fill factor is defined as value from 1 through 100 that specifies the proportion of the index page to be left empty. The default value for fill factor is 0.

Editor's Picks