Overview

When creating a VenueRecovery and the Transactional Data exceeds 10 GB in Size limit which prevents SQL Express from being able to Restore the bak file.

Please following the below guide 

Instructions:

Method 1:

1. Create a Venue Recovery from Database.exe on the Headoffice Workstation for the Specified VenueID. I.e VenueId = 2

2. Once the VenueRecovery complete, it now time to delete data from a specify date/Shift 

3. Login to SSMS 

4. Query the VenueRecovery DB

5. Using the following Script you can reduce the Transactional Data. 

SQL Query:

-- Ensure that you delete the VenueRecovery.bak from 'C:\\bepoz\\backup' before running the below query 

USE VenueRecovery

--SET DATE TO DELETE TRANSACTIONAL DATE FROM 

DECLARE @IDATE DATETIME

DECLARE @iShiftFrom INT 

set @IDATE = '01-01-2022' --<<<<<< change date here DD,MM,YYYY <<<<<<<<<<<<<<

select @iShiftFrom = (datediff(day, '01-01-2001', @idate) * 10) + 9

DELETE FROM TransLines where TransactionID in (select TransactionID from Transactions where shiftid <= @iShiftFrom)

DELETE FROM TransCards where TransactionID in (select TransactionID from Transactions where shiftid <= @iShiftFrom)

DELETE FROM TransPayments where TransactionID in (select TransactionID from Transactions where shiftid <= @iShiftFrom)

DELETE FROM Transactions where shiftid <= @iShiftFrom

DELETE FROM POLINE 

DELETE FROM POHEADER 

UPDATE TransComment SET Comment = ''

GO

USE [VenueRecovery]  DBCC SHRINKFILE (N'VenueRecovery' , 1)    

USE [VenueRecovery]  DBCC SHRINKFILE (N'VenueRecovery_log' , 1)    

GO

DECLARE @name VARCHAR(50) -- database name  

DECLARE @path VARCHAR(256) -- path for backup files  

DECLARE @fileName VARCHAR(256) -- filename for backup  

DECLARE @fileDate VARCHAR(20) -- used for file name

 

-- specify database backup directory

SET @path = 'C:\\Bepoz\\Backup\\'   --Default Bepoz backup directory

 

-- specify filename format

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

 

DECLARE db_cursor CURSOR READ_ONLY FOR  

SELECT name 

FROM master.sys.databases 

WHERE name IN ('VenueRecovery')  

AND state = 0 -- database is online

AND is_in_standby = 0 -- database is not read only for log shipping

 

OPEN db_cursor   

FETCH NEXT FROM db_cursor INTO @name   

 

WHILE @@FETCH_STATUS = 0   

BEGIN   

   SET @fileName = @path + @name + '.BAK'  

   BACKUP DATABASE @name TO DISK = @fileName  

 

   FETCH NEXT FROM db_cursor INTO @name   

END   

 

CLOSE db_cursor   

DEALLOCATE db_cursor

---

• You can now manually zip the .BAK File in the backup directory

• Copy and Restore the zip file as per normal database Restore functionality. 

---

Method 2:

Is to use the automated toolbox file called : VenueRecovery_Resize.exe

1. Create a Venue Recovery from Database.exe on the Headoffice Workstation for the Specified VenueID. I.e VenueId = 2

2. Run VenueRecovery_Resize.exe and follow the onscreen prompts 

• The first prompt will highlight that you need to create a VenueRecovery DB first before you continue 

• The second prompt will prompt for a Date in DD/MM/YYYY format for the Transactional Data that you want to Delete less than and equal to Date specified

• It will then delete all the transaction data from said date <= Date Specified from the VenueRecovery DB only

NOTE: If the VenueRecovery DB has 10 GB of Data this process can take some time to delete the Transactional Data

• It will then automatically Zip the file 

• Then open the explorer window directly to the Directory where the Zip file was created

---

Related Articles:

• Example 1