I am trying to backup my Transaction Logs using T-SQL with the aid of a job. The problem I am running into is that I need it to backup every three hours and retain one days worth of transactional logs. This is the query I got so far.
BACKUP LOG CA001 TO DISK = 'S:\AZTARGP\CA001\ca001_TRN_bkuplog.trn' with retaindays = 1
Try the Database Maintenance Plan. It's a database object and one of the properties is "Remove Files Older Than ___". It creates an entry in your SQL SERVER AGENT under JOBS to run the plan as scheduled.
It also allows much more than just simple backups and it very easy to manage.
I set up a Sunday backup that keeps it's prior Sunday file but removes older ones, but that backup doesn't affect my ongoing daily backups and logfile changes (separate jobs).
Also: I notice you're referencing a "S:" drive. Does SQL backup recognize mapped drives? Maybe that's just a shortcoming of the Database Maintenance Plan Object. It would seem risky. I'd rather do it to a separate (physical) volume physically attached, then immediately copy that the mapped drive (that's what I do). That way a RAID corruption on the DB volume doesn't corrupt the backup too. Also; my backup volume is just RAID 1 (mirrored), since that's easily transfered to a different server in a disaster situation.
The S:\ drive is actually an iscsi drive that is recognised by the server. What is funny is that the maintenance plan for the database backup works just not the transaction logs. I also have checked to make sure the database was not in simple recovery mode.
I am sort of trying to find a way to give my backup names a date and time. Then I want to only keep a days worth of transaction logs becuase we do a full backup of the database everynight.
I do have
declare @bkupdate datetime
set @bkupdate = getdate()
but I don't know how to include it into my sql statement. When I do I get an syntax error.
BACKUP LOG CA001 TO DISK = 'S:\aztargp\ca001\CA001_' + @bkupdate + '_TRNLOG.trn' with retaindays = 1
I am just trying to use t-sql until I can find the problem with why my database maintenance plan is not working. Thanks for the help so far.
BACKUP DATABASE [wfab] TO DISK = N'd:\SQL Backups\Daily\mydb_db_latest.bak' WITH INIT , NAME = N'mydbLatest', SKIP , FORMAT
This hard codes the filename. I have this running every 6 hours. Surely using a variable for the filename would be fine, although why not build the whole thing first.
declare @bkdbName varchar(50)
set @bkdbName = 'mydb' + Convert(varchar(50),getdate(),110)
declare @bkName varchar(255)
set @bkName = 'd:'
set @bkName = trim(@bkName) + '\SQL Backups\Daily\'
set @bkName = trim(@bkName) + trim(@bkdbName)
BACKUP DATABASE [wfab] TO DISK = trim(@bkName) WITH INIT , NAME = trim(@bkName), SKIP , FORMAT
The reason I liked it without the special naming is because then it shows up pre-registered when I want to do a restore. I do a scheduled copy of these backup files to various places before the next backup takes place. Actually; I copy it to a standby server (as with the half-hour log files), ftp it to an offsite server, and I have a scheduled CD burn (encrypted) to my desktop at 3:30 every day, that I take home with me. This in addition to replication - I think it's safe to say we'll never loose much.
I haven't tried the above - I'm curious if it would then cause all the prior backups to show on the pre-registered backup file list. That would be very nice for restores - something we do regularly to test environments.
code works great. Had to change a couple of things. How can I get the @bkdbName to also contain the time?
Here's a link to MSDN for TSQL Ref. Make this a favorite.
Look at "Cast and convert" for conversion formats.
This should help:
Result is: 15:57:34 Note: 114 returns 11 or 12 characters, but you probably don't need miliseconds.
Use SUBSTRING to parse out the ":" since that's probably not allowed as part of a filename.
I'm curious (and too lazy to try it). After creating a couple of these backups, does it list all these backups on the drop-down list when you go into the RESTORE dialog box? I know that works if the name is static.
Thanks guys for the help. I also found out what was wrong with my DB maintenance plans. When you set to backup master,model,msdb and other databases in one plan it works fine for the db backups but not the translog. When i created a seperate plan for my user databases the transaction log backups completed successfully.
With SQL Server 2005 I had to make a fair few amendments ...
Just in case this solution is not working for people or they need a more specfic backup script, I made the following alterations to ensure that they would work correctly:
-- Declares a string
declare @sql nvarchar(255)
-- Declares the filename. Please change _CHANGE_ to backup name
declare @bkdbName varchar(50)
set @bkdbName = '_CHANGE_' + Convert(varchar(50),getdate(),110)
-- declares the path name. Seperated by disk drive and directory.
declare @bkName varchar(255)
-- Enter the Drive letter below. Please remove C: as current value.
set @bkName = 'C:'
-- Enter the folder name below (with lead and end '\' back slashes.
-- Ensure that you change the folder from Temp below.
set @bkName = @bkName + '\Temp\'
set @bkName = @bkName + @bkdbName
-- Sets the backup running by executing the @sql string.
set @sql = 'BACKUP DATABASE DB_NAME TO DISK = '''+@bkName+'.bak'''
exec sp_executesql @sql
The TRIM was not working (understandably) and WITH INIT was never going to work because the whole point of this script was to give the backups different names, not to overwrite the backup file each time with your new backup.
If you do use this altered script, be very careful when copying and pasting as I have a feeling that the quotes (') might get changed in translation.
This runs each evening as we use a third-party solution to take off-site backups continuously and therefore local backups are more for allowing faster restores if necessary ... generally on test systems.
I hope this was helpful, but do post any issues that you might have when using it.
NOTE: The reason that I have gone for thsi approach is that I am getting increasingly annoyed with maintenance plans falling over for no apparent reason.
VERY handy - Thanks a lot PMASchmed! I've just added a lot more functionality using this, although getting the syntax right on the first attempt was a little bit challenging.
Also, for anyone that uses this it's important to add the path to the path environmental variable sdo that it can be run from any location.
Right-click My Computer > Properties > Advanced > Environmental Variables
Add C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn at the end after the semi-colon ... without any spaces and making sure you installed in the same location as me (actually, i installed on E: )!
Ok it, then you can run sqlmaint from wherever you need to.
Just tupe sqlmaint -? for a list of (extremely) useful commands.