In this post, we would like to cover Database Backup of different types and their restoration – which one is needed when and how to use them in which sequence. In earlier post, we had covered aspects of full backup with compression, cumulative backup and tran backup (Point in Time Recovery with Cumulative and Tran Backup Post Full Backup) however in this post, we will cover a different aspect of same concept from the very scratch meaning how do we create devices, create database and how do we attach or assign devices to a new database while creating it.
Let’s start first with Database Device Creation:
USE master
go
disk init name = ‘test_db_backup_data’,
physname = ‘/tmp/lab_test/devices/test_db_backup_data.dat’,
size = ’50M’, dsync = true
go
disk init name = ‘test_db_backup_log’,
physname = ‘/tmp/lab_test/devices/test_db_backup_log.dat’,
size = ’30M’, dsync = true
go
Above, we have created two devices – first is of 50MB size for data and second one is for log sized 30MB. Now, lets create database attaching these two devices using script as follows:
USE master
go
create database test_db_backup on test_db_backup_data=50 log on test_db_backup_log=30
go
Its time to use this database and create some object as well as insert some dummy data in order to perform test
use test_db_backup
go
create table test_backup(created_by varchar(12), created_date datetime, Purpose varchar(10))
go
insert into test_backup values(‘Pranaw’,’08-SEP-2022′, ‘Lab’)
go
Now, we have inserted data in above steps after creating table, you may cross check using select query on test_backup table. Let’s take full backup using dump command:
use master
go
dump database test_db_backup to ‘/tmp/lab_test/backup/test_db_backup.bak’
go
Now, lets create another database and devices for it to perform database restoration as we don’t want to over-write our backup database. This is required for lab purpose in order to test the data whether it is populated correctly or not.
disk init name = ‘test_db_restore_data’,
physname = ‘/tmp/lab_test/devices/test_db_restore_data.dat’,
size = ’50M’, dsync = true
go
disk init name = ‘test_db_restore_log’,
physname = ‘/tmp/lab_test/devices/test_db_restore_log.dat’,
size = ’30M’, dsync = true
go
create database test_db_restore on test_db_restore_data=50 log on test_db_restore_log=30
go
Now, database is created, lets load this database from the dump of backup database. We need to keep in mind that target database has to have size of data and log same as that of source database or larger otherwise, it won’t load and throw error. Lets load database using below command now:
use master
go
load database test_db_restore from ‘/tmp/lab_test/backup/test_db_backup.bak’
go
online database test_db_restore
go
Without using online database command, only restoration will not make it accessible and hence we need to issue online command explicitly in order to make it accessible and also for rollback and roll-forward to take place. We will see this in depth when we get into cumulative and tran backup.
Now, test_db_backup and test_db_restore has got exact same data and there is no difference in terms of object as well as data inside an object.
Lets get into cumulative backup now and see how to take cumulative backup
dump database test_db_backup cumulative to ‘/tmp/lab_test/backup/test_db_backup_cum1.bak’
go
When you issue above command, you get an error which says you need to enable cumulative backup at db_option level for above database.
use master
go
sp_dboption test_db_backup, ‘allow incremental dumps’, true
go
use test_db_backup
go
checkpoint
go
You need to issue checkpoint command as recommended by SAP Sybase ASE.
Now, lets insert some more data for testing:
use test_db_backup
go
insert into test_backup values(‘Pranaw’, ’09-SEP-2022′, ‘Lab Test’)
go
dump database test_db_backup to ‘/tmp/lab_test/backup/test_db_backup_full.bak’
go
insert into test_backup values(‘Kumar’, ’10-SEP-2022′, ‘YouTube’)
go
dump database test_db_backup cumulative to ‘/tmp/lab_test/backup/test_db_backup_cum1.bak’
go
insert into test_backup values(‘Pathak’, ’11-SEP-2022′, ‘Blog’)
go
dump database test_db_backup cumulative to ‘/tmp/lab_test/backup/test_db_backup_cum2.bak’
go
Above, please note that whenever we enable cumulative backup at db_option level, we need to take full backup first then only we can issue cumulative backup command so that it will know what is the last point from where changes need to be retained. We have taken two cumulative backup after successive inserts. Now, lets see how to load them cumulatively:
use master
go
load database test_db_restore from ‘/tmp/lab_test/backup/test_db_backup_full.bak’
go
load database test_db_restore cumulative from ‘/tmp/lab_test/backup/test_db_backup_cum1.bak’
go
online database test_db_restore
go
use test_db_restore
go
select * from test_backup
go
Since, we have loaded data upto cum1, we shall have data up to 10 SEP only and not up to 11 SEP. Now, what should be the loading mechanism to get data up to 11 SEP 2022? Yes, you got it right – we don’t need both the cumulative backups, we just need the last cumulative backup as it contains all the changes made from last full backup, so the script will be as follows:
use master
go
load database test_db_restore from ‘/tmp/lab_test/backup/test_db_backup_full.bak’
go
load database test_db_restore cumulative from ‘/tmp/lab_test/backup/test_db_backup_cum2.bak’
go
online database test_db_restore
go
Now, let’s talk about tran backup – Here changing db option is not required unless you have truncate log on chkpoint is enabled – in case, its enabled – you need to disable it. I have covered this in my another post Point in Time Recovery with Cumulative and Tran Backup Post Full Backup.
use test_db_restore
go
select * from test_backup
go
use test_db_backup
go
insert into test_backup values(‘Dba”s diary’, ’12-SEP-2022′, ‘SQL Server’)
go
dump tran test_db_backup to ‘/tmp/lab_test/backup/test_db_backup_tran1.bak’
go
Above insert statement also covers how to insert data in a table with single quote string. Let’s load this data and check the sequence below:
use master
go
load database test_db_restore from ‘/tmp/lab_test/backup/test_db_backup_full.bak’
go
load database test_db_restore cumulative from ‘/tmp/lab_test/backup/test_db_backup_cum2.bak’
go
load tran test_db_restore from ‘/tmp/lab_test/backup/test_db_backup_tran.bak’
go
online database test_db_restore
go
use test_db_restore
go
select * from test_backup
go
Last select statement is to showcase that it contains all rows i.e. until 12 SEP 2022. Now, we shall cover PITR(Point In Time Recovery) – I have covered all above scenario in my video at my lab. Request you to watch the same and provide your feedback: