0

I have a requirement to regularly take a backup of a set of tenant databases (all same schema), and restore them in our UAT environment, then clear down most of the client data, leaving only a few months worth. I'm clearing about 7 years of data each time. This happens monthly. Each database is about 100GB. SQL Server 2008 R2.

My current process is this:

  • Backup PROD to a BAK file full copy-only
  • Backup the current UAT database full copy-only
  • Restore the PROD BAK file with replace over the UAT database
  • Then I run a script that is structured much like the following, which deletes data in batches from a rolling view:

Script:

USE [UAT.TenantName]

-- This is setup to keep only the orders
-- created within the last three months.
-- Change as appropriate for the requirements
CREATE VIEW vw_DeleteOrder
AS
    SELECT TOP (10000) * FROM [Order]
    WHERE DateCreated < DATEADD(MONTH,-3, GETDATE())
    ORDER BY OrderId DESC
GO

-- protect against ever running this against the wrong database
-- database should have the name UAT as prefix
IF (DB_NAME() LIKE 'UAT%')
BEGIN

    DECLARE
    @DatabaseName NVARCHAR(30) = '[' + DB_NAME() + ']',
    @RestrictedUser BIT = 0,
    @DisableConstraints BIT = 0,
    @Reindex BIT = 0

    IF @RestrictedUser = 1
    BEGIN
        EXEC('ALTER DATABASE ' +  @DatabaseName + ' SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE; ')
    END

    IF @SimpleMode = 1
    BEGIN
        EXEC('ALTER DATABASE ' +  @DatabaseName + ' SET RECOVERY SIMPLE; ')
    END
   
    DECLARE @msg NVARCHAR(1000)
    DECLARE @Ordercount INT, @MaxId INT, @MinId INT

    IF @DisableConstraints = 1
    BEGIN
        -- disable all constraints
        EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
    END

    -- notifications and messaging (not relevant between envs)
    DELETE FROM OrderNotificationQueue
    DELETE FROM OrderMessageQueue
    DELETE FROM PaymentExtractOrderPayment
    DELETE FROM PaymentExtract

    SELECT @msg = 'Deleted Notifications and messaging:: ' + CONVERT(varchar, SYSDATETIME(), 121)
    RAISERROR(@msg, 0, 1) WITH NOWAIT
 
    DECLARE @OrderIds TABLE (Id INT, CustomerId INT, ShippingAddressId INT)
    DECLARE @AttachmentIds TABLE(Id INT)
    DECLARE @OrderAttachmentIds TABLE (Id INT)
    DECLARE @AddressIds TABLE (Id INT)
    DECLARE @CustomerIds TABLE (Id INT)
    DECLARE @StorageFileIds TABLE (Id INT)
    DECLARE @OrderInvoiceIds TABLE (Id INT)
    
    SET NOCOUNT ON;
    DECLARE @r INT;
    SET @r = 1;
    
    WHILE @r > 0
    BEGIN
        BEGIN TRY

        BEGIN TRANSACTION;
   
   
        -- Data for linked deletes
        INSERT INTO @OrderIds SELECT OrderId, CustomerId, ShippingAddressId FROM Order WHERE OrderId IN (SELECT OrderId FROM vw_DeleteOrder)
        INSERT INTO @OrderAttachmentIds SELECT OrderAttachmentId FROM OrderAttachment WHERE OrderId IN (SELECT Id FROM @OrderIds)
        INSERT INTO @CustomerIds SELECT CustomerId FROM Order WHERE OrderId IN (SELECT Id FROM @OrderIds)
        INSERT INTO @AddressIds SELECT AddressId FROM Customer WHERE CustomerId IN (SELECT CustomerId FROM @OrderIds)
        INSERT INTO @AddressIds SELECT ShippingAddressId FROM @OrderIds
        INSERT INTO @StorageFileIds SELECT StorageFileId FROM OrderAttachment WHERE OrderId IN (SELECT Id FROM @OrderIds) AND StorageFileId IS NOT NULL
        INSERT INTO @StorageFileIds SELECT StorageFileId FROM OrderInvoice WHERE OrderId IN (SELECT Id FROM @OrderIds) AND StorageFileId IS NOT NULL
        INSERT INTO @OrderInvoiceIds SELECT OrderInvoiceId FROM OrderInvoice WHERE OrderId IN (SELECT Id FROM @OrderIds)
             
        SELECT @msg = 'Completed inserts:: ' + CONVERT(varchar, SYSDATETIME(), 121)
        RAISERROR(@msg, 0, 1) WITH NOWAIT
           
        -- Invoicing        
        DELETE FROM OrderInvoiceQueue WHERE OrderId IN (SELECT Id FROM @OrderIds)
        DELETE FROM OrderInvoiceAppliedRate WHERE OrderInvoiceId IN (SELECT Id FROM @OrderInvoiceIds)
        DELETE FROM OrderInvoiceLine WHERE OrderInvoiceId IN (SELECT Id FROM @OrderInvoiceIds)
        DELETE FROM OrderInvoice WHERE OrderId IN (SELECT Id FROM @OrderIds)
        SELECT @msg = 'Deleted Order Invoice data:: ' + CONVERT(varchar, SYSDATETIME(), 121)
        RAISERROR(@msg, 0, 1) WITH NOWAIT          
      
        -- Order data
        DELETE FROM OrderAttribute WHERE OrderId IN (SELECT Id FROM @OrderIds)
        DELETE FROM OrderPayment WHERE OrderId IN (SELECT Id FROM @OrderIds)
        DELETE FROM OrderNote WHERE OrderId IN (SELECT Id FROM @OrderIds)
        DELETE FROM OrderReference WHERE OrderId IN (SELECT Id FROM @OrderIds)
        DELETE FROM OrderOwner WHERE OrderId IN (SELECT Id FROM @OrderIds)
        DELETE FROM OrderRelation WHERE OrderId IN (SELECT Id FROM @OrderIds) OR RelatedOrderId IN (SELECT Id FROM @OrderIds)
        
        SELECT @msg = 'Deleted Order related data:: ' + CONVERT(varchar, SYSDATETIME(), 121)
        RAISERROR(@msg, 0, 1) WITH NOWAIT

        -- Order attachment data
        DELETE FROM OrderAttachment WHERE OrderId IN (SELECT Id FROM @OrderIds)

        SELECT @msg = 'Deleted Order attachment data:: ' + CONVERT(varchar, SYSDATETIME(), 121)
        RAISERROR(@msg, 0, 1) WITH NOWAIT
   
        -- Random links to Order
        DELETE FROM FinanceExtractOrderInvoice WHERE OrderInvoiceId IN (SELECT Id FROM @OrderInvoiceIds)

        -- Storage files
        DELETE FROM StorageFile WHERE StorageFileId IN (SELECT Id FROM @StorageFileIds)
              
        DELETE FROM vw_DeleteOrder;
         
        SET @r = @@ROWCOUNT;
        SELECT @msg = 'Rows left:: ' + CAST(@r AS NVARCHAR(10)) + ' :: ' + CONVERT(varchar, SYSDATETIME(), 121)
        RAISERROR(@msg, 0, 1) WITH NOWAIT
         
        SELECT @msg = 'Deleted Order data:: ' + CONVERT(varchar, SYSDATETIME(), 121)
        RAISERROR(@msg, 0, 1) WITH NOWAIT
   
        -- Delete customers from defined list (need to check multiple use addresses)
        DELETE FROM Customer 
        WHERE CustomerId IN (SELECT DISTINCT CustomerId FROM @CustomerIds) 
        AND CustomerId NOT IN (SELECT CustomerId FROM [Order])
        SELECT @msg = 'Deleted customer data:: ' + CONVERT(varchar, SYSDATETIME(), 121)
        RAISERROR(@msg, 0, 1) WITH NOWAIT
          
        -- Delete addresses from defined list (need to check multiple use customers)
        DELETE FROM [Address] 
        WHERE AddressId IN (SELECT DISTINCT Id FROM @AddressIds)
        AND AddressId NOT IN (SELECT ShippingAddressId FROM [Order])
        AND AddressId NOT IN (SELECT AddressId FROM Customer WHERE CustomerId IN (SELECT CustomerId FROM [Order]))
        SELECT @msg = 'Deleted address data:: ' + CONVERT(varchar, SYSDATETIME(), 121)
        RAISERROR(@msg, 0, 1) WITH NOWAIT
 
        IF @@TRANCOUNT = 1 
        BEGIN
            COMMIT TRANSACTION
            SELECT @msg = 'Transaction committed:: ' + CONVERT(varchar, SYSDATETIME(), 121)
            RAISERROR(@msg, 0, 1) WITH NOWAIT
        END
    
        --- run twice to make sure log wraps
        CHECKPOINT 
        CHECKPOINT
        SELECT @msg = 'Checkpoint:: ' + CONVERT(varchar, SYSDATETIME(), 121)
        RAISERROR(@msg, 0, 1) WITH NOWAIT
        
        END TRY
        BEGIN CATCH
            IF (XACT_STATE()) = -1
                ROLLBACK TRANSACTION
            IF (XACT_STATE()) = 1
                COMMIT TRANSACTION

            -- stop loop
            SET @r = 0

            DECLARE 
                @Error NVARCHAR(MAX) = ERROR_MESSAGE(), 
                @Severity INT = ERROR_SEVERITY(),
                @State SMALLINT = ERROR_STATE()
            RAISERROR(@Error, @Severity, @State)
        END CATCH
    
    END

    IF @DisableConstraints = 1
    BEGIN
        EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL' 
    END

    SELECT @Ordercount = COUNT(*) FROM [Order]
    SELECT @msg = 'Final Orders:: ' + CAST(@Ordercount AS NVARCHAR(10)) + ' :: ' + CONVERT(varchar, SYSDATETIME(), 121)
    RAISERROR(@msg, 0, 1) WITH NOWAIT

    IF (@RestrictedUser = 1)
    BEGIN
        EXEC('ALTER DATABASE ' +  @DatabaseName + ' SET MULTI_USER WITH ROLLBACK IMMEDIATE; ') 
    END

    IF @Reindex = 1
    BEGIN
        ---- Rebuild affected indexes
        DBCC DBREINDEX ('Address')
        DBCC DBREINDEX ('Order')
        -- etc
    END

    EXEC('ALTER DATABASE ' +  @DatabaseName + ' SET RECOVERY FULL; ')
 
