title: Backup database using sqlcmd command during transaction and the DB tables are not located in the backup file
body: <p>I'm trying to backup a database using sqlcmd command during a transaction and any tables created during the transaction were not added to the backup file for some reason. Any idea why?</p>
  <pre><code>BEGIN TRAN [Test]
  
     BEGIN TRY
          
          DECLARE @Command as nvarchar(max)=NULL
  
          SET @Command = CONCAT('EXEC xp_cmdshell ''sqlcmd -Q &quot;CREATE DATABASE [Test] ON ( NAME = ImportDB, FILENAME = ''''C:\Test\Test.mdf'''' , SIZE = 4096KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = ImportDB_log, FILENAME = ''''C:\Test\Test_log.ldf'''' , SIZE = 1000MB , FILEGROWTH = 50%);&quot;',CHAR(39))
          EXEC sp_executesql @Command
  
          SET @Command = 'CREATE TABLE [Test].[dbo].[Test] 
          (
              [ID] int NOT NULL,
              [Data] nvarchar(50) NULL,
          CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
          (
              [ID] ASC
          )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
          ) ON [PRIMARY]'
          EXEC (@Command)
  
          SET @Command = 'INSERT INTO [Test].[dbo].[Test] SELECT 1,''testtesttesttesttest'''
          EXEC (@Command)
  
          SET @Command = CONCAT('EXEC xp_cmdshell ''sqlcmd -Q &quot;EXEC [tempdb].[dbo].sp_executesql N''''BACKUP DATABASE [Test] TO  DISK = ''''''''C:\Test\Test.bak'''''''' WITH NOFORMAT, INIT,  NAME = ''''''''Test'''''''' , SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10'''';&quot;',CHAR(39))
          EXEC (@Command)
  
  
      END TRY
  
      BEGIN CATCH
          SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() AS ErrorState,
          ERROR_PROCEDURE() AS ErrorProcedure,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage;
  
          IF @@TRANCOUNT &gt; 0
              BEGIN
                  ROLLBACK TRANSACTION [Test]
              END
      END CATCH;
  
      IF @@TRANCOUNT &gt; 0
          BEGIN
              COMMIT TRAN [Test]
          END
  </code></pre>