I have a deployment script which uses sqlpackage.exe to deploy incremental changes to a database. (The project is maintained in the database edition of Visual Studio 2012 Express.) One of the flags I'm passing to this tool is:
/p:BackupDatabaseBeforeChanges=True
What I'm not finding, however, is where these backups exist. So far every piece of documentation I find on MSDN simply says:
Get or set boolean that specifies whether a database backup will be
performed before proceeding with the actual deployment actions.
Are the backups only temporary and simply deleted after a successful deployment? Or, worse, is it simply not creating backups at all despite this flag? If it is creating backups, where are they? I've looked in:
C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012\MSSQL\Backup
But there's nothing there. Maybe the "backup" is in a different form somewhere? Essentially what I'd like to do is indefinitely retain a snapshot of the target database before any given deployment (basically what I do with the deployed application in the same script).
Best Answer
Have a look at the .sql script that the deploy is creating. It should contain something like
IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) BEGIN DECLARE @rc int, -- return code @fn nvarchar(4000), -- file name for back up @dir nvarchar(4000) -- backup directory
END GO
As you can see above, the destination for the backups is determined by the registry values which store the default backup location for the target SQL Server.