END
ELSE
BEGIN
    RAISERROR('Database clear down process only runs when the database name has the prefix UAT', 0, 1) WITH NOWAIT
END
  • I then anonymize what is left for GDPR purposes (overwriting customer GDPR related data with junk. i.e. Customer names, addresses, ad other personably identifiable information), so that tenants can do operational business process testing.

At the end of this process 98% of the data has gone, but the database size is still the same on disk.

Everything I seem to read online screams "don't use shrink database or shrink file". However, these databases still need to be operational, but I really want to recover the space.

For this kind of scenario, where I need to copy and restore a database like this, and recover space without making the database horrifically fragmented, and I really really don't care about recovering the data I've deleted in this process, what is the suggested solution?

3
  • "I then anonymize what is left for GDPR purposes...At the end of this process 98% of the data has gone" - My question is, what are you actually testing at this point if you've manipulated the data this much?...Perhaps your process can be simplified in a way where you wouldn't have to worry about constant change in database size, depending on the answer to that question.
    – J.D.
    Mar 24 at 11:35
  • @J.D. Tenants are using the left over data to perform business process testing. They don't care what the customer name was, or address was. As far as we are concerned, once we have pruned the database, and run the GDPR scripts (basically updates every text field to junk strings), we don't care about the data that has been deleted. We don't need to roll it back, we don't need to recover it. It's purely a scaled down version, with all the configuration settings from the PROD environment, with a limited set of anonymized data.
    – Rebecca
    Mar 24 at 14:00
  • I guess the question is what data changes in the production environment, that you aren't anonymizing, and you are using in your test environment? Or if all the remaining data is anonymized, then why is this a recurring process and not just a static database in your test environment? If there is data changes, maybe your process doesn't have to entail dumping a full backup every time, just to get a tiny subset of changes, rather there may be a more efficient solution to keep a static database that you only sync certain changes to.
    – J.D.
    Mar 24 at 17:51

2 Answers 2

1

Your options are:

  • Backup/restore and then shrink
  • Export/import only the data that you actually need (instead of backup/restore)

You have to ask yourself: What is this disk space worth?

Also, if this is a regular process, you will need that disk space the next time you perform the operation - so what do you really gain by doing the shrink?

If you are still determined to do the shrink, then just go ahead. You'll have a horrible fragmentation after the shrink, but unless you have the database on a single magnetic disk (very unlikely) that might not hurt you much - at all!

If you still want to get rid of the fragmentation, then just rebuild your indexes - the database is tiny now that you have deleted all this data.

0

At the end of this process 98% of the data has gone, but the database size is still the same on disk.

If you can afford downtime

  1. Create new database with OldName_Temp
  2. Script out complete data and schema with all objects from Old database to this new database "Old_name_temp"
  3. Once data is moved rename OldName to something else so that you have the database name free
  4. Now rename the Oldname_temp to Oldname.
  5. Drop the old database.

Now you have new database with free space available.

Shrinking is "eveil" if done daily. In your scenario you either have to shrink or use method suggested above to get the space. Making shrink a habit is what people ask DBA's and developers not to develop.

2
  • I like this idea, but can it be scripted? I know I can use' Generate Scripts' in the GUI and then chose 'Schema and Data', but I want to automate this as best as possible.
    – Rebecca
    Mar 24 at 15:15
  • 1
    You can script it using Powershell and the SMO API from Powershell. I've done that, but that work was for clients so I don't have anything saved that I can share, I'm afraid. Mar 24 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.