July 20, 2011 at 11:04 am
I am a relatively new DBA, and my employer is getting ready to roll-out a SQL-based application to clients. As we will be involved in assisting these clients setup backups of their data, I am looking to setup scheduled backups.
I am pushing for Full Recovery mode, and have a question on backing up.
Some clients may be installed using SQL 2008 R2 Express Edition, which does not have the SQL Agent or Maintenance Plans. What I am looking to do, is to have a Windows Scheduled Task, which will run a batch file to call sqlcmd to run the backup. I am pushing for hourly (or more frequent) transaction log backups.
My question is, how can I modify the following command:
sqlcmd -q "USE DBNAME" -Q "BACKUP LOG DBNAME TO DISK = 'C:\FOLDER\DBNAME.TRN'"
to "auto-name" the backup file, possibly with the date and time?
Before anyone comments, no the backups won't be saved to the C:\ drive of the server, that drive letter is more a placeholder than anything. Worst case, I believe I can do this with some batch file fu, but if there's an easier way...
Thank you,
Jason A.
July 20, 2011 at 11:07 am
My advice would be to use Dynamic SQL. Declare a variable, set the variable's value to the sql command above (with the date / time info concated into the string), then call sp_executesql (@Myvar) to execute the sql command.
July 20, 2011 at 11:08 am
I can't vouch for it but you could use this :
http://standalonesqlagent.codeplex.com/
Combined with this (which I vouch for) :
July 22, 2011 at 9:01 am
Well, I did try loading the stand alone SQL Agent on my work PC, to give it a testing. Problem is, the install failed (Win7 x64 Pro)
I did a little more work, and figured out how I can date and time stamp the backup files within a DOS batch file, though.
Thanks!
Jason A.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply