CREATE DUMMY DATABASE - size close to 200GB

 CREATE DUMMY DATABASE - size close to 200GB

 USE DummyBackupTestDB;

GO

-- ⚠️ WARNING: This will create a large database (~200GB). Ensure you have enough disk space.

-- Step 1: Create a test database

IF DB_ID('DummyBackupTestDB') IS NULL

    CREATE DATABASE DummyBackupTestDB;

GO

-- Step 2: Switch to the new DB

USE DummyBackupTestDB;

GO

-- Step 3: Create a table with large fixed-length columns

IF OBJECT_ID('dbo.BigDataTest') IS NULL

BEGIN

    CREATE TABLE dbo.BigDataTest (

        ID INT IDENTITY(1,1) PRIMARY KEY,

        JunkData CHAR(8000) NOT NULL DEFAULT REPLICATE('A', 8000)

    );

END

GO

-- Step 4: Fill the table with large amounts of data

-- Each row is ~8KB. To reach ~200GB: 200 * 1024 MB / 8 KB = 26,214,400 rows

-- Insert in 1M row batches (approx 8GB per batch)

DECLARE @Batch INT = 0;

WHILE @Batch < 25

BEGIN

    PRINT 'Inserting batch ' + CAST(@Batch + 1 AS VARCHAR(10));

    

    INSERT INTO dbo.BigDataTest (JunkData)

    SELECT TOP (1000000) REPLICATE('A', 8000)

    FROM sys.all_objects a

    CROSS JOIN sys.all_objects b;

    

    SET @Batch += 1;

END

GO

-- Step 5: Verify row count and size

SELECT COUNT(*) AS TotalRows FROM dbo.BigDataTest;

GO