September 26, 2012 at 11:56 am
Hi All,
Need assistance in creating a script for the below requirement.
Any assistance to satisfy this requirement will be greatly appreciated.
Due to the mirror being deployed, we need a script to backup database transaction log. This request is only for version SQL server 2008 and over
Key Items needed in script:
· Determine where the current transaction log resides and write trans log to this file directory.
· Script has to delete older transaction logs after backup transaction logs have been created
· If transaction log is greater than space available do not run backup, but send email to DBA “An attempt to create a transaction backup has failed due to lack of space.”
Let me know if you need more details or is this enough information to fulfill the above requirement.
Thanks,
SueTons
Regards,
SQLisAwe5oMe.
September 27, 2012 at 7:21 am
Have a look at Ola Hallengren's Database Backup script. It does what you're wanting and if setup in a SQL Agent job you can send an email if the job fails for any reason.
If you want to have an email when a backup fails regardless of what kicked it off you can setup a SQL Agent alert that sends an email based on that specific error message.
http://ola.hallengren.com/scripts/DatabaseBackup.sql
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 1, 2012 at 6:28 am
opc.three (9/27/2012)
Have a look at Ola Hallengren's Database Backup script. It does what you're wanting and if setup in a SQL Agent job you can send an email if the job fails for any reason.If you want to have an email when a backup fails regardless of what kicked it off you can setup a SQL Agent alert that sends an email based on that specific error message.
What could be advantage hallengren database maintainance script instead of using builtin Management plan?
October 1, 2012 at 6:53 am
What could be advantage hallengren database maintainance script instead of using builtin Management plan?
[/quote]
You mean I can complete my requirement using SQL server maintenance plan?
Regards,
SueTons
Regards,
SQLisAwe5oMe.
October 1, 2012 at 7:11 am
SQLCrazyCertified (9/26/2012)
· Determine where the current transaction log resides and write trans log to this file directory.
Why do you want to write the log backups to the folder that contains the tran log? Along with potentially causing space issues, this means that if you ever lose the drive that has the tran logs on, you lose all your log backups too.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2012 at 9:13 am
ananda.murugesan (10/1/2012)
opc.three (9/27/2012)
Have a look at Ola Hallengren's Database Backup script. It does what you're wanting and if setup in a SQL Agent job you can send an email if the job fails for any reason.If you want to have an email when a backup fails regardless of what kicked it off you can setup a SQL Agent alert that sends an email based on that specific error message.
What could be advantage hallengren database maintainance script instead of using builtin Management plan?
The built-in management plan will rebuild all indexes regardless of their fragmentation level. Ola's script inspects the level of fragmentation of each index and only rebuilds or reorganizes the indexes that require it per a fragmentation-threshold you set. The default of 30% for rebuild and 5% for reorganize is an acceptable place to start.
The bottom line is that Ola's script is more efficient in what it does and has far less impact on the transaction log after you have run it the initial time and allowed it to maintain any indexes that hadn't been maintained in a while. For most of the databases I have implemented his script on I run the script every night because at most I'll only see a handful of indexes be rebuilt or reorganized and running it every night keeps that number down so I do not leave a bunch of index work to do for the weekend. It also helps keep the indexes healthier by maintaining them as soon as they need it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 1, 2012 at 3:11 pm
GilaMonster (10/1/2012)
SQLCrazyCertified (9/26/2012)
· Determine where the current transaction log resides and write trans log to this file directory.Why do you want to write the log backups to the folder that contains the tran log? Along with potentially causing space issues, this means that if you ever lose the drive that has the tran logs on, you lose all your log backups too.
Hi Gail, I believe it should have said, "Determine where the transaction log backup resides and write the tlog backup to that directory"
I have to double check....I will get back to you.
Thanks,
SueTons.
Regards,
SQLisAwe5oMe.
October 2, 2012 at 7:49 am
I'll add another vote for Ola's method. Unless you know what you are doing and carefully configure your maintenance plans rather than just taking the defaults, you'll end up doing wasted work on all your indexes such as rebuilding (which effectively reorganises) after reorganising.
And Ola's method is far simpler to set up - run a script and then create a job or two. Once you've done it the first time you can script each job creation to a .sql file and then when you set up the next server just tweak the script for folders etc. and run it. It also helps to have a standard for server building - same drive letters (d:, l:, j:, b: etc.) used for data, trans logs, job logs and backups throughout your servers.
I also add a cmdexec jobstep in the agent job after the backup steps are complete to copy all backup files to another network location. This includes the in-day transaction log backups so we have full recovery if we lose a server.
My favourite thing about these methods is that they pick up any new databases and logs that have been created without your knowledge, for example by Sharepoint, and backs them up properly.
October 2, 2012 at 1:58 pm
SQLCrazyCertified (10/1/2012)
GilaMonster (10/1/2012)
SQLCrazyCertified (9/26/2012)
· Determine where the current transaction log resides and write trans log to this file directory.Why do you want to write the log backups to the folder that contains the tran log? Along with potentially causing space issues, this means that if you ever lose the drive that has the tran logs on, you lose all your log backups too.
Hi Gail, I believe it should have said, "Determine where the transaction log backup resides and write the tlog backup to that directory"
I have to double check....I will get back to you.
Thanks,
SueTons.
Gail,
I have verified that the statement should have said "Determine where the transaction log backup resides and write the tlog backup to that directory".
Thanks,
SueTons.
Regards,
SQLisAwe5oMe.
October 2, 2012 at 2:03 pm
opc.three (9/27/2012)
Have a look at Ola Hallengren's Database Backup script. It does what you're wanting and if setup in a SQL Agent job you can send an email if the job fails for any reason.If you want to have an email when a backup fails regardless of what kicked it off you can setup a SQL Agent alert that sends an email based on that specific error message.
I really cannot follow most of the things in this script. Can someone please explain what's the summary of this script is doing?.....I am not very good with scripting.
Also, can Ola's script suffice my requirement?....or is it little too much for my requirement.
Thanks,
SueTons.
Regards,
SQLisAwe5oMe.
October 4, 2012 at 9:38 am
ananda.murugesan (10/1/2012)
opc.three (9/27/2012)
Have a look at Ola Hallengren's Database Backup script. It does what you're wanting and if setup in a SQL Agent job you can send an email if the job fails for any reason.If you want to have an email when a backup fails regardless of what kicked it off you can setup a SQL Agent alert that sends an email based on that specific error message.
What could be advantage hallengren database maintainance script instead of using builtin Management plan?
· Determine where the current transaction log backup files resides and write trans log to this file directory........I can I achieve this using Maintenance Plan
· Script has to delete older transaction logs after backup transaction logs have been created......I can I achieve this using Maintenance Plan
· If transaction log is greater than space available do not run backup, but send email to DBA “An attempt to create a transaction backup has failed due to lack of space.”....
Can someone please advise if this also can he achieved using maintenance plans as well......I know we can set up alerts.....but is there a script to check the available space before running the backup and if there is not enough space then send email......is this possible?....someone please advise.
Thanks,
SueTons
Regards,
SQLisAwe5oMe.
October 4, 2012 at 9:57 am
Honestly, you shouldn't be using your backup plans to monitor for drive space. There should be existing alerts and the DBA should have known long before a log backup would fail that the drive is low on space.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 5, 2012 at 11:25 am
SQLCrazyCertified (9/26/2012)
Hi All,Need assistance in creating a script for the below requirement.
Any assistance to satisfy this requirement will be greatly appreciated.
Due to the mirror being deployed, we need a script to backup database transaction log. This request is only for version SQL server 2008 and over
Key Items needed in script:
· Determine where the current transaction log backup resides and write trans log to this file directory.
· Script has to delete older transaction logs after backup transaction logs have been created
· If transaction log is greater than space available do not run backup, but send email to DBA “An attempt to create a transaction backup has failed due to lack of space.”
Let me know if you need more details or is this enough information to fulfill the above requirement.
Thanks,
SueTons
This what I found/put together.....I want to create a job with 3 steps and use the codes below. Can someone review these code and advise if this will work for my requirement above?
------------------------------------------------------------------------------------------
--// Transaction Log Backup User Databases where recovery model = Full/Bulk\\--
------------------------------------------------------------------------------------------
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
SET @path = '\\ServerName\DriveName\FolderName\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
+ '_'
+ REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
AND DATABASEPROPERTYEX(name, 'Recovery') IN ('FULL','BULK_LOGGED')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.TRN'
BACKUP LOG @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
--// Script to check the free space on the drive and send alert when free space drops
below particular threshold setting for the drive \\--
------------------------------------------------------------------------------------------
declare @MB_Free int
create table #FreeSpace(
Drive char(1),
MB_Free int)
insert into #FreeSpace exec xp_fixeddrives
select @MB_Free = MB_Free from #FreeSpace where Drive = 'C'
-- Free Space on C drive Less than Threshold
if @MB_Free < 1024
exec master.dbo.xp_sendmail
@recipients ='youremailaddress.com',
@subject ='ServerName - Free Space Issue on C Drive',
@message = 'Free space on C Drive
has dropped below 1 gb'
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
--// Maintenance Cleanup Task (Delete backup Files, Search Folder(Folder Path)
and delete files based on an extention(trn), Delete files older than 2 weeks.\\--
------------------------------------------------------------------------------------------
EXECUTE master.dbo.xp_delete_file 0
,N'D:\MSSQL\BACKUP'--Folder path to be deleted.
,N'trn' --Delete file names start with trn extention
,N'2012-09-20T10:13:42' --Delete trn files older than 2 weeks.
------------------------------------------------------------------------------------------
Regards,
SQLisAwe5oMe.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply