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