point in time restore in SQL server

hi,

right now we have situtation that we tried to do point in time restore, we do transaction log backup every 15 minutes and full backup on sunday only at 9:00 PM, diff backup from mon to sat at 9:00 PM. assume that we have log backup at 9:15 PM, how can I restore the DB up to 9:20PM as the T log backup hasn't been done yet?

also if the backup run start from 9:00 p.m. to 9:30 p.m., it only backup everything snapshoted at 9:00 PM but not 9:01 PM,right?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
From all the details you provided in my opinion you can never restore up to 9:20PM sharp as what you can restore (or PITR) is:
1. restore the DIFF that was completed at 9:30 if this is what you mean by "also if the backup run start from 9:00 p.m. to 9:30 p.m"
2. restore the FULL DB, DIFF's and Tlogs up to the one taken at 9:15 OR the one taken at 9:30 which will give you pretty much the same as DIFF that completed at 9:30.
0
Jason clarkDBA FreelancerCommented:
As you mention in the description. You take Log Backup at after every 15 min. So if you restore your Backup you will get the database upto the last transaction log backup taken. So there is no need to restore backup at 9.20 restore that after 9.30. As it is clear if you restoring at 9.20 you will not get the changes made in between 9.15 and 9.20.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"also if the backup run start from 9:00 p.m. to 9:30 p.m., it only backup everything snapshoted at 9:00 PM but not 9:01 PM,right?"

I am sorry, how about this ? I get a question on this.

". restore the FULL DB, DIFF's and Tlogs up to the one taken at 9:15 OR the one taken at 9:30 which will give you pretty much the same as DIFF that completed at 9:30."

an investment bank people question me on this, he is giving me a hints, use some SQL command in command prompt can check the rest of transaction STILL in existing transaction log file to dig it out and then apply to the just recovered DB after last T-log restore.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Vitor MontalvãoMSSQL Senior EngineerCommented:
how can I restore the DB up to 9:20PM as the T log backup hasn't been done yet?
Easy. Perform a tail log backup before restoring and use it in the restore process.

if the backup run start from 9:00 p.m. to 9:30 p.m., it only backup everything snapshoted at 9:00 PM but not 9:01 PM,right?
Wrong. It will backup everything that happened until 09:30PM. Is the end time that counts and not the begin time.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
" It will backup everything that happened until 09:30PM. Is the end time that counts and not the begin time."

good ! it include all the change occur until 9:30 PM ? but during this time all data keep checking., SQL backup still handle all data changing during backup time ?

but data change location,  change from location to location, how can SQL server backup the data changed but backup passed reading ?

"Perform a tail log backup before"

this is the part of the log still hasn't been T-Log backup since the last T-log backup, and it is still keep on the log file when we found database mdf corrupt ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
but during this time all data keep checking., SQL backup still handle all data changing during backup time ?
Yes. This is a quite robust technology and works the same for all DBMS.

this is the part of the log still hasn't been T-Log backup since the last T-log backup, and it is still keep on the log file when we found database mdf corrupt ?
Yes but not only for corruption. It's also for your case that database is good.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Yes. This is a quite robust technology and works the same for all DBMS."

what is the full command to backup and restore it ?

"but not only for corruption. It's also for your case that database is good."

but if log file corrupted, we can't do this, right?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
what is the full command to backup and restore it ?
What do you mean? Is the usual BACKUP and RESTORE commands.

but if log file corrupted, we can't do this, right?
Of course not. It's a BACKUP LOG statement so it needs to have an healthy log file.
0
lcohanDatabase AnalystCommented:
So it looks to me like there's multiple database issues if it is true as you say that "when we found database mdf corrupt" and "but if log file corrupted" right?

Having that level of corruption I doubt there's anything that can help to "use some SQL command in command prompt can check the rest of transaction STILL in existing transaction log file to dig it out and then apply to the just recovered DB after last T-log restore."  I used in the past Apex Log Explorer but my DB files were always in good state and maybe you could get an evaluation/trial copy and see if this helps but as said my DB's had no corruption when I used it. http://www.apexsql.com/sql_tools_log.aspx

Regardless of what will be the outcome of the recovery if this DB is used for some financial/investment company then the T-Log backups should be taken at least every 5 or even 1 minute so this way your potential data loss window is max 5(or 1 minute)  depending on frequency you take them.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"So it looks to me like there's multiple database issues if it is true as you say that "when we found database mdf corrupt" and "but if log file corrupted" right?"

no, you can see what from Victor is is good.

actually I met someone :):) and they are from banking industry, they need this and they give me a hints that they can do it and ask me what is my way to do it and I also propose this: http://www.apexsql.com/sql_tools_log.aspx, one of the tools I know can do this easily.

they only use command mode and they prefer that, they even claim SSMS need license, I am not sure under what situation as from my understand if you have SQL serve license SSMS is free, if we don't have SQL server then we don't even think about using SSMS at all, right?

"Regardless of what will be the outcome of the recovery if this DB is used for some financial/investment company then the T-Log backups should be taken at least every 5"

that's why the question if the T-Log is backing up at 5 minutes interval and if 9:15 PM has the T-log backup, we still need a method to restore the lost at 9:17 PM, another T-log don't even start yet.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
they only use command mode and they prefer that, they even claim SSMS need license
Not true. SSMS as any SQL Server client tool is free. Only engines needs license (SQL Server, SSAS, SSIS and SSRS).
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Not true. SSMS as any SQL Server client tool is free. Only engines needs license (SQL Server, SSAS, SSIS and SSRS)."

yeah, I think so but they serious state  that out! I can't see why.

I answer by SSMS can do this and they said what if SSMS don't exist.......
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I answer by SSMS can do this and they said what if SSMS don't exist.......
SSMS is only a friendly way to perform actions on a SQL Server database. Of course everything that is performed by SSMS is actually T-SQL code running in the background. I don't use SSMS for many things but I admit that sometimes it helps performing my tasks.
0
marrowyungSenior Technical architecture (Data)Author Commented:
you still use command mode ?

I only use SSMS.


it seems that link tell you how to do tail log backup:

https://technet.microsoft.com/en-us/library/dd297499(v=sql.105).aspx

it is using SSMS.

but it seems the command still that:

BACKUP LOG database_name TO <backup_device> WITH NORECOVERY

what if we can't login to that SQL server from SSMS anymore as the SQL server by corruption of master DB, can't start at all?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
what if we can't login to that SQL server from SSMS anymore as the SQL server by corruption of master DB, can't start at all?
Without master database you can't start SQL Server but that's a different issue and has nothing to do with this point in time restore issue.
0
marrowyungSenior Technical architecture (Data)Author Commented:
can't login using SSMS and login to the SQL server, how can I run the backup command , and therefore tail-log backup ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Well, like I said, that's another issue. You can't do backups, your applications can't connect to databases, jobs can't run, ...
You should open another question for that issue but I can tell you that solution pass from restoring an old master backup or rebuild the current one but this has some particularities and I don't want to diverge from the issue of this question.
0
marrowyungSenior Technical architecture (Data)Author Commented:
ok. tks anyway.
0
marrowyungSenior Technical architecture (Data)Author Commented:
Victor, what I means is, if I connect from SSMS and do the tail log backup, that DB should be offline, even assuming master DB is ok but one user DB mdf file is not good. so I have to find a method to do tail log backup, right?

so I assume it is a yes if one of the user DB'd datafile corrupted and we still can do tail log backup via SSMS ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
so I assume it is a yes if one of the user DB'd datafile corrupted and we still can do tail log backup via SSMS ?
I'm lost now. Is YES for what question?
0
marrowyungSenior Technical architecture (Data)Author Commented:
this is about tail log backup, just want to know if one of the user DB'd datafile corrupted and we still can do tail log backup via SSMS ? that USER DB should be in a pending state ?

I never have user DB corrupt before so I don't know .
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I think it depends on the corruption. For example, if the corruption occurs on the transaction log then for sure you won't be able to perform a tail log backup.
But best thing to do is to give a try and see what happens.
0
marrowyungSenior Technical architecture (Data)Author Commented:
" For example, if the corruption occurs on the transaction log then for sure you won't be able to perform a tail log backup."

tks,

"But best thing to do is to give a try and see what happens."

someone said by isql.exe command it can be done, just wonder how.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
someone said by isql.exe command it can be done, just wonder how.
No matter what tool you use the behavior will be the same because the commands will be always executed by SQL Server engine. The tools are only an interface to send the commands to the engine. They don't do magic things but only offers some help like a more user friendly interface or intellisense.
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks for that.
0
marrowyungSenior Technical architecture (Data)Author Commented:
Victor,

I am now following your direction to give your score, I knew how now.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.