T-SQL: Backup Databases
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
DecLare @server VARCHAR(150) --used for servername
--Specify Database Backup Directory
SET @path = 'C:\Downloads\DB_Backups\PC01\'
--Specify Filename Format
SET @fileDate = REPLACE(REPLACE(CONVERT(VARCHAR(20),GETDATE(),120), ' ', '-'), ':', '-')
DECLARE db_cursor CURSOR FOR
SELECT UPPER([Name])
FROM
master.sys.databases
Where [Name] not in ('TempDB') and state_desc = 'online'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '_FULL.BAK'
BACKUP DATABASE @name
TO DISK = @fileName
WITH NOFORMAT,
NOINIT,
SKIP,
NOREWIND,
NOUNLOAD,
--COMPRESSION,
STATS = 10
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
T-SQL: Return Backup History
--Script #1:
Declare @BackupType char(1) = 'L'
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
--msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
AND
msdb..backupset.type = @BackupType
AND
msdb.dbo.backupset.database_name = 'JDE_PRODUCTION'
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date DESC
--Script #2:
;WITH backups_list AS
(
Select
ROW_NUMBER() Over (Partition By b.database_name, b.type Order By b.database_name ASC, b.backup_finish_date DESC) AS 'RowNumber',
d.name AS [database_name],
b.backup_set_id,
b.media_set_id,
b.type AS backup_type,
b.backup_finish_date,
b.backup_start_date,
b.backup_size,
bms.is_compressed,
bms.is_encrypted,
d.recovery_model_desc,
b.user_name,
b.machine_name,
b.server_name
From Master.sys.databases AS d
Left Join Msdb.dbo.[backupset] AS b on d.name = b.database_name
Left Join Msdb.dbo.backupmediaset bms on b.media_set_id = bms.media_set_id
Where d.name not in ('TEMPDB')
)
Select
UPPER(bl.database_name) as 'Database',
bl.recovery_model_desc as 'RecoveryModel',
COALESCE(CONVERT(varchar(25),bl.backup_finish_date),'-') as'BackupFinish',
CASE bl.backup_type WHEN 'D' THEN 'DATABASE' WHEN 'I' THEN 'DIFFERENTIAL' WHEN 'L' THEN 'LOG' ELSE '-' END AS 'BackupType',
bl.Backup_Start_Date,
bl.Backup_Finish_Date,
DateDiff(s,bl.backup_start_date, bl.backup_finish_date) BackupDuration_Sec,
CONVERT(Decimal(18,2),bl.backup_size/1024/1024) as 'BackupSize_MB',
bl.Is_Compressed,
ISNULL(bl.Is_Encrypted,0) as 'Is_Encrypted',
COALESCE(CONVERT(varchar(250),m.physical_device_name),'-') as 'PhysicalDeviceName',
COALESCE(CONVERT(varchar(10),(DATEDIFF(day,bl.backup_finish_date, GETDATE()))),'-') AS 'Last_Backup_Days',
COALESCE(CONVERT(varchar(10),(DATEDIFF(hour,bl.backup_finish_date, GETDATE()))),'-') AS 'Last_Backup_Hours',
COALESCE(CONVERT(varchar(10),(DATEDIFF(minute,bl.backup_finish_date,GETDATE()))),'-') AS 'Last_Backup_Mins',
COALESCE(CONVERT(varchar(25),bl.server_name),'-') as 'Server',
COALESCE(CONVERT(varchar(25),bl.user_name),'-') as 'UserName'
From backups_list bl
Left Join msdb.dbo.backupmediafamily m ON bl.media_set_id = m.media_set_id
Where (bl.RowNumber = 1 and m.family_sequence_number = 1) or bl.backup_set_id is null
Order By bl.backup_finish_date ASC, bl.database_name ASC
|
|
|
|
|
|