DB Backup and Restore with Differential backups

--Taking Backup

--Step1:

BACKUP DATABASE TestDBMain TO DISK = 'C:\Temp\DatabaseBackups\TestDBMain_FullB.bak'

GO

--Step2:

--Create some test table and insert some Rows

--Step3:

BACKUP DATABASE TestDBMain TO DISK = 'C:\Temp\DatabaseBackups\TestDBMain_DiffB_1.bak' WITH DIFFERENTIAL

GO

--Step4:

--Insert Some more rows

--Step5:

BACKUP DATABASE TestDBMain TO DISK = 'C:\Temp\DatabaseBackups\TestDBMain_DiffB_2.bak' WITH DIFFERENTIAL

GO

--Step4 and Step5 Can be repeated as many time

-------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------

--Restore DB First Time

--Step1:

--Create TestDB1 using UI . Pointing MDF and LDF as shown below, And without 'NORECOVERY' command

--Step2:

RESTORE DATABASE TestDB1

  FROM DISK = 'C:\Temp\DatabaseBackups\TestDBMain_FullB.bak'

  WITH REPLACE,

  MOVE 'TestDBMain' TO 'c:\temp\DatabaseBackups\TestDB1\TestDB1.mdf',

  MOVE 'TestDBMain_log' TO 'c:\temp\DatabaseBackups\TestDB1\TestDB1.ldf';

GO

-------------------------------------------------------------------------------------------------------------------------------------

--Restore DB Second Time

--Step1:

--Create TestDB2 using UI . Pointing MDF and LDF as shown below, And with 'NORECOVERY' command

--Step2:

RESTORE DATABASE TestDB2

  FROM DISK = 'C:\Temp\DatabaseBackups\TestDBMain_FullB.bak'

  WITH NORECOVERY,REPLACE,

  MOVE 'TestDBMain' TO 'c:\temp\DatabaseBackups\TestDB2\TestDB2.mdf',

  MOVE 'TestDBMain_log' TO 'c:\temp\DatabaseBackups\TestDB2\TestDB2.ldf';

GO

--Step3:

RESTORE DATABASE TestDB2 FROM DISK = 'C:\Temp\DatabaseBackups\TestDBMain_DiffB_1.bak' WITH RECOVERY

GO

-------------------------------------------------------------------------------------------------------------------------------------

--Restore DB Third Time

--Step1:

--Create TestDB3 using UI . Pointing MDF and LDF as shown below, And with 'NORECOVERY' command

--Step2:

RESTORE DATABASE TestDB3

  FROM DISK = 'C:\Temp\DatabaseBackups\TestDBMain_FullB.bak'

  WITH NORECOVERY,REPLACE,

  MOVE 'TestDBMain' TO 'c:\temp\DatabaseBackups\TestDB3\TestDB3.mdf',

  MOVE 'TestDBMain_log' TO 'c:\temp\DatabaseBackups\TestDB3\TestDB3.ldf';

GO

--Step3:

RESTORE DATABASE TestDB3 FROM DISK = 'C:\Temp\DatabaseBackups\TestDBMain_DiffB_1.bak' WITH NORECOVERY

GO

--Step4:

RESTORE DATABASE TestDB3 FROM DISK = 'C:\Temp\DatabaseBackups\TestDBMain_DiffB_2.bak' WITH RECOVERY

GO

free hit counter