June 15, 2013 at 3:33 am
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
June 15, 2013 at 7:16 am
Why not use SQL Server Agent? XE?
June 15, 2013 at 10:15 am
we have sql server express edition and we have to schedule it to take transaction log backup automatically.
thnx......reply
June 15, 2013 at 11:25 am
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.
June 15, 2013 at 1:09 pm
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......
June 15, 2013 at 1:30 pm
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 😡
I'm guessing you plan to backup the transaction logs more regularly?
How big are the databases? How often do you backup the databases?
June 16, 2013 at 12:39 am
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
June 16, 2013 at 3:26 am
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[/url] for older OS versions. but there's a slight syntax change I think. What OSs do your SQL Servers run on?
June 16, 2013 at 4:25 am
We are having windows server 2003 , 2008 ,2008 R2 and also SBS server.
thnx..........reply
June 16, 2013 at 4:27 am
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.
June 16, 2013 at 4:36 am
I think when i perform transaction log backup it will be in format:
backup log databasename to disk='Path.trn'
When we perform regular log backup like in 15 mins OR 5 mins basis then what i observed is always transaction log file is smaller in size.
Append will work in full backup.
thnx.......reply
June 16, 2013 at 4:48 am
Append will work in transaction log too using the WITH NOINIT. The default is also append. You must be looking at something different. Easy way to see it:
create table dird (colA char(100))
BACKUP LOG ReplicationDB to disk='D:\tlog.bak'
with t1 as (select 'aaa' colA union all select 'aaa' union all select 'aaa' union all select 'aaa' union all
select 'aaa' union all select 'aaa' union all select 'aaa' union all select 'aaa' union all
select 'aaa' colA union all select 'aaa'),
t2 as (select t.colA from t1 t, t1 b),
t3 as (select t.colA from t2 t, t2 a, t2 b)
insert into dird select * from t3
BACKUP LOG ReplicationDB to disk='D:\tlog.bak'
delete from dird
BACKUP LOG ReplicationDB to disk='D:\tlog.bak'
with t1 as (select 'aaa' colA union all select 'aaa' union all select 'aaa' union all select 'aaa' union all
select 'aaa' union all select 'aaa' union all select 'aaa' union all select 'aaa' union all
select 'aaa' colA union all select 'aaa'),
t2 as (select t.colA from t1 t, t1 b)
insert into dird select * from t2
BACKUP LOG ReplicationDB to disk='D:\tlog.bak'
The size of the tlog backup continues to increase. Even though the last one only inserts 100 rows.
June 16, 2013 at 4:52 am
when i perform transaction log backup which is in format:
backup log databasename to disk='path.trn'
When we perform transaction log backup regularly it size get smaller and in full backup we do "with init" i.e. overwrite OR 'with noinit" i.e. append.
My main motto is in express edition how to schedule transaction log backup using Windows task scheduler which will be scheduled and using batch file in which all command will be present.
thnx...........reply
June 16, 2013 at 5:29 am
I have already given you everything to handle a 15 minute transaction log backup. Just specify WITH NOINIT and see how it goes.
June 16, 2013 at 5:39 am
If any problem occurs i will post u msg in this post only.
thnx......Dird:-)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply