Inspired by a blog post from Bruce Chapman (iFinity) I built 2 powershell scripts that do (most of) the needed steps by starting a simple script. But before we take a closer look on the scripts lets talk a little bit about my setup.
- ) My customer websites on the server reside in a folder named customernumber_customername (e.g. 0000_bitboxx). In this folder I have a subfolder called “dnn” where the DNN installation is found. This gives me the opportunity to have other functionality installed for this customer in his home folder (e.g. “webmail”)
- ) Locally I have a virtual machine set up with all the developer and debugging stuff I need (VS, Resharper, Fiddler ..). And also all the stuff I need to run a DotNetNuke website (IIS, SQLServer)
- ) On the webserver, I need to install the Powershell Community Extensions found on Codeplex. This is only needed for zipping the website files and the backup of the database. If someone finds a better solution (with 7zip and command line parameters for example) please let me know!
- ) In my virtual machine I have an SQL User named “test” that I use in the local web.config for the copied website. The script adds this user automatically to the database and changes the appropriate line in web.config
- ) In opposite to Bruce Chapman I don’t change the domain of the installation. The script adds the domain to the host-file in the virtual machine. So running it in the VM ensures that you do not hassle with your live site. (If you want to be sure sure: disconnect the VM from the internet connection!)
If you never have worked with powershell before (as me a few days before), you need to know a few things. Normally powershell is installed on your windows machine. The version does not matter for my scripts. But before you can start with them you need to set the permission. By default running scripts is prohibited in windows security. So open up a powershell window as administrator and enter the following :
Set-ExecutionPolicy RemoteSigned
Now lets start with the backup script for the server:
The backup script
# Configure these
$DBName = "0006_bitboxx"
$DNNInstallPath = "D:\0006_bitboxx\dnn"
$OutPutPath = "D:\temp\backup\"
$SqlInstance = "MSSQLINSTANCE"
# Here we go...
$DNNZipFile = $DBName + ".dnn.zip"
$DBZipFile = $DBName + ".db.zip"
$ResZipFile = $DBName +".zip"
$DNNZipName = $OutPutPath + $DNNZipFile
# Clear screen
cls
#zip DNN to Targetdirectory
write-host "Zip DNN ..."
$dummy=Write-Zip -level 9 -IncludeEmptyDirectories -Path $DNNInstallPath -OutputPath $DNNZipName -Quiet
write-host "Backup DB ..."
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SqlInstance
$DBBakFile = $OutPutPath + $DBName + ".bak"
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoExtended') | out-null
$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
$dbBackup.Database = $DBName
$dbBackup.Devices.AddDevice($DBBakFile, "File")
$dbBackup.Action = "Database"
$dbBackup.SqlBackup($s)
write-host "Zip DB ..."
$DBZipName = $OutPutPath + $DBZipFile
$dummy = Write-Zip -level 9 $DBBakFile -OutputPath $DBZipName -Quiet
write-host "Clean up ..."
$dummy=Remove-Item $DBBakFile
write-host "Ready ! See files ($DBZipFile, $DNNZipFile) in $OutPutPath"
So, whats happening here ? At first, we need to configure some things in the first 4 lines like Database name, Path to DNN installation, Path to the resulting zip files and the name of your SQL server instance. The line which begins with $dummy = write-zip only functions if the powershell community extensions are installed. This command has the backdraft that it retains the directory structure in the zip only if you enter a dirctory (here $DNNInstallPath) as a parameter. Keep that in mind if you want to alter the script for your needs! Next we use the SMO to backup our database and after that we zip the resulting bak file too into our $OutPutPath. Last we delete the .bak file and end up with two zips: “0006_bitboxx.dnn.zip” which contains the DNN files and “0006_bitboxx.db.zip” which contains the backup of the database.
Download the zips
Sorry, no script for this… Do it the way you prefer :-)
The restore script
This one is a little bit more difficult and costs my a few grey hairs (more). But for me its running now perfectly. Tried to get the restore process of the database done with SMO too, but this does not work for me in some circumstances (don’t no which and why). So plain old SQL does the job ^^
# Configure these
$DBName = "0006_bitboxx"
$WebSiteAddress = "www.bitboxx.net"
$SqlInstance = "TW-WIN8-DEVWEB"
$frameworkRuntime = "v4.0" # "v2.0" oder "v4.0"
$InPutPath = "C:\Temp\Backup\"
$WebSitePath = "C:\Inetpub\wwwroot\$DBName\dnn\"
$OutPutPath = "C:\Inetpub\wwwroot\$DBName\"
# Here we go
$DNNZipFile = $DBName + ".dnn.zip"
$DBZipFile = $DBName + ".db.zip"
$LocalDBUser = "test"
$connectionString = "Data Source=$SqlInstance;Initial Catalog=$DBName;User ID=$LocalDBUser;Password=$LocalDBUser"
$WebsiteName = $DBName
$DNNZipPath = $InPutPath + $DNNZipFile
$DBZipPath = $InPutPath + $DBZipFile
cls
Import-Module WebAdministration
#remove existing IIS Site and App pool
write-host "Remove old website information in IIS (if any)..."
Remove-Item IIS:\Sites\$websiteName -Recurse -ErrorAction SilentlyContinue
Remove-Item IIS:\AppPools\$websiteName -Recurse -ErrorAction SilentlyContinue
#delete anything in the destination folder
write-host "Delete old contents of target directory (if any)..."
$dummy = new-item -force -path $OutPutPath -itemtype "directory"
$dummy = get-childitem $OutPutPath | remove-item -force -recurse
#unzip DNN to Targetdirectory
$Shell = New-Object -ComObject Shell.Application
$Dest = $Shell.namespace($OutPutPath)
$Zip = $Shell.namespace($DNNZipPath)
write-host "Unzip DNN Files..."
$Dest.CopyHere($Zip.items())
#Set the ACL on the folder
write-host "Adding 'FullControl' to website folder ..."
$Acl = Get-Acl $OutPutPath
$inherit = [system.security.accesscontrol.InheritanceFlags]"ContainerInherit, ObjectInherit"
$propagation = [system.security.accesscontrol.PropagationFlags]"None"
$accessrule = New-Object system.security.AccessControl.FileSystemAccessRule("NETZWERKDIENST", "FullControl", $inherit, $propagation, "Allow")
$Acl.SetAccessRule($AccessRule)
$dummy = Set-Acl -AclObject $Acl $OutPutPath
Write-Host "'Fullcontrol' access was added to $OutPutPath"
#Create the IIS Site and App pool
$dummy = New-Item IIS:\AppPools\$WebsiteName -Force
$dummy = Set-ItemProperty IIS:\AppPools\$WebsiteName -name ProcessModel.identityType -Value 2
$dummy = Set-ItemProperty IIS:\AppPools\$WebsiteName managedRuntimeVersion $frameworkRuntime
$dummy = New-Item IIS:\Sites\$WebsiteName -bindings @{protocol="http";bindingInformation=":80:$WebsiteAddress"} -physicalPath $WebSitePath
$dummy = Set-ItemProperty IIS:\Sites\$WebsiteName -name applicationPool -value $WebsiteName
write-host "Webside + Applicationpool for $WebsiteName created"
#Update the hosts file
$hostsentry = Select-String $Env:SystemRoot\System32\drivers\etc\hosts -pattern "$WebsiteAddress" -quiet
if (-not $hostsentry)
{
Add-Content $Env:SystemRoot\System32\drivers\etc\hosts "127.0.0.1 $WebsiteAddress"
write-host "Added $WebsiteAddress to hosts file"
}
#Unzip DB.bak
$Shell = New-Object -ComObject Shell.Application
$Dest = $Shell.namespace($InPutPath)
$Zip = $Shell.namespace($DBZipPath)
write-host "Unzip DB File..."
$Dest.CopyHere($Zip.items())
#Restore the database
#load assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"
#need to check if database exists, and if it does, drop it
$db = $server.Databases[$DBName]
if ($db)
{
#we will use KillDatabase instead of Drop
#Kill database will drop active connections before
#dropping the database
write-host "Database $DBName exists, will be deleted now!"
$server.KillDatabase($DBName)
}
#get backup file
$backupFile = $InputPath + $DBName + ".bak"
# Open ADO.NET Connection with Windows authentification to local $SqlInstance.
$con = New-Object Data.SqlClient.SqlConnection;
$con.ConnectionString = "Data Source=" + $SqlInstance + ";Initial Catalog=master;Integrated Security=True;";
$con.Open();
# Create the database.
$sql = "CREATE DATABASE [$DBName];"
$cmd = New-Object Data.SqlClient.SqlCommand $sql, $con;
$dummy = $cmd.ExecuteNonQuery();
Write-Host "Database $DBName is created!";
# DB Filename + Logfilename shortening (RESTORE DATABASE MOVE syntax ...)
$moveDB = $DBName
if ($moveDB.Length -gt 16)
{
$moveDB = $moveDB.Substring(0,16)
}
$moveDBLog = ($DBName + "_log")
if ($moveDBLog.Length -gt 20)
{
$moveDBLog = $moveDBLog.Substring(0,20)
}
Write-Host "Restoring Database $DBName ...";
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"
$db = $server.Databases[$DBName]
$DBMasterPath = $db.PrimaryFilePath
$sql = "USE [master]; RESTORE DATABASE [" + $DBName + "] FROM DISK = N'" + $backupFile+ "' WITH FILE = 1, MOVE N'"+ $moveDB + "' TO N'" + $DBMasterPath + $DBName +".mdf', MOVE N'" + $moveDBLog + "' TO N'"+ $DBMasterPath + $DBName+ "_log.ldf', NOUNLOAD, REPLACE, STATS = 5"
$cmd = New-Object Data.SqlClient.SqlCommand $sql, $con;
$dummy = $cmd.ExecuteNonQuery();
Write-Host "Database $DBName is Restored!";
remove-item $backupFile
# Adding "test" user to the database and make him dbowner
$sql = "USE [$dbname]"
$cmd = New-Object Data.SqlClient.SqlCommand $sql, $con;
$dummy = $cmd.ExecuteNonQuery();
$sql = "CREATE USER [$LocalDBUser] FOR LOGIN [$LocalDBUser]"
$cmd = New-Object Data.SqlClient.SqlCommand $sql, $con;
$dummy = $cmd.ExecuteNonQuery();
$sql = "EXEC sp_addrolemember N'db_owner', N'$LocalDBUser'"
$cmd = New-Object Data.SqlClient.SqlCommand $sql, $con;
$dummy = $cmd.ExecuteNonQuery();
Write-Host "Made User '$LocalDBUser' dbowner!";
# Close & Clear all ADO things.
$cmd.Dispose();
$con.Close();
$con.Dispose();
# Change web.config connection string
write-host "Change connectionstring in web.config..."
$webConfigPath = $WebSitePath +"web.config"
$backup = $webConfigPath + ".bak"
# Get the content of the config file and cast it to XML and save a backup copy labeled .bak
$xml = [xml](get-content $webConfigPath)
$xml.Save($backup)
# Change original connectionString
$root = $xml.get_DocumentElement();
$node = $root.SelectSingleNode("//connectionStrings/add[@name='SiteSqlServer']")
$node.connectionString = $connectionString
# Save it
$xml.Save($webConfigPath)
write-host "Restore Done!"
Hope these scripts can speed up your admin or developer life and frees you up from doing daunting tasks! And if you have any suggestions or better solutions, please let me know !