Dumping / restoring SQL Server data using Powershell

Sometimes you need to transfer data between two SQL Server databases. This Powershell 2.0 script might come in handy. It dumps one or all tables from the specified database to .DAT files and later restores them to another database with the same schema. Basically a convenient wrapper for bcp.

param([string]$action, [string]$srv, [string]$db, [string]$src)

$scriptDir = Split-Path $MyInvocation.MyCommand.Path -Parent
$fileName = Split-Path $MyInvocation.MyCommand.Path -Leaf

$sqlroot = "C:Program FilesMicrosoft SQL Server90"
$bcp = "$sqlrootToolsbinnbcp.exe"
$sqlcmd = "$sqlrootToolsbinnsqlcmd.exe"

function Dump-All
{
	Write-Host "Dumping all tables from $db..." -Fore Cyan
	[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
	$smosrv = New-Object Microsoft.SqlServer.Management.Smo.Server $srv
	$smodb = $smosrv.databases[$db]
	$smodb.Tables | % { 
		Dump $_.Name
	}
	Write-Host "All tables dumped" -Fore Green
}

function Dump($tbl)
{
	Write-Host "Dumping table $db.dbo.$tbl to $tbl.dat..." -Fore Cyan
	$args = "$db.dbo.$tbl", "out", "$tbl.dat", "-S$srv", "-T", "-w", "-E"
	& $bcp $args
	Write-Host "Dump completed" -Fore Green
}

function Restore-All
{
	Write-Host "Restoring all tables from $src..." -Fore Cyan
	(Get-ChildItem "$src*.DAT") | % { 
		Restore $_
	}
	Write-Host "All tables restored" -Fore Green
}

function Restore($datfile)
{
	if($datfile.Name)
	{
		$table = $datfile.Name.ToLower().Replace(".dat", "")
		$file = $datfile.FullName
	}
	else
	{
		$file = (Split-Path $datfile -Leaf)
		$table = $file.ToLower().Replace(".dat", "")
	}
	Write-Host "Restoring table $db.dbo.$table from $file..." -Fore Cyan

	& $sqlcmd -S $srv -E -d $db -Q `"TRUNCATE TABLE $table`"

	$args = "$db.dbo.$table", "in", "$file", "-S$srv", "-T", "-w", "-E"
	& $bcp $args
	Write-Host "Restore completed" -Fore Green
}

function Print-Help
{
	Write-Host "USAGE:" -Fore White
	Write-Host
	Write-Host "  .DbDumper out <servername> <database> <table | ALL>" -Fore White
	Write-Host "  Exports data from specified table to a file named after the table with a .DAT extension." -Fore White
	Write-Host "  If ALL is specified instead of table name, all tables will be dumped." -Fore White
	Write-Host "  Example: .DbDumper.ps1 out . APO_Profiles UserObject" -Fore White
	Write-Host
	Write-Host "  .DbDumper in <servername> <database> <filename | path>" -Fore White
	Write-Host "  Imports data from specified file to specified database." -Fore White
	Write-Host "  The file should have the same name as the table with a .DAT extension." -Fore White
	Write-Host "  If a path is specified, ALL files will be imported." -Fore White
	Write-Host "  Example: .DbDumper.ps1 in . APO_Profiles UserObject.DAT" -Fore White
	Write-Host
}

function main
{
	if($action -eq "out")
	{
		if($src -eq "ALL")
		{
			Dump-All
		}
		else
		{
			Dump $src
		}
	}
	elseif($action -eq "in")
	{
		Write-Host "WARNING!!! This will DELETE all data in the affected tables!!! ESC to abort." -Fore Yellow
		$key = $host.ui.RawUI.ReadKey("NoEcho,IncludeKeyUp,IncludeKeyDown")
        if ($key.VirtualKeyCode -eq 27) # ESCAPE
		{
			return;
		}

		if((Test-Path $src -PathType Container))
		{
			Restore-All
		}
		else
		{
			Restore $src
		}
	}
	else
	{
		Print-Help
	}
}

main
This entry was posted in Work and tagged . Bookmark the permalink.

Leave a Reply

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