October 13, 2008 at 4:53 pm
I need to create a SQL Server Agent job that will do create a transaction log backup file each time it runs. It runs on on the how every hour. I know the maintenance plan adds a datetime stamp at the end of each log file name but how can I do it outside the maintenance plan?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
October 14, 2008 at 9:59 am
Something like this will work:
[font="Courier New"]DECLARE @datetime VARCHAR(20)
DECLARE @filename VARCHAR(100)
-- get the date in ANSI yyyymmddhhmmss
SET @datetime = REPLACE(CONVERT(VARCHAR(10), GETDATE(), 102), '.', '') +
REPLACE(CONVERT(VARCHAR(10), GETDATE(), 108), ':', '')
SET @filename = '[path]\' + @datetime + '_datebasename_log.trn'
BACKUP LOG [Database Name] TO DISK = @filename
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 14, 2008 at 10:18 am
So I would copy that script into a SQL Server Agent Job?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
October 14, 2008 at 10:21 am
Yes.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 14, 2008 at 10:39 am
Do I need specify like INIT or NOINIT?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
October 14, 2008 at 10:51 am
Because you are creating a new file each time you do not need init/noinit. If you wanted to create one log backup file per day you would need somemore logic and then would need the NOINIT to append to a file.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 14, 2008 at 11:01 am
IS there one perference over another?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
October 14, 2008 at 11:10 am
There are pluses and minuses to both. I usually append to one file. I think it makes restores easier as you just need to change the file number when restoring instead of the file name. But you can also script restores from multiple files fairly simply too. The benefit is to multiple files is that a problem with one log backup doesn't affect earlier ones.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 14, 2008 at 1:06 pm
So if I were to go with a single file transaction log backup how would I setup the job?
here is a scenerion
M - Full DB Backup @ 12:00am
T-Log Backup Every hour on the hour
T- Full DB Backup @ 12:00am
T-Log Backup Every hour on the hour
W- Full DB Backup @ 12:00am
T-Log Backup Every hour on the hour
Th- Full DB Backup @ 12:00am
T-Log Backup Every hour on the hour
F- Full DB Backup @ 12:00am
T-Log Backup Every hour on the hour
S- Full DB Backup @ 12:00am
T-Log Backup Every hour on the hour
Su- Full DB Backup @ 12:00am
T-Log Backup Every hour on the hour
So what would be an example of setting up a SQL Server jobs to follow this scenerio?
And then let say we have a developer induced issue (they inadvertantly deleted some data) at let say Wednesday at 3:30pm so we need to restore the database back to where it was at 3:00pm, what would be the process?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
October 14, 2008 at 2:03 pm
Answering your second question first, here are a couple of articles on point in time restores by people I respect:
http://www.sqlservercentral.com/articles/Backup+and+Recovery/restoringtoapointintime/1905/%5B/url%5D
[url]http://www.sqlservercentral.com/articles/Backup/recoverytoapointintime/1914/">
http://www.sqlservercentral.com/articles/Backup+and+Recovery/restoringtoapointintime/1905/%5B/url%5D
http://www.sqlservercentral.com/articles/Backup/recoverytoapointintime/1914/
Now for the backup scenario you list. I'd add at least 1 differential per day so that I don't have to restore 23 log backups when the problem happens at 11:15 pm. For jobs I'd have 2 jobs one that runs the nightly full. Then a second one that does the log backups from 1am through 11pm. Pseudo-code for the job would be something like this:
Set the backup file name like in my first post
If Hour(getdate()) = 1 then
Backup Log db_name to Disk = filname with init
Else
Backup Log db_name to Disk = filename with noinit
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 15, 2008 at 7:22 am
That worked great. But I need run into an issue in testing. We backup our production database to tape and also take t-log backup every hour to disk. We tried restoring the database from tape to another server and left it in recovery mode. We copied the t-log backup files to a local drive on the server we are trying to restore on. When I went to apply the transaction log backups I recieved the following error:
---------------------------
Microsoft SQL-DMO (ODBC SQLState: 42000)
---------------------------
The log in this backup set terminates at LSN 1360142000022148100001, which is too early to apply to the database. A more recent log backup that includes LSN 1360167000025330700001 can be restored.
RESTORE LOG is terminating abnormally.
---------------------------
OK
---------------------------
What's up with that?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
October 15, 2008 at 8:03 am
Since you are restoring from a file that contains multiple backups you need to reference the file number in the backup file. Like this:
Restore Log database_name From Disk=FilePath With File = 1 (to N), NoRecovery (Recovery when on last file
You can find the number of backups in the backup file by running:
Restore HeaderOnly From Disk=FilePath
The position column is the File number.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 15, 2008 at 8:21 am
I found the issue was I trying to restore a log backup that was associated with the previous nights backups.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
October 15, 2008 at 8:26 am
Now the million dollar question. Is there was to restore the full database backup to another database name and then apply all the transaction log backup to the new database?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
October 15, 2008 at 8:41 am
Yes, lookup Restore in BOL (Books on line) and there is an example. You basically use a different database name and the with MOVE property.
Like:
Restore Database NewDBName From Disk = FilePath WITH MOVE 'OLDDB_Data' TO 'C:\MySQLServer\Newdb.mdf',
MOVE 'OldDb_Log' TO 'C:\MySQLServerewdb.ldf';
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply