Windows PowerShell and SMO - Professional SQL Server 2008 Administration with Windows PowerShell - page 289

'" + @Folderpath + @databasename + "_" + convert(varchar(8),getdate(),112) + ".bak'"
end
If @Backuptype = "Tran"
begin
set @query = "backup database " + @databasename + " to disk =
'" + @Folderpath + @databasename + "_" + convert(varchar(8),getdate(),112) + ".trn'"
end
If @Backuptype = "Diff"
begin
set @query = "backup database " + @databasename + " to disk =
'" + @Folderpath + @databasename + "_" + convert(varchar(8),getdate(),112) + ".Dif'"
end
print @query
exec (@query)
GO
Next, create a PowerShell script Backup-DataBase.ps1 . This script connects to a SQL Server
instance and executes the uspBackupDB stored procedure. You can copy the code from the
uspBackupDB.sql file:
Param (
[string] $SQLSERVER,
[string] $DATABASE,
[string] $LOCATION,
[string] $BACKTUPTYPE
)
$SQL = "master.dbo.uspBackupDB"
#write-host $SQL
$conn = New-Object System.Data.SqlClient.SqlConnection("Data
Source = $SQLSERVER;Initial Catalog = master;Integrated Security = SSPI")
$cmd = New-Object System.Data.SqlClient.SqlCommand("$SQL", $conn)
$cmd.CommandType = [System.Data.CommandType]'StoredProcedure'
$cmd.Parameters.Add("@databasename",[System.Data.SqlDbType]"VarChar",128) | Out-Null
$cmd.Parameters.Add("@Folderpath",[System.Data.SqlDbType]"VarChar",1000) | Out-Null
$cmd.Parameters.Add("@BackupType",[System.Data.SqlDbType]"VarChar",4) | Out-Null
$cmd.Parameters["@databasename"].Value = $DATABASE
$cmd.Parameters["@Folderpath"].Value = $LOCATION
$cmd.Parameters["@BackupType"].Value = $BACKTUPTYPE
$conn.Open()
$cmd.ExecuteNonQuery() | Out-Null
$conn.Close()
Now you can create a folder ''Backup'' on the C: drive to store all the backup files. Here you are
going to use the cmdlet New-Item to create the folder:
New-Item -Path C:\ -Name Backup -Type directory
Now execute the PowerShell script backup-database.ps1 and take a backup of the master database
as shown here:
.\Backup-DataBase.ps1 PowerServer3 AdventureWorks2008 c:\Backup\ Full
.\Backup-DataBase.ps1 PowerServer3 AdventureWorks2008 c:\Backup\ Tran
.\Backup-DataBase.ps1 PowerServer3 AdventureWorks2008 c:\Backup\ Diff