Sql Server Backup With Scripting

 

Sql Server Backup With Scripting

 

 

You can manage your backups with stored procedures. I create 3 different sp and create 3 jobs to backup in the proper time.

 

Just create sp and put this  sql into job:

exec dbo.FullBackup 'DBName','X:\BackUp\'

 

 

---1. Full backup

CREATE procedure [dbo].[FullBackup]

(

@databaseName    nvarchar(100),

@backupDirectory nvarchar(100)

)

as

BEGIN

declare

@backupFileName varchar(100),

-- @backupDirectory varchar(100),  

 @databaseDataFilename varchar(100), @databaseLogFilename varchar(100),  

 @databaseDataFile varchar(100), @databaseLogFile varchar(100)  

--, @databaseName varchar(100)

, @execSql varchar(1000)   

 

-- Set the path fo the backup directory on the sql server pc  

----set @backupDirectory = 'X:\BackUp\Differential\' -- such as 'c:\temp\'  

 

-- Create the backup file name based on the backup directory, the database name and today's date  

set @backupFileName = @backupDirectory +  'FULL_'+@databaseName + '_' + replace(convert(varchar, getdate(), 111), '/', '.') +

  +'_'+replace(convert(varchar, getdate(), 108), ':', '_') + '.bak' 

 

-- Get the data file and its path  

select @databaseDataFile = rtrim([Name]),  

 @databaseDataFilename = rtrim([Filename])  

from master.dbo.sysaltfiles as files  

 inner join 

 master.dbo.sysfilegroups as groups  

 on 

 files.groupID = groups.groupID  

where DBID = (  

  select dbid  

  from master.dbo.sysdatabases  

  where [Name] = @databaseName  

 )  

 

-- Get the log file and its path  

select @databaseLogFile = rtrim([Name]),  

 @databaseLogFilename = rtrim([Filename])  

from master.dbo.sysaltfiles as files  

where DBID = (  

  select dbid  

  from master.dbo.sysdatabases  

  where [Name] = @databaseName  

 )  

 and 

 groupID = 0  

 

print 'Backing up "' + @databaseName + '" database to "' + @backupFileName + '" with ' 

print '  data file "' + @databaseDataFile + '" located at "' + @databaseDataFilename + '"' 

print '  log file "' + @databaseLogFile + '" located at "' + @databaseLogFilename + '"' 

 

set @execSql = '  

backup database [' + @databaseName + ']  

to disk = ''' + @backupFileName + ''' 

with 

  noformat,  

  noinit,  

  name = ''' + @databaseName + ' backup'',  

  norewind,  

  nounload,  

  skip'  

--  select @execSql

exec(@execSql)

END

 

---2. Differantial backup

CREATE procedure [dbo].[DiffBackup]

(

@databaseName    nvarchar(100),

@backupDirectory nvarchar(100)

)

as

BEGIN

declare

@backupFileName varchar(100),

-- @backupDirectory varchar(100),  

 @databaseDataFilename varchar(100), @databaseLogFilename varchar(100),  

 @databaseDataFile varchar(100), @databaseLogFile varchar(100)  

--, @databaseName varchar(100)

, @execSql varchar(1000)  

 

-- Set the name of the database to backup  

----set @databaseName = 'IBTF' 

-- Set the path fo the backup directory on the sql server pc  

----set @backupDirectory = 'X:\BackUp\Differential\' -- such as 'c:\temp\'  

 

-- Create the backup file name based on the backup directory, the database name and today's date  

set @backupFileName = @backupDirectory + 'DIFF_'+@databaseName + '_' + replace(convert(varchar, getdate(), 111), '/', '.') +

  +'_'+replace(convert(varchar, getdate(), 108), ':', '_') + '.bak' 

 

-- Get the data file and its path  

select @databaseDataFile = rtrim([Name]),  

 @databaseDataFilename = rtrim([Filename])  

from master.dbo.sysaltfiles as files  

 inner join 

 master.dbo.sysfilegroups as groups  

 on 

 files.groupID = groups.groupID  

where DBID = (  

  select dbid  

  from master.dbo.sysdatabases  

  where [Name] = @databaseName  

 )  

 

-- Get the log file and its path  

select @databaseLogFile = rtrim([Name]),  

 @databaseLogFilename = rtrim([Filename])  

from master.dbo.sysaltfiles as files  

where DBID = (  

  select dbid  

  from master.dbo.sysdatabases  

  where [Name] = @databaseName  

 )  

 and 

 groupID = 0  

 

print 'Backing up "' + @databaseName + '" database to "' + @backupFileName + '" with ' 

print '  data file "' + @databaseDataFile + '" located at "' + @databaseDataFilename + '"' 

print '  log file "' + @databaseLogFile + '" located at "' + @databaseLogFilename + '"' 

 

 

set @execSql = '  

BACKUP DATABASE [' + @databaseName + ']  

TO  DISK = ''' + @backupFileName + ''' 

WITH  DIFFERENTIAL , NOFORMAT, NOINIT,

  name = ''' + @databaseName + ' backup'',  

SKIP, NOREWIND, NOUNLOAD,  STATS = 10'

  

--  select @execSql

exec(@execSql)

END

 

---3. Transaction Log Backup

CREATE procedure [dbo].[TranBackup]

(

@databaseName    nvarchar(100),

@backupDirectory nvarchar(100)

)

as

BEGIN

declare

@backupFileName varchar(100),

-- @backupDirectory varchar(100),  

 @databaseDataFilename varchar(100), @databaseLogFilename varchar(100),  

 @databaseDataFile varchar(100), @databaseLogFile varchar(100)  

--, @databaseName varchar(100)

, @execSql varchar(1000)  

 

-- Set the name of the database to backup  

----set @databaseName = 'IBTF' 

-- Set the path fo the backup directory on the sql server pc  

----set @backupDirectory = 'X:\BackUp\Differential\' -- such as 'c:\temp\'  

 

-- Create the backup file name based on the backup directory, the database name and today's date  

set @backupFileName = @backupDirectory + 'TRANSACTION_'+@databaseName + '_' + replace(convert(varchar, getdate(), 111), '/', '.') +

  +'_'+replace(convert(varchar, getdate(), 108), ':', '_') + '.trn' 

 

-- Get the data file and its path  

select @databaseDataFile = rtrim([Name]),  

 @databaseDataFilename = rtrim([Filename])  

from master.dbo.sysaltfiles as files  

 inner join 

 master.dbo.sysfilegroups as groups  

 on 

 files.groupID = groups.groupID  

where DBID = (  

  select dbid  

  from master.dbo.sysdatabases  

  where [Name] = @databaseName  

 )  

 

-- Get the log file and its path  

select @databaseLogFile = rtrim([Name]),  

 @databaseLogFilename = rtrim([Filename])  

from master.dbo.sysaltfiles as files  

where DBID = (  

  select dbid  

  from master.dbo.sysdatabases  

  where [Name] = @databaseName  

 )  

 and 

 groupID = 0  

 

print 'Backing up "' + @databaseName + '" database to "' + @backupFileName + '" with ' 

print '  data file "' + @databaseDataFile + '" located at "' + @databaseDataFilename + '"' 

print '  log file "' + @databaseLogFile + '" located at "' + @databaseLogFilename + '"' 

 

 

set @execSql = '  

BACKUP LOG  [' + @databaseName + ']  

TO  DISK = ''' + @backupFileName + ''' 

WITH NOFORMAT, NOINIT, 

  name = ''' + @databaseName + ' Transaction Log  Backup'',  

SKIP, NOREWIND, NOUNLOAD,  STATS = 10'

  

--  select @execSql

exec(@execSql)

 

END