Creating a custom database backup script to handle your database backup.
usp_bu_user_dbs
CREATE PROCEDURE [dbo].[usp_bu_user_dbs]
--===================
-- begin SP parameter list
--===================
@source_server varchar(255), -- where DB lives
@target_path varchar(255), -- path on target server
@db_name varchar(255), -- database to backup
@backup_type varchar(4) -- type of backup FULL/DIFF/LOG
AS
SET NOCOUNT ON
--===================
-- internal variables
--===================
DECLARE
@sql_stmt1 varchar(8000),
@sql_stmt2 varchar(8000),
@bk_file_name varchar(255),
@ansi_datetime varchar(12),
@file_extension varchar(4),
@file_name1 varchar(255),
@file_name2 varchar(255),
@file_name3 varchar(255),
@file_name4 varchar(255),
@descrip varchar(255),
@backup_name varchar(255)
--===================
-- constants
--===================
SET @file_extension = '.bak'
SET @descrip = @backup_type + 'Backup of ' + @db_name + ' on ' + CAST( GETDATE() AS varchar(25))
SET @backup_name = @backup_type + 'backup of ' + @db_name
SET @ansi_datetime =
CAST( YEAR(GETDATE()) AS varchar(4))
+ CASE
WHEN MONTH(GETDATE()) < 10 THEN '0' + CAST( MONTH(GETDATE()) AS varchar(1))
ELSE CAST( MONTH(GETDATE()) AS varchar(2))
END
+ CASE
WHEN DAY(GETDATE()) < 10 THEN '0' + CAST( DAY(GETDATE()) AS varchar(1))
ELSE CAST( DAY(GETDATE()) AS varchar(2))
END
+ CASE
WHEN DATEPART(hh, GETDATE()) < 10 THEN '0' + CAST( DATEPART(hh, GETDATE()) AS varchar(1))
ELSE CAST( DATEPART(hh, GETDATE()) AS varchar(2))
END
+ CASE
WHEN DATEPART(mi, GETDATE()) < 10 THEN '0' + CAST( DATEPART(mi, GETDATE()) AS varchar(1))
ELSE CAST( DATEPART(mi, GETDATE()) AS varchar(2))
END
SET @bk_file_name =
@source_server + '_'
+ @db_name + '_'
+ @ansi_datetime + '_'
+ CASE
WHEN @backup_type = 'FULL' THEN + 'FULL' + '_'
WHEN @backup_type = 'DIFF' THEN + 'DIFF' + '_'
WHEN @backup_type = 'LOG' THEN + 'LOG' + '_'
END
SET @file_name1 = @target_path + @bk_file_name + '01' + @file_extension
SET @file_name2 = @target_path + @bk_file_name + '02' + @file_extension
SET @file_name3 = @target_path + @bk_file_name + '03' + @file_extension
SET @file_name4 = @target_path + @bk_file_name + '04' + @file_extension
IF @backup_type = 'LOG'
BEGIN
SET @sql_stmt1 = 'BACKUP LOG ' + @db_name + ' TO '
+ ' DISK = ' + '''' + @file_name1 + ''''
+ ' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10, '
+ ' NAME = ' + '''' + @db_name + ' Transaction Log Backup' + ''''
SET @sql_stmt2 = 'RESTORE VERIFYONLY FROM '
+ 'DISK = ' + '''' + @file_name1 + ''''
+ ' WITH NOUNLOAD, NOREWIND'
END
IF @backup_type = 'DIFF'
BEGIN
SET @sql_stmt1 = 'BACKUP DATABASE '
+ @db_name + ' TO '
+ ' DISK = ' + '''' + @file_name1 + ''''
+ ' , DISK = ' + '''' + @file_name2 + ''''
+ ' WITH'
+ ' DIFFERENTIAL ,'
+ ' SKIP, NOREWIND, NOUNLOAD, STATS = 10, NOFORMAT, INIT,'
+ ' NAME = ' + '''' + @db_name + ' Differential Backup' + ''''
SET @sql_stmt2 = 'RESTORE VERIFYONLY FROM '
+'DISK = ' + '''' + @file_name1 + ''''
+ ' , DISK = ' + '''' + @file_name2 + ''''
+ ' WITH NOUNLOAD, NOREWIND'
END
IF @backup_type = 'FULL'
BEGIN
SET @sql_stmt1 = 'BACKUP DATABASE '
+ @db_name + ' TO '
+ ' DISK = ' + '''' + @file_name1 + ''''
+ ' , DISK = ' + '''' + @file_name2 + ''''
+ ' , DISK = ' + '''' + @file_name3 + ''''
+ ' , DISK = ' + '''' + @file_name4 + ''''
+ ' WITH SKIP, NOREWIND, NOUNLOAD, STATS = 10, NOFORMAT, INIT, '
+ ' NAME = ' + '''' + @db_name + ' Full Backup' + ''''
SET @sql_stmt2 = 'RESTORE VERIFYONLY FROM '
+'DISK = ' + '''' + @file_name1 + ''''
+ ' , DISK = ' + '''' + @file_name2 + ''''
+ ' , DISK = ' + '''' + @file_name3 + ''''
+ ' , DISK = ' + '''' + @file_name4 + ''''
+ ' WITH NOUNLOAD, NOREWIND'
END
PRINT @sql_stmt1
PRINT @sql_stmt2
EXECUTE (@sql_stmt1)
EXECUTE (@sql_stmt2)FULL Backup
--Full Backups
USE master
go
DECLARE @RC int
DECLARE @result int
DECLARE @source_server varchar(255)
DECLARE @target_path varchar(255)
DECLARE @db_name varchar(255)
DECLARE @backup_type varchar(4)
DECLARE @failed int
DECLARE @counter int
DECLARE @maxcount int
CREATE TABLE #AllDatabases
(
id INT IDENTITY,
Name NVARCHAR(128)
)
CREATE TABLE #FailedDatabases
(
id INT IDENTITY,
Name NVARCHAR(128)
)
INSERT INTO #AllDatabases
SELECT name
FROM master.sys.databases (NOLOCK)
WHERE name not in ('_db_utils', 'tempdb', 'model', 'master', 'msdb')
AND state_desc = 'ONLINE'
AND is_read_only = 0
--Uncomment next section for Multi-AG Servers
AND [name] NOT IN (
--Backups skipped on non-primary replicas
SELECT dc.database_name
FROM sys.availability_replicas r
JOIN sys.availability_databases_cluster dc ON dc.group_id = r.group_id
LEFT JOIN sys.dm_hadr_availability_group_states gs ON gs.group_id = r.group_id
AND gs.primary_replica = r.replica_server_name
WHERE gs.primary_replica IS NULL
AND r.replica_server_name = @@SERVERNAME )
ORDER BY [name]
--select * from #AllDatabases
SET @failed = 0
SET @counter = 1
SELECT @maxcount = MAX(id) FROM #AllDatabases
WHILE @counter <= @maxcount
BEGIN TRY
SELECT @db_name = Name FROM #AllDatabases WHERE id = @counter
SET @source_server = REPLACE (@@servername, '\', '_')
SET @target_path= 'D:\DBBackups\'
Set @backup_type = 'FULL'
EXECUTE @RC = .[dbo].[usp_bu_user_dbs]
@source_server
,@target_path
,@db_name
,@backup_type
SET @counter = @counter + 1
END TRY
BEGIN CATCH
INSERT INTO #FailedDatabases
(Name)
SELECT Name FROM #AllDatabases WHERE id = @counter
SET @counter = @counter + 1
END CATCH
DECLARE @ROWCOUNT Int
SET @ROWCOUNT = (select COUNT (*)
--select *
FROM #FailedDatabases)
PRINT @ROWCOUNT
IF @ROWCOUNT > 0
BEGIN
SELECT Name AS FailedDatabaseBackupName FROM #FailedDatabases
RAISERROR ('Backups failed for some databases. ' , 16, 1)
END
DROP TABLE #AllDatabases
DROP TABLE #FailedDatabases
Comments