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
