Ryfujk

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 FilesMicrosoft SQL ServerMSSQL11.SQLEXPRESS2012MSSQLBackup

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).

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

EXEC @rc = [master].[dbo].[xp_instance_regread] N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'BackupDirectory', @dir output, 'no_output'

if (@rc = 0) SELECT @dir = @dir + N''



IF (@dir IS NULL)

BEGIN 

    EXEC @rc = [master].[dbo].[xp_instance_regread] N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'DefaultData', @dir output, 'no_output'

    if (@rc = 0) SELECT @dir = @dir + N''

END



IF (@dir IS NULL)

BEGIN

    EXEC @rc = [master].[dbo].[xp_instance_regread] N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerSetup', N'SQLDataRoot', @dir output, 'no_output'

    if (@rc = 0) SELECT @dir = @dir + N'Backup'

END



IF (@dir IS NULL)

BEGIN

    SELECT @dir = N'$(DefaultDataPath)'

END



SELECT  @fn = @dir + N'$(DatabaseName)' + N'-' + 

        CONVERT(nchar(8), GETDATE(), 112) + N'-' + 

        RIGHT(N'0' + RTRIM(CONVERT(nchar(2), DATEPART(hh, GETDATE()))), 2) + 

        RIGHT(N'0' + RTRIM(CONVERT(nchar(2), DATEPART(mi, getdate()))), 2) + 

        RIGHT(N'0' + RTRIM(CONVERT(nchar(2), DATEPART(ss, getdate()))), 2) + 

        N'.bak' 

        BACKUP DATABASE [$(DatabaseName)] TO DISK = @fn

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.