Creating a Time Series database for SQL Server Restore testing

As a prelude to a post describing some new features of the dbatools Restore-DbaDatabase function, I thought I’d just got through how I create a time series database to give me something to test restoring to Point in Time with minimal overhead.

This sort of database can also be really handy for your own training if you want to play with point in time restores, or explore what can be done with standby restores.

Premise

I want to end up with:

• A database – basic unit of restore
• Single table – Let’s keep this small and simple
• Table contains a series of rows written at a set time interval

So the table should end up like this:

We should end up with a nice spread of times so we can easily say ‘Restore to this point in time’ and then be able to check we’ve actually gotten there

The Script

USE MASTER
go

if exists (SELECT * from sys.databases where name='blogtest')
BEGIN
	ALTER DATABASE blogtest SET single_USER with rollback immediate;
	DROP DATABASE blogtest;
END
GO


CREATE DATABASE blogtest
go

alter database blogtest set recovery full
go

use blogtest
go

IF EXISTS (SELECT * FROM SYS.tables WHERE name='steps')
BEGIN
	drop table steps
END
go

CREATE TABLE steps(
step INTEGER,
dt DATETIME2
);
go

DECLARE @step INTEGER
SET @step=0

--@delay is the value in seconds between 
DECLARE @delay varchar(8)
set @delay = '00:30:00'

BACKUP DATABASE[blogtest] TO DISK='c:\dbatools\blogtest\blogtest.bak'

WHILE (@step<10)
BEGIN
INSERT INTO steps values (@step, GETDATE())
SELECT @step=@step+1
WAITFOR DELAY @delay
END
BACKUP LOG [blogtest] TO DISK='c:\dbatools\blogtest\blogtest_1.trn'


WHILE (@step<20)
BEGIN
INSERT INTO steps values (@step,GETDATE())
SELECT @step=@step+1
WAITFOR DELAY @delay
END

BACKUP LOG [blogtest] TO DISK='c:\dbatools\blogtest\blogtest_2.trn'

WHILE (@step<30)
BEGIN
INSERT INTO steps values (@step,GETDATE())
SELECT @step=@step+1
WAITFOR DELAY @delay
END

BACKUP DATABASE[blogtest] TO DISK='c:\dbatools\blogtest\blogtest2.bak'

WHILE (@step<40)
BEGIN
INSERT INTO steps values (@step, GETDATE())
SELECT @step=@step+1
WAITFOR DELAY @delay
END
BACKUP LOG [blogtest] TO DISK='c:\dbatools\blogtest\blogtest_21.trn'

WHILE (@step<50)
BEGIN
INSERT INTO steps values (@step,GETDATE())
SELECT @step=@step+1
WAITFOR DELAY @delay
END


BACKUP DATABASE[blogtest] TO DISK='c:\dbatools\blogtest\blogtest_22.bak' WITH DIFFERENTIAL

WHILE (@step<60)
BEGIN
INSERT INTO steps values (@step,GETDATE())
SELECT @step=@step+1
WAITFOR DELAY @delay
END


BACKUP LOG [blogtest] TO DISK='c:\dbatools\blogtest\blogtest_23.trn'

The script start off by creating an empty database and making sure it’s in Full Recovery mode so we can have transaction log backups. We then create a simple table with 2 columns, one for the integer id.

Declare 2 variables:

• @step is to hold a counter variable
• @delay to hold the waitfor value between steps. In this example it’s 30 seconds

Then it’s just a set of loops to insert data into the steps table at the defined intervals. After each loop we take a backup. In this example there’s a mix of Full, Differential and Transaction Log backups being taken. This can easily be adjusted to suit your own needs.

I’ll return to this example when I go through using the new standby and continue features in Restore-DbaDatabase in a future post.

If you’ve found this useful, or want a way to modify it to fit your needs then drop a comment below.