angel angel - 6 months ago 11
SQL Question

How to create (with sql code) a full , differential and transaction log backups


I want to generate 3 backups scripts, a full backup, a differential
backup and a transaction log backup.

assuming we are using northwind. how to generate those scripts?

I would appreaciate it was step by step.

Answer

this is how to do the full backup:

USE AdventureWorks2008R2;
GO
BACKUP DATABASE AdventureWorks2008R2
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2008R2.Bak'
--optional
   WITH FORMAT,
      MEDIANAME = 'Z_SQLServerBackups',
      NAME = 'Full Backup of AdventureWorks2008R2';
GO

this is the log backup:

BACKUP LOG MyAdvWorks_FullRM
   TO MyAdvWorks_FullRM_log1;
GO

when you have a full backup you can use the backup device to perform deferential backup:

-- Create a full database backup first.
BACKUP DATABASE MyAdvWorks 
   TO MyAdvWorks_1 
   WITH INIT
GO
-- Time elapses.
-- Create a differential database backup, appending the backup
-- to the backup device containing the full database backup.
BACKUP DATABASE MyAdvWorks
   TO MyAdvWorks_1
   WITH DIFFERENTIAL
GO

it's really all there in the MSDN:
https://technet.microsoft.com/en-us/library/ms191304(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/ms191284(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/ms191180(v=sql.105).aspx

it will also have more information about performing backups to different types of medias

Comments