Share via

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

  • Article

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.

https://mohamedradwan.files.wordpress.com/2015/08/backup-db-and-web-squence1.png?w=660

Command Text

"-verb:sync -source:iisapp='" + IISApplicationPath + "',computerName=" + ServerIP + ",userName=" + IISUserName + ",password=" + IISPassword + ", -dest:package='" + BuildDetail.BuildDefinition.Name + "_Backup.zip',encryptPassword="

The build definition for that part will be as the following:

https://mohamedradwan.files.wordpress.com/2015/08/backup-db-and-web-squence-parameters.png?w=660

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.

https://mohamedradwan.files.wordpress.com/2015/08/restore-db-and-web-squence1.png?w=660

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:\Windows\SysWOW64\ + 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:

https://mohamedradwan.files.wordpress.com/2015/08/restore-db-and-web-squence-parameters.png?w=660

Some good commands

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 Server\MSSQL11.BGAPIDB01Q\MSSQL\Backup\,N'BAK'

Invoke Process by call .bat file

BuildDirectory + "\src\Scripts\BackDB_Test.bat"

Invoke Process by cmd.exe and arguments

File:   "cmd.exe"`` Arguments:  "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= msdeploy -verb:sync -source:iisapp='Default web site/Lara',computerName=172.18.0.333,userName=YourUserName,password=YourPassword, -dest:package='defaultWebsiteBackup.zip',encryptPassword= msdeploy -verb:sync -source:package="C:\Program Files (x86)\IIS\Microsoft Web Deploy V3\defaultWebsiteBackup.zip" -dest:auto -setParam:kind=ProviderPath,scope=iisApp,value='Default web site/Lara' msdeploy -verb:sync -source:package="C:\Windows\SysWOW64\defaultWebsiteBackup.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'