mirror of https://github.com/bvn13/PyBackuper.git
130 lines
4.1 KiB
Plaintext
130 lines
4.1 KiB
Plaintext
USE [master]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[sp_BackupDatabases] Script Date: 02.06.2016 16:03:38 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: Microsoft
|
|
-- Create date: 2010-02-06
|
|
-- Description: Backup Databases for SQLExpress
|
|
-- Parameter1: databaseName
|
|
-- Parameter2: backupType F=full, D=differential, L=log
|
|
-- Parameter3: backup file location
|
|
-- =============================================
|
|
|
|
ALTER PROCEDURE [dbo].[sp_BackupDatabases]
|
|
@databaseName sysname = null,
|
|
@backupType CHAR(1),
|
|
@backupLocation nvarchar(200)
|
|
--,@fileName nvarchar out
|
|
AS
|
|
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @DBs TABLE
|
|
(
|
|
ID int IDENTITY PRIMARY KEY,
|
|
DBNAME nvarchar(500)
|
|
)
|
|
|
|
-- Pick out only databases which are online in case ALL databases are chosen to be backed up
|
|
-- If specific database is chosen to be backed up only pick that out from @DBs
|
|
INSERT INTO @DBs (DBNAME)
|
|
SELECT Name FROM master.sys.databases
|
|
where state=0
|
|
AND name=@DatabaseName
|
|
OR @DatabaseName IS NULL
|
|
ORDER BY Name
|
|
|
|
-- Filter out databases which do not need to backed up
|
|
IF @backupType='F'
|
|
BEGIN
|
|
DELETE @DBs where DBNAME IN ('tempdb')
|
|
END
|
|
ELSE IF @backupType='D'
|
|
BEGIN
|
|
DELETE @DBs where DBNAME IN ('tempdb','master','ou_test')
|
|
END
|
|
ELSE IF @backupType='L'
|
|
BEGIN
|
|
DELETE @DBs where DBNAME IN ('tempdb','master','ou_test')
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
RETURN
|
|
END
|
|
|
|
-- Declare variables
|
|
DECLARE @BackupName varchar(100)
|
|
DECLARE @BackupFile varchar(100)
|
|
DECLARE @DBNAME varchar(300)
|
|
DECLARE @sqlCommand NVARCHAR(1000)
|
|
DECLARE @dateTime NVARCHAR(20)
|
|
DECLARE @Loop int
|
|
DECLARE @fileName nvarchar(4000)
|
|
|
|
-- Loop through the databases one by one
|
|
SELECT @Loop = min(ID) FROM @DBs
|
|
|
|
SET @fileName = ''
|
|
SET @BackupFile = ''
|
|
|
|
WHILE @Loop IS NOT NULL
|
|
BEGIN
|
|
|
|
-- Database Names have to be in [dbname] format since some have - or _ in their name
|
|
SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'
|
|
|
|
-- Set the current date and time n yyyyhhmmss format
|
|
SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),102),'.','') + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')
|
|
|
|
-- Create backup filename in path\filename.extension format for full,diff and log backups
|
|
IF @backupType = 'F'
|
|
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.bak'
|
|
ELSE IF @backupType = 'D'
|
|
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.bak'
|
|
ELSE IF @backupType = 'L'
|
|
SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.trn'
|
|
|
|
--PRINT @BackupFile
|
|
SET @fileName = @fileName+ ','+ @BackupFile;
|
|
--PRINT @fileName;
|
|
|
|
-- Provide the backup a name for storing in the media
|
|
IF @backupType = 'F'
|
|
SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
|
|
IF @backupType = 'D'
|
|
SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
|
|
IF @backupType = 'L'
|
|
SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime
|
|
|
|
-- Generate the dynamic SQL command to be executed
|
|
|
|
IF @backupType = 'F'
|
|
BEGIN
|
|
SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
|
|
END
|
|
IF @backupType = 'D'
|
|
BEGIN
|
|
SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
|
|
END
|
|
IF @backupType = 'L'
|
|
BEGIN
|
|
SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
|
|
END
|
|
|
|
--PRINT @sqlCommand
|
|
-- Execute the generated SQL command
|
|
EXEC(@sqlCommand)
|
|
|
|
-- Goto the next database
|
|
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
|
|
|
|
END
|
|
|
|
SET @fileName = SUBSTRING(@fileName, 2, LEN(@fileName)-1)
|
|
PRINT 'RESULT:'
|
|
PRINT @fileName; |