Every database administrator (DBA) is—first and foremost—human. And everyone makes mistakes. It’s not the absence of mistakes but rather how you prepare for those mistakes that makes you a great DBA. Luckily, there are many ways to prepare for those mishaps, whether the errors are made by you or someone else on your team.
One commonly made mistake is to drop an object in a database or accidentally delete data. It’s a typical practice to back up your database, so you can restore it if you forget to highlight the WHERE clause of an UPDATE or DELETE statement, causing you to delete or update way more data than you meant to. Most DBAs have had done this at some point and have had to restore from a backup to correct the data.
But what do you do when a single object such as an index, a stored procedure, a function, or even a view has been accidentally dropped or overwritten? Restoring from a backup can be time-consuming, especially when you only need a single object. This is where keeping scripts of the data definition language (DDL) of your objects as backups can come in handy.
This process is akin to using Continuous Integration (CI) and Continuous Delivery (CD) in DevOps. Using copies of scripts, you can seamlessly integrate and revert changes. In this case, we’re using them for reverting and fixing untimely accidents.
There are several ways you can script out objects in your database. Regardless of which method you choose, this process should be a part of your backup regimen. Let’s walk through one good method you could use.
Scripting Objects Using SQL Server Management Studio
SQL Server Management Studio (SSMS) makes it simple to generate scripts from the entire database. Right-click on the database, choose Tasks, then Generate Scripts as seen in Figure 1.
Figure 1
From here, you’ll be given the option to script out the entire database or just select objects by type. You can script Stored Procedures, Tables, or Functions, to name a few, as seen in Figure 2. You can also drill in deeper to script a single object. In this example, all the stored procedures are selected.
Though it’s not ideal, if you work in an environment where source control isn’t used, this may be an effective way to ensure you have the most recent copy of stored procedures prior to a deployment. Having these scripts in a code repository will make it easy to revert a change if you have scripted them out beforehand.
Figure 2
The next screen is where it can get interesting. There are a lot of options available for how and where to script the objects. For those interested in Notebooks, you can save the scripts to a Jupyter Notebook. There are options for you to save the scripts to a file and even an option to create a new file per object. Lastly, we can script them to a query window.
Figure 3
When you choose Finish, the wizard will script out all your objects and any settings associated with those objects.
All you must do now to fix a mistake is run the “create” script. Having the DDL in a secure location will save you the time it takes to restore a copy of the database and then manually recreate the object. No one wants to do a full restore just to grab one object—especially on a multi-terabyte database.
Scripting Indexes
When you have multiple environments such as test, development, and production, it’s easy to let these environments drift out of sync, especially if you aren’t using a DevOps tool or source control for controlled deployments.
This particularly common when it comes to indexes on a table. It is very easy to have schema drift on the indexes created in development and production; you can expect some drift throughout the development lifecycle as projects progress. However, it’s important to try to keep them the same. Whether it’s different included columns or simply indexes that don’t exist, it takes time to investigate these differences and then recreate or alter indexes to get them back in line. Let’s look at how to generate the DDL for these indexes.
The process of scripting out indexes is a little less intuitive with this tool. If you look at Figure 4, you’ll note that when you select tables, the indexes belonging to those tables aren’t listed, and indexes aren’t an option to pick.
Figure 4
To get to the indexes, we must dig a little deeper. First, choose the table or all tables to script and hit Next. When you get to the next screen, choose the Advanced button in the top-right corner.
Figure 5
Once in the Advanced menu, you’ll need to scroll down a bit to find the Script Index option under Table/View Options and change the value to True, as shown in Figure 6.
Figure 6
The option is buried, but clever DBAs know where to find it. When this option is chosen, you will not only get the table definition but all indexes—clustered, non-clustered, unique, and columnstore. The only downfall to this method is it generates the tables and the indexes. If you want only the indexes, you must manually parse the script and remove what you don’t want. Of course, many DBAs are script-savvy and can find a way to automate this part. Take the time to peek through the list of options, and you may find a few other useful gems you can benefit from, like statistics.
An “Undo” Button for Mistakes
DBAs make mistakes. But it’s easy to prevent long restore times for single objects by regularly scripting out database objects as part of your backup and restore strategies. Better yet, we can use a tool to do this for us. Get started with a free download of SolarWinds DBA xPress, a free tool you can use to try automating this defensive procedure.
