Tuesday, July 11, 2017

Backup Restore

--Basic backup
--differential backup
--back up log
--restores
--restore to a point in time

Backup Database Community_Assist 
to Disk='C:\backups\Community_Assist.bak'
with expiredate ='7/12/2017'

use Community_Assist
Create table AfterBackup
(
   afterbackupkey int identity(1,1) primary key,
   AfterbackupTime datetime
)

Insert into AfterBackup(AfterbackupTime)
values(GetDate())

Select * from AfterBackup 
Disk ='C:\backups\Community_Assist.log' 

Backup Database Community_Assist 
to Disk='C:\backups\Community_Assist.bak'
with differential

Backup log Community_Assist 
to Disk ='C:\backups\Community_Assist.log'
Use Master

Backup log Community_Assist to 
Disk ='C:\backups\Community_Assist.log' 
with norecovery

Restore database Community_Assist 
From Disk ='C:\backups\Community_Assist.bak' 
with recovery, file =1

Restore database Community_Assist 
From Disk ='C:\backups\Community_Assist.bak' 
with norecovery, file =2

Restore log Community_Assist 
From Disk ='C:\backups\Community_Assist.log' 
with recovery 

Create Database Test
Go
Use Test
Go
Create Table People
(
   personkey int,
   PersonLastName nvarchar(255),
   PersonFirstname nvarchar(255),
   Email nvarchar(255)
)
Go
Insert into People(personKey, 
PersonLastName, PersonFirstname,
Email)
Select personKey, 
PersonLastName, PersonFirstname,
PersonEmail from Community_Assist.dbo.Person

Select * from People

Backup database test to disk='C:\Backups\test.bak'
Backup log test to disk='C:\Backups\test.log'

update People
set PersonLastName='Smith'
use Master



RESTORE LOG Test 
   FROM Disk='C:\Backups\Test.log'  
   WITH FILE=1, NORECOVERY, STOPAT = 'jul 11, 2017 2:10 PM';  
RESTORE DATABASE Test WITH RECOVERY;  
Use Master
Use test
Select * from People

No comments:

Post a Comment