Where Does sqlpackage.exe Store Database Backups?

Question

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

score 1 · Answer 1 · answered Feb 03 '14 at 22:30

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'Software\Microsoft\MSSQLServer\MSSQLServer', 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'Software\Microsoft\MSSQLServer\MSSQLServer', 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'Software\Microsoft\MSSQLServer\Setup', 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.

Where Does sqlpackage.exe Store Database Backups?

1 Answers1