Skip to content

Instantly share code, notes, and snippets.

@NeilRobbins

NeilRobbins/gist:2226646

Created March 28, 2012 14:31
Show Gist options
  • Save NeilRobbins/2226646 to your computer and use it in GitHub Desktop.
Save NeilRobbins/2226646 to your computer and use it in GitHub Desktop.
Download ZIP
Backup, Restore, then delete a Database
Raw
gistfile1.ps1
Import-Module “sqlps” -DisableNameChecking
Set-Location SQLSERVER:\SQL\<MachineName>\<InstanceName>
Backup-SqlDatabase -ServerInstance <MachineName> -Database <NameOfDatabaseToCopy> -BackupFile <PathOfDirToStoreBak>\<BackupName>.bak
$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile(<NameOfDataFile>, "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\<NameOfDataFile>_COPY.mdf")
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("<NameOfDataFile>_log", "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\<NameOfDataFile>_COPY.ldf")
Restore-SqlDatabase -ServerInstance <MachineName>\<InstanceName> -Database <NameOfDatabaseToCopy> -BackupFile <PathOfDirToStoreBak>\<BackupName>.bak -RelocateFile @($RelocateData,$RelocateLog)
Invoke-Sqlcmd -Query "ALTER DATABASE <NameOfDatabaseToCopy> SET SINGLE_USER WITH ROLLBACK IMMEDIATE" -QueryTimeout 65534
Invoke-Sqlcmd -Query "DROP DATABASE <NameOfDatabaseToCopy>" -QueryTimeout 65534
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment