BACKUP
MAX(b.backup_finish_date) AS BackUpTime,
case when b.type = 'D' then 'Full'
when b.type = 'I' then 'Differential'
when b.type = 'L' then 'Log'
when b.type = 'F' then 'File or filegroup'
when b.type = 'G' then 'Differential file'
when b.type = 'P' then 'Partial'
when b.type = 'Q' then 'Differential partial'
when b.type is NULL then 'N/A'
else 'Other'
end as Backup_type
FROM sys.sysdatabases s
LEFT OUTER JOIN msdb.dbo.backupset b ON b.database_name = s.name
GROUP BY s.Name, b.type
ORDER BY Database_Name
Database Büyüklükleri
EXEC sp_databases
select instance_name, counter_name, cntr_value as size_in_kb
from sys.dm_os_performance_counters
where counter_name in ('Data File(s) Size (KB)', 'Log File(s) Size (KB)')
EXEC BackupDbWithTs
@db_name = 'FS'
,@folder = 'C:\Temp'
,@backup_type = 'FULL'
,@with_checksum = 'Y'
,@do_verification = 'Y'
,@copy_only = 'N'
USE [DENEME]
GO
/****** Object: StoredProcedure [dbo].[BackupDbWithTs] Script Date: 12.09.2017 01:22:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[BackupDbWithTs]
@db_name sysname
,@folder nvarchar(255)
,@backup_type varchar(13)
,@with_checksum char(1) = 'Y'
,@do_verification char(1) = 'Y'
,@copy_only char(1) = 'N'
AS
DECLARE
@sql nvarchar(4000)
,@filename nvarchar(255)
,@full_path_and_filename nvarchar(1000)
,@err_msg nvarchar(2000)
,@crlf varchar(2) = CHAR(13) + CHAR(10)
,@backup_extension varchar(10)
SET @backup_type = RTRIM(@backup_type)
--Verify valid backup type
IF @backup_type NOT IN('FULL', 'LOG', 'DIFF')
BEGIN
SET @err_msg = 'Backup type ' + @backup_type + ' is not valid.
Allowed values are FULL, LOG and DIFF'
RAISERROR(@err_msg, 16, 1)
RETURN -101
END
--Make sure folder name ends with '\'
IF RIGHT(@folder, 1) <> '\'
SET @folder = @folder + '\'
--Set file extension
IF @backup_type IN('FULL', 'DIFF')
SET @backup_extension = '.bak'
ELSE
SET @backup_extension = '.trn'
--Construct filename
SET @filename = @db_name + '_backup_' + REPLACE(REPLACE(REPLACE(CONVERT(CHAR(19), CURRENT_TIMESTAMP, 120), '-', '_'), ' ', '__'), ':', '_')
--RAISERROR(@filename, 10, 1) WITH NOWAIT
--Construct full path and file name
SET @full_path_and_filename = @folder + @filename + '_' + @backup_type + IIF(@copy_only = 'Y', '_COPY_ONLY', '') + @backup_extension
--Construct backup command
SET @sql = 'BACKUP ' + CASE @backup_type WHEN 'LOG' THEN 'LOG' ELSE 'DATABASE' END + ' ' + QUOTENAME(@db_name) + @crlf
SET @sql = @sql + 'TO DISK = ' + QUOTENAME(@full_path_and_filename,'''') + @crlf
SET @sql = @sql + 'WITH' + @crlf
SET @sql = @sql + ' NOINIT,' + @crlf
SET @sql = @sql + ' NAME = ' + QUOTENAME(@filename,'''') + ',' + @crlf
IF @backup_type = 'DIFF'
SET @sql = @sql + ' DIFFERENTIAL,' + @crlf
IF @with_checksum <> 'N'
SET @sql = @sql + ' CHECKSUM,' + @crlf
IF @copy_only = 'Y'
SET @sql = @sql + ' COPY_ONLY,' + @crlf
--Add other backup options below if you want to
--Remove trailing comma and CRLF
SET @sql = LEFT(@sql, LEN(@sql) - 3)
--PRINT @sql
EXEC(@sql)
IF @do_verification = 'Y'
RESTORE VERIFYONLY FROM DISK = @full_path_and_filename
/*
KULLANIMI
EXEC BackupDbWithTs
@db_name = 'FS'
,@folder = 'C:\Temp'
,@backup_type = 'FULL'
,@with_checksum = 'Y'
,@do_verification = 'Y'
,@copy_only = 'N'
*/
Hiç yorum yok