Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server transaction log backup procedure using batch file. Expand / Collapse
Author
Message
Posted Saturday, June 15, 2013 3:33 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 21, 2014 12:41 PM
Points: 65, Visits: 234
Hi Guys,

Please provide sql server transaction log backup procedure to perform the backup automatically using batch file and
that job will be run using Windows task scheduler.

Please do not give third party tool reference i want it only by using batch file.

Thanks........Reply
Post #1463839
Posted Saturday, June 15, 2013 7:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:36 AM
Points: 198, Visits: 668
Why not use SQL Server Agent? XE?


Dird // Junior DBA
11g OCA
10.5 newbie
Post #1463849
Posted Saturday, June 15, 2013 10:15 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 21, 2014 12:41 PM
Points: 65, Visits: 234
we have sql server express edition and we have to schedule it to take transaction log backup automatically.

thnx......reply
Post #1463872
Posted Saturday, June 15, 2013 11:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:36 AM
Points: 198, Visits: 668
Something like this will work:

A .bat file containing the following:
sqlcmd -S SERVERNAME\INSTANCENAME -E -d master -e -b -i d:\mssql\backup\backup_tlogs.sql -o d:\mssql\backup\backup_tlogs.log

Then in backup_tlogs.sql you would have:
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 = 'D:\mssql\Backup\'

-- set up backup file suffix
SELECT @fileDate = datepart(dw,getdate()) -- day of week number 1 = Sun, 5 = Thu etc

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE recovery_model_desc != 'SIMPLE'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP LOG @name TO DISK = @fileName WITH INIT

FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor

This will keep 7 days worth of transaction logs; overwriting them as the week restarts. If you want to keep it for longer you'd want to play around with the date section of the filename.



Dird // Junior DBA
11g OCA
10.5 newbie
Post #1463884
Posted Saturday, June 15, 2013 1:09 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 21, 2014 12:41 PM
Points: 65, Visits: 234
Thanks...

Step goes like this right:
1. Save in notepad sqlcmd sript as .bat file

2. After that sql script of backup_tlogs.sql i have to save in notepad OR i have to save in sql query analyzer ?

Also we have user database will it work after searching thru different instances ?

please reply......
Post #1463892
Posted Saturday, June 15, 2013 1:30 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:36 AM
Points: 198, Visits: 668
Saggy (6/15/2013)

1. Save in notepad sqlcmd sript as .bat file

Yes.
Saggy (6/15/2013)

2. After that sql script of backup_tlogs.sql i have to save in notepad OR i have to save in sql query analyzer ?


Save this in notepad also (make sure the "Save as type" is not .txt; same with the .bat).
This .sql should be stored in "d:\mssql\backup\" or if you put it somewhere else you will need to update the locations in the .bat file.

Saggy (6/15/2013)

Also we have user database will it work after searching thru different instances ?


The script will backup the transaction logs of all full/bulk_logged databases in "SERVERNAME\INSTANCENAME" specified in the .bat file (you will need to change this to the name of the server & instance).
If you have multiple instances will different databases on the same server then you will need to make further changes. I guess if you duplicate the line from the .bat file it may work that way e.g. if you have 2 instances on server SVR01 that are named INST01 and INST02 then your .bat file would look something like this:
sqlcmd -S SVR01\INST01 -E -d master -e -b -i d:\mssql\backup\backup_tlogs.sql -o d:\mssql\backup\backup_tlogs.log
sqlcmd -S SVR01\INST02 -E -d master -e -b -i d:\mssql\backup\backup_tlogs.sql -o d:\mssql\backup\backup_tlogs.log

If you wanted to do this then you would (at least) need to alter 1 line of the .sql file so that databases with the same name from different instances didn't overwrite each other. To do this change:
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

To:
SET @fileName = @path + @@servicename + '_' + @name + '_' + @fileDate + '.BAK'

To keep things cleaner you could store the backups in separate folders (per instance) but then you would need one .sql file per instance so I think just adding the instance name is easier.

Edit: Actually, how often do you plan to run this backup? The code I provided only works if the transaction log backup is once per day actually :x
I'm guessing you plan to backup the transaction logs more regularly?
How big are the databases? How often do you backup the databases?



Dird // Junior DBA
11g OCA
10.5 newbie
Post #1463896
Posted Sunday, June 16, 2013 12:39 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 21, 2014 12:41 PM
Points: 65, Visits: 234
We are performing transaction log backup when sql server user database or system database threshold value goes above 90 %.

when we found database us above 90 % we manually perform transaction log backup so that it gets down i.e. below 90% and we have thousands of different user databases on different instances on different-different servers to perform log backup .

Also majorly we have by-default server i.e. MSSQLSERVER which is express edition and we perform transaction log backup frequently so it does not affect performance.

Also tell me where i have to give the polling for you (Dird) so that your points can increase.

thnx....reply
Post #1463927
Posted Sunday, June 16, 2013 3:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:36 AM
Points: 198, Visits: 668
I have no idea how the "points" works on this website. It seems to just be "posts" unlike on the Oracle forums where you receive points for correct/helpful answers o:

If they all have the default instance then you can use the original version of the code I provided. However, you will have to change the backup from "WITH INIT" to "WITH NOINIT" if you will do regular transaction log backups. This will append to the file instead of overwriting.

You will also want to create the following .bat file to delete the old (otherwise it will append forever & you'll run out of space). Schedule to run once per day in the morning:
rem
rem Log the output to a file
rem
:clean_main
call :cleanup >> cleanup.log


rem
rem Delete .bak files older than 6 days.
rem
:cleanup
forfiles /M *.bak /D -6 /c "cmd /c echo Deleting @file && del /Q @file"

:end

You will need to place this script in the folder that the transaction log backups will be stored. It will keep 6 days (e.g. if today is Tuesday it will delete Wednesday's file before tomorrow) worth of backups so you should keep your transaction log backups in a different folder to your database backups...just in case you have weekly backups (it would get deleted before a new one is taken) or daily backups & don't need that many days of backups.

If you do daily backups then this script could be used for both. e.g. if you keep 3 days of daily backups then you can also keep 3 days of transaction log backups by changing the 6 to a 3.

It outputs a log. After the first 7-10 days when you've seen from the log that it has been deleting files successfully then you can remove this part of the .bat script.

Note: this script may not work for all of your servers becase of the forfiles command. According to here you can download it from here for older OS versions. but there's a slight syntax change I think. What OSs do your SQL Servers run on?



Dird // Junior DBA
11g OCA
10.5 newbie
Post #1463932
Posted Sunday, June 16, 2013 4:25 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 21, 2014 12:41 PM
Points: 65, Visits: 234
We are having windows server 2003 , 2008 ,2008 R2 and also SBS server.

thnx..........reply
Post #1463935
Posted Sunday, June 16, 2013 4:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 4:36 AM
Points: 198, Visits: 668
2008 & 2008r2 will work. You will have to test it on 2003/sbs. if it doesn't work then try downloading that file mentioned in the URL.


Dird // Junior DBA
11g OCA
10.5 newbie
Post #1463936
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse