The Safe and Easy Way to Delete All Data in a SQL Server DB : Page 2

Do's and Don'ts of Delete

You can run sp_DeleteAllData against any database, but I suggest that you use a copy and not a production database. Perhaps the quickest way to copy a database is to restore a backup copy using a new name:

1. Create a backup by right-clicking the database, choosing Tasks, and then selecting Back Up.
2. In the resulting Back Up Database dialog, retain the defaults and click OK.
3. Be sure to change the backup copy's name, if you already have a working backup on the same server. You can use an existing backup, but that might foul up your backup routine. So you're better off creating a new backup. For the sake of performance, use a small database.
4. Restore the backup by right-clicking the database, choosing Tasks, clicking Restore, and then selecting Restore.
5. In the To control in the Restore Database dialog box, enter a new name.

When you return to Management Studio, you'll find the copy of the backup in the Databases node.

Make sure your copy of the database is safely backed up somewhere—with data—before deleting all the data. With a backup handy, you have no reason to rely on a more complex solution. If something goes wrong or you change your mind, you simply restore the backup and start over.

Even with a backup, though, avoid dynamic calls. A problem in your automated solution could grind processing to a halt and take hours to troubleshoot.

Simply Deleted

It's easy to get caught up in writing the best code possible, but sometimes the easy way is enough. You can write complex code that will delete all the data in a database, but you'll work harder than necessary. Using the undocumented stored procedure sp_MSForEachTable and backing up the database first, makes quick work of an otherwise tedious task.

Previous Page