Thursday, July 12, 2012

Backup and Restore

--this backs up the database Automart
--the init makes it the first and allows differential backups
BackUp Database Automart
To Disk='C:\AutomartBackup\Automart.bak'
with init

--we will add a table between the initial and the differential backup
Use Automart
Create Table TestTable
(
   ID int identity(1,1) primary key,
   MyTimestamp DateTime
)

Insert into TestTable (MyTimestamp)
Values(GETDATE())

--the differential backup backs up whatever has changed
--since the last backup
Backup Database Automart
To disk='C:\AutomartBackup\Automart.bak'
with differential

--we will add a couple more rows
--that will be only in the log
Insert into TestTable (MyTimestamp)
Values(GETDATE())

use master
--now we backup the log
--the norecovery is required so that
--we can restore the initial backup and all
--the differential backups
Backup log Automart
to disk='C:\AutomartBackup\AutomartLog.trn'
With norecovery, no_truncate

--now we will restore the backups
--first the initial backup
--the the differential backup
--then the log. the log is with recovery
--to put the database back in a useable
--mode
Restore Database Automart
From Disk ='C:\AutomartBackup\Automart.bak'
with noRecovery, file=1

Restore Database Automart
From Disk ='C:\AutomartBackup\Automart.bak'
with noRecovery, file=2

use master
Restore log Automart
From Disk='C:\AutomartBackup\Automartlog.trn'
with Recovery

Use Automart

Select * From TestTable

No comments:

Post a Comment