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?
