June 24, 2019 at 12:43 pm
Hi Team,
Please help me on this below metioned code.
Requirement: If we pass the DB name should take the Full or Diff or Tlog backups in deafult location where the data files existed in the same server. First it should check the DB size and drive space is avaialble then it create or disply the message space is not avaialble.
-- Declare variables
DECLARE @DBNAME VARCHAR(300)
--DECLARE @BackupPath VARCHAR(1000)
DECLARE @Count int
DECLARE @BackupName varchar(100)
DECLARE @BackupFile varchar(100)
DECLARE @backupType Varchar(100)
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
SET @DBName='Test'
SET @BackupPath='F:\Backup\'
select @Count=name from sys.databases where name=@DBName
IF (@Count>0)
BEGIN
--SET @sqlCommand = 'USE master
--GO
--BACKUP DATABASE '+@DBName+'
--TO DISK = N'+@BackupPath+'
--WITH CHECKSUM';
-- Set the current date and time n yyyyhhmmss format
SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' + 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'
-- 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
-- Execute the generated SQL command
EXEC(@sqlCommand)
END
ELSE
BEGIN
PRINT 'DB is not existed'
END
June 24, 2019 at 1:32 pm
I guess that your question is how do you check the space remaining on the drive? Try using the stored procedure xp_fixeddrives.
John
June 24, 2019 at 7:33 pm
It really isn't a good idea to put backups on the same drive/location as your data/log files. Not only will that cause performance issues - but if you lose that drive you also lose the ability to restore.
You can also query the backup directory location defined for that instance using xp_instance_regread. This will identify the location defined in the GUI for you - so you can then determine the rest of the path (if needed).
As for checking the space available - I would create a separate monitoring process using a powershell script and set that as the first job step in the agent job. You need to be able to figure out how large a typical backup file will be - and then see if there is enough space available...or, you check the available space and notify when the space available drops below a certain percentage of the drive space.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 27, 2019 at 11:22 am
Thanks Jhon and Jeffrey. I will check the backup directory location defined for that instance using xp_instance_regread.
I will try for the same and will update you.
Thanks once again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply