0

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?

BEGIN TRAN [Test]

   BEGIN TRY
        
        DECLARE @Command as nvarchar(max)=NULL

        SET @Command = CONCAT('EXEC xp_cmdshell ''sqlcmd -Q "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%);"',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 "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'''';"',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 > 0
            BEGIN
                ROLLBACK TRANSACTION [Test]
            END
    END CATCH;

    IF @@TRANCOUNT > 0
        BEGIN
            COMMIT TRAN [Test]
        END
user2007221
  • 37
  • 6
  • 1
    If a transaction isn't commited before a backup is completed, it will not be present in the back up. – Thom A Jun 29 '20 at 09:23
  • 1
    A `RESTORE` command recovers the database to a consistent state during the restore process. The database will contain only committed transactions when the operation completes. The create table and insert statements in your example are rolled back. – Dan Guzman Jun 29 '20 at 10:21
  • Guys, Thank you for your assistance. Based on your answers I made some changes to my code to backup the DB only after the transaction committed. – user2007221 Jun 29 '20 at 11:42

0 Answers0