Backup and Restore (Rollback) DB and Web Application during TFS Build

One of our TFS assignments needed to extend the current build and deployment process which includes (Creating and and Deploying Web package, Deploying Database, Versioning Assembly, Run Post Integration test after deployment, etc.) to include rollback which will perform backup and restore.

We agreed on performing the backup during the deployment and the restore will be a separate build definition.

For the backup during the deployment, I added the following section that backup the DB and the Web App.Backup DB and Web Squence

Command Text
———————–
“-verb:sync -source:iisapp='” + IISApplicationPath + “‘,computerName=” + ServerIP + “,userName=” + IISUserName + “,password=” + IISPassword + “, -dest:package='” + BuildDetail.BuildDefinition.Name + “_Backup.zip’,encryptPassword=password123”

The build definition for that part will be as the following:
Backup DB and Web Squence-parameters

For the rollback and restore, I added the following section that restore the backup for both DB and Web, for the DB I had to get exclusive access so I can restore the DB even there is active connection from other clients.

Restore DB and Web Squence

Command Text
———————–
“cmd.exe /k Sqlcmd -Q “”use master alter database “ + DBNameForBackup + ” set single_user with rollback immediate RESTORE DATABASE “ + DBNameForBackup + ” FROM DISK='” + DBNameForBackup + “_Bakup.bak’ WITH REPLACE alter database “ + DBNameForBackup + ” set multi_user”” -S “ + DBServerOrIP

Command Text
———————–
“-verb:sync -source:package=””C:WindowsSysWOW64” + BuildDefinitionDeploymentName + “_Backup.zip”” -dest:iisapp='” + IISApplicationPath + “‘,computerName=” + ServerIP + “,userName=” + IISUserName + “,password=” + IISPassword + “, -setParam:kind=ProviderPath,scope=iisApp,value='” + IISApplicationPath + “‘”

The build definition for that part will be as the following:
Restore DB and Web Squence-parameters

Some good commands that I end up with:

PowerShell
==========

sqlps (PowerShell for SQL)

PS SQLSERVER:> Backup-SqlDatabase -ServerInstance . -Database database1 -BackupAction Database

PS SQLSERVER:> Backup-SqlDatabase -ServerInstance 10.43.94.222 -Database database1 -BackupAction Database

PS SQLSERVER:> Invoke-Sqlcmd -Query “BACKUP DATABASE DATABASE1 TO DISK=’BackupsMyDB.bak'” -ServerInstance 10.43.94.222 -U YourUserName -P YourPassword

PS SQLSERVER:> Invoke-Sqlcmd -Query “RESTORE DATABASE DATABASE1 FROM DISK=’BackupsMyDB.bak’ WITH REPLACE” -ServerInstance 10.43.94.222 -U YourUserName -P YourPassword

PS SQLSERVER:> Invoke-Sqlcmd -Query “use master alter database database1 set single_user with rollback immediate alter database database1 set multi_user” -ServerInstance 10.43.94.222 -U YourUserName -P YourPassword

PS SQLSERVER:> Invoke-Sqlcmd -Query “use master alter database database1 set single_user with rollback immediate RESTORE DATABASE DATABASE1 FROM DISK=’BackupsMyDB.bak’ WITH REPLACE alter database database1 set multi_user” -ServerInstance 10.43.94.222 -U YourUserName -P YourPassword

sqlcmd
=======

sqlcmd -U YourUserName -P YourPassword -S 10.43.94.222

Sqlcmd -Q “BACKUP DATABASE DATABASE1 TO DISK=’BackupsMyDB.bak'” -S 10.43.94.222 -U YourUserName -P YourPassword

Sqlcmd -Q “use master alter database database1 set single_user with rollback immediate RESTORE DATABASE DATABASE1 FROM DISK=’BackupsMyDB.bak’ WITH REPLACE alter database database1 set multi_user” -S 10.43.94.222 -U YourUserName -P YourPassword

Delete DB Backup T-SQL
=========================

EXECUTE master.dbo.xp_delete_file 0,N’G:Microsoft SQL ServerMSSQL11.BGAPIDB01QMSSQLBackup’,N’BAK’

Invoke Process by call .bat file
===========================

BuildDirectory + “srcScriptsBackDB_Test.bat”

Invoke Process by cmd.exe and arguments
=======================================

File:   “cmd.exe”
Arguemnts:  “cmd.exe /k Sqlcmd -Q “”BACKUP DATABASE DATABASE1 TO DISK=’BackupsMyDB.bak'”” -S 10.43.94.189 -U YourUserName-P YourPassword”

===================================================================================================================================================================================

Web
=====

msdeploy -verb:sync -source:iisapp=’Default web site/Lara’,computerName=Localhost -dest:package=’defaultWebsiteBackup.zip’,encryptPassword=password123

msdeploy -verb:sync -source:iisapp=’Default web site/Lara’,computerName=172.18.0.333,userName=YourUserName,password=YourPassword, -dest:package=’defaultWebsiteBackup.zip’,encryptPassword=password123
msdeploy -verb:sync -source:package=”C:Program Files (x86)IISMicrosoft Web Deploy V3defaultWebsiteBackup.zip” -dest:auto -setParam:kind=ProviderPath,scope=iisApp,value=’Default web site/Lara’

msdeploy -verb:sync -source:package=”C:WindowsSysWOW64defaultWebsiteBackup.zip” -dest:iisapp=’Default web site/Lara’,computerName=172.18.0.333,userName=YourUserName,password=YourPassword, -setParam:kind=ProviderPath,scope=iisApp,value=’Default web site/Lara’


Share This: