RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


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

Deleting all the data in a SQL Server database doesn't have to involve complex code. The undocumented stored procedure sp_MSForEachTable safely bypasses built-in limitations to accomplish this common task.


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.

Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date