Create Automated Daily Backups

Create Automated Daily Backups

The T_SQL script below shown will be used to create dialy snapshots

Declare  @Sql NVarchar(max),

             @Dbname Varchar(100) = 'SnapshotDB',  

-- database for creating snapshot

             @date varchar(30) ,

             @SnapPath Varchar(255) ,

             @snapshotname varchar(255),

             @drop NVarchar(max)

             

             set @date =  replace( convert(varchar,getdate(),101),'/','')

         set @SnapPath = 'D:\training_Data'+'_'+@date+'.ss' -- snapshot path to store

         set @snapshotname = @Dbname + '_SNAPSHOT_'+@date

         set @drop = 'DROP DATABASE '+ @snapshotname

         SET @Sql = 'CREATE DATABASE ' + @snapshotname + ' ON (

                  NAME = ' + @Dbname +',FILENAME = '''+ @SnapPath +''') AS SNAPSHOT OF '+ @Dbname

 IF EXISTS (select 1 from sys.databases where name = @snapshotname )

 BEGIN

   exec sp_executesql @drop 

   exec sp_executesql @Sql

 END      

  ELSE

      BEGIN 

         exec sp_executesql @Sql

        END

I will show how to create daily snapshots using jobs set by step.In order to execute job SQL server agent must be running.

Go to SQL server Agent Node

Right click on Job

Next Click on New Job. On General tab Provide Job name ,category, description

Next Go to Steps tab 

Click On New.Write the Step Name and select type Transact-SQL script (T-SQL).select database as mentioned in the above script.copy the above script.

select ok.and select the job step as quit the job on success as shwon below.

select schedules tab . click on new or pick. here i used the pick button.

Click on properties to select job schedule.

click ok. 

click on OK.Job created successfully. In order to test the job manually execute the job steps as shown below.

click on Start Job at Step

Daily snapshots created successfully. Refresh database snapshots folder.you can able see the snapshot.