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
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.
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).
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.
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.