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.