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:
StepID | DateStamp |
---|---|
3 | 08/06/2017 09:46:45 |
4 | 08/06/2017 09:47:15 |
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.
Leave a Reply