September 23, 2015 at 12:46 am
Dear All,
I'm trying find a way of crating an sql server job which would restore only transactional log file just once a day. The trouble I'm having is trying to get only the latest transactional log file it it possible to achieve through e.g powershell etc as I can't find a way through sql query please?
Thank you in advance!
Tizita
September 23, 2015 at 1:44 am
Why not use logshipping and set the restore job to run once a day?
September 23, 2015 at 6:48 am
It depends.
What's the recovery model used by the source database ?
What's the final goal of your database ?
Do you need your target database to be in e.g. read-only state, Read-Write or Restoring ?
( what's the goal of your target db )
tt-615680 (9/23/2015)
...The trouble I'm having is trying to get only the latest transactional log file it it possible to achieve through e.g powershell etc as I can't find a way through sql query please?...
I fear, that if you cannot "find your way through sql query", you may be playing way out of your league !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 23, 2015 at 6:57 am
I'm with Anthony, why not just use log shipping. It's built into the product and will do what you want.
However, a couple of suggestions. First, check the msdb database. There you can see the complete backup history which will show you the latest transaction log backup.
But...
Let's talk about this "restore once a day from the latest log backup".
How often are you running log backups. Once a day? If so, you're using them utterly incorrectly. They should be run frequently throughout the day. Most of my systems have had them running every 10-15 minutes, all day long. This is in order to always be able to restore up to the last 10 minutes in the case of an emergency. If you're running them daily, you could lose an entire day's worth of backups, not to mention that your log files are unnecessarily large. Further, if you are running log backups more frequently than once a day, you can't just restore the last one. You have to restore the database, leaving it in recovery, and then restore all the logs between whatever backup you took until the point you wish to stop. You can't just install the latest and skip the others. Log backups are in a chain.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 23, 2015 at 8:39 am
Thank you for all your replies!
I get a transactional log file from another server which is not d not done by log shipping, it is just a job which runs daily to drop the transactional log file into a particular location on my server; all I need to do is then pick up the latest t-log file and restore it on my local database.
Please let me know if you have any questions.
Thank you!
September 23, 2015 at 8:55 am
But, if it's already dropping this single log backup (and please, note my concerns above), what more are you looking for?
You just have to reference the location and get the latest file from where it's been placed for you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 23, 2015 at 9:29 am
The database will be in "Stand by/Read only" but my problem is that if something goes wrong then I would not want the job to try and restore from the other transactional log files which are also on the same drive.
September 23, 2015 at 11:31 am
Assuming there's a naming standard based on date & time, that's one way to check. Another is to use PowerShell, then you can look at the date on the file.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 24, 2015 at 2:01 am
Set-location "c:\myfolder\SQLBackups"
# List all files and order by LastWriteTime descending
get-childitem -Filter '*.BAK' | Select FullName, LastWriteTime | sort LastWriteTime -Descending | ft -AutoSize
# only the most recent file
$Bakfile = get-childitem -Filter '*.BAK' | Select FullName, LastWriteTime |
sort LastWriteTime -Descending | Select -First 1
Restore-SqlDatabase -serverinstance YourServer\Instance -Database Yourdatabase `
-BackupFile $Bakfile.Fullname -Checksum `
-StandbyFile 'E:\MSSQL12.instance\MSSQL\Backup\YourDatabase_StandBy.BAK'
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 25, 2015 at 5:02 am
Thank you for your help! I’ve tried to run the scrip but unfortatly I get the following error message, I have changed the location name where the transactional log file is coming from and the server name, databasename etc but I’m not sure what else I’m missing?
The term 'Restore-sqlDatabase' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:12 + Restore-sqldatabase <<<< -serverinstance [nameofserver] -Database [databasename]`
+ CategoryInfo : ObjectNotFound: (Restore-sql:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException
PS g:\\transactionallogbackuplocation > -BackupFile $Bakfile.Fullname -Checksum`
Missing expression after unary operator '-'.
At line:1 char:2
+ - <<<< BackupFile $Bakfile.Fullname -Checksum`
+ CategoryInfo : ParserError: (-:String) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : MissingExpressionAfterOperator
Thank you!
September 25, 2015 at 5:55 am
tt-615680 (9/25/2015)
Thank you for your help! I’ve tried to run the scrip but unfortatly I get the following error message, I have changed the location name where the transactional log file is coming from and the server name, databasename etc but I’m not sure what else I’m missing?The term 'Restore-sqlDatabase' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:12 + Restore-sqldatabase <<<< -serverinstance [nameofserver] -Database [databasename]`
+ CategoryInfo : ObjectNotFound: (Restore-sql:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException
PS g:\\transactionallogbackuplocation > -BackupFile $Bakfile.Fullname -Checksum`
Missing expression after unary operator '-'.
At line:1 char:2
+ - <<<< BackupFile $Bakfile.Fullname -Checksum`
+ CategoryInfo : ParserError: (-:String) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : MissingExpressionAfterOperator
Thank you!
the pc/server where you develop this ps1 script, should have SQLPS installed ( comes with the full sql2014 client ).
The one running the script, should have access to the given location as does the sqlserver service account of the target instance.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply