Quick data wipe via truncate

“How to remove all data from a database?” is something that I have often seen asked on the forums. How do you remove all the data quickly? First you try with delete and find that this is too slow and the transaction log has to record all the changes, so you use truncate which is great at removing the data and with less logging. However the problem with this is that you cannot use truncate when there are constraints.
On a side note I often say that it is best to have a scripted empty version of your database rather that remove data.

You will need SMO installed for this script to work.

This script will produce the commands to:
1. Drop Constraints
2. Truncate All Tables
3. Recreate Constraints
It is not fully automated for safety and relies on the end user checking that the scripts perform the tasks they want!
Replace (local) and AdventureWorks with the sql instance/database you want to script the changes for, and as long as you have not got any schema bound objects then you will have a script to accomplish the task.


0. Backup taken!!!
1. Correct Server/Instance selected?
2. Correct Database selected?

I take no Liability for any issues relating to this script, but appreciate any feedback.

[Void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
[Microsoft.SqlServer.Management.Smo.ScriptingOptions] $ScriptingOptions = New-Object "Microsoft.SqlServer.Management.Smo.ScriptingOptions";
$ScriptingOptions.TargetServerVersion = [Microsoft.SqlServer.Management.Smo.SqlServerVersion]::Version90; #Version90, Version100, Version105
$ScriptingOptions.ContinueScriptingOnError = $false; #ignore scripts errors, advisable to keep set to $false
$SqlInstance = New-Object "Microsoft.SqlServer.Management.Smo.Server" "(local)"; 
$SqlInstance.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Table], "IsSystemObject");
$SqlInstance.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject");
$Database = $SqlInstance.Databases["AdventureWorks"];
$DropFKScript = $null;
$TruncateScript = $null;
$CreateFKScript = $null;
$SchemaBound = $($Database.Views|Where-Object {!$_.IsSystemObject -and $_.IsSchemaBound}|%{$_});
If ($SchemaBound -eq $null){
	$ScriptingOptions.DriForeignKeys = $true;
	$ScriptingOptions.SchemaQualifyForeignKeysReferences = $true;
	$ScriptingOptions.ScriptDrops = $false;
	$CreateFKScript += "{0}`r`nGO`r`n" -f $($_.Script($ScriptingOptions));
	$ScriptingOptions.ScriptDrops = $true;
	$DropFKScript += "{0}`r`nGO`r`n" -f $($_.Script($ScriptingOptions));
	$TruncateScript += $Database.Tables|Where-Object {!$_.IsSystemObject}|%{"TRUNCATE TABLE [{0}].[{1}]" -f ($_.Schema,$_.Name)};
	Write-Host "Schema Bound Objects Present:";
	$SchemaBound|%{"[{0}].[{1}]" -f ($_.Schema,$_.Name)};


Leave a Reply

Your email address will not be published. Required fields are marked *