DBProject/UpdateScripts/Script006.sql - master - AstroJohn/MailRobot

DBProject/UpdateScripts/Script006.sql
100 строк2 KB
AstroJohn
1st commit
11 сен 2024, 13:31
11 сен 2024, 13:310f57dc5
Create Procedure dbo.MakeBackupNew
--<BeginHeader>-------------------------------------------------------------------------------------
-- <Description>   
-- <Group>         
-- <Creator>       Кузнецов Евгений
-- <CreateDate>    29.08.2019 15:11:00
-- <UpdateDate>    
-- <Parameters>

-- <Returns>

-- <Notes>
--   
-- <Modifications>
--   
-- <ToCopy>False<Checker>John<CopyDate>27.06.2013 02:02:05<Copier>John<Version>1<CopyOrder>
-- <CheckOut>False<CheckOutDate>23.11.2016 13:42<CheckOutAuthor>John
--<EndHeader>---------------------------------------------------------------------------------------
As

Begin

	Set Nocount On
	
	--+ Backup

	Declare @BackupPath Nvarchar(4000)

	Exec master.dbo.xp_instance_regread
		N'HKEY_LOCAL_MACHINE',
		N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',
		@BackupPath Output, 
		'no_output' 		
            
	If right(@BackupPath, 1) <> '\'
		Set @BackupPath = @BackupPath + '\'
		
	Declare @DBName Nvarchar(255)
	Set @DBName = db_name()
	
	Declare @DBFileName Varchar(255)
	
	Declare @pathName Nvarchar(512) 
	Set @pathName = @BackupPath + @DBName + '-' + convert(Varchar(8), getdate(), 112) + replace(convert(Varchar(8), getdate(), 8), ':', '') + '.bak' 
	
	Declare @backupName Nvarchar(512) 
	Set @backupName = @DBName + N' - Full Database Backup'
	
	Backup Database @DBName To Disk = @pathName
	With NOFORMAT, NOINIT, NAME = @backupName, SKIP, NOREWIND, Nounload, Stats = 10
	
	Declare @backupSetId As Int
	Select
		@backupSetId = position
	From msdb..backupset
	Where database_name = @DBName
	And backup_set_id = 
		(
			Select
				max(backup_set_id)
			From msdb..backupset
			Where database_name = @DBName
		)
		
	If @backupSetId Is Null
		Raiserror(N'Ошибка верификации. Сведения о резервном копировании не найдены.', 16, 1)
		
	Restore VERIFYONLY From  Disk = @pathName With  File = @backupSetId,  Nounload,  NOREWIND
	
	--/
	
	--+ Shrink
	
	Dbcc SHRINKDATABASE(@DBName)
	
	Declare @FileId As Int 
	
	Select Top 1 
		@FileId = file_id 
	From sys.database_files 
	Where [type] = 0
	
	If @FileId Is Not Null
		Dbcc SHRINKFILE (@FileId, 0, TRUNCATEONLY)	
	
	Select Top 1
		@FileId = file_id 
	From sys.database_files 
	Where [type] = 1
	
	If @FileId Is Not Null
		Dbcc SHRINKFILE (@FileId, 0, TRUNCATEONLY)
		
	Print 'Database ' + @DBName + ' has been shrunk.'		
	
	--/
			
	Return
		
End