|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Differential and Transaction backup restore.
Differential and Transaction backup restore.
I have scheduled differential backup to run at 3PM and the transactional backup to run at 3pm. In the event of data restore work, should I restore the data from differential as well as transactional backups. My questions, is it required to run data restore from the 3Pm transactional backup? Thanks, Steve Why are you doing a Differential and a Log backup at the same time? Are you
only issuing one Log backup during the entire day? Depending on which kicked in first or finished first and if there was any activity during that time it could be either one or both. Transaction logs keep a running chain of all transactions since the first FULL backup assuming you never truncated the log outside of a log backup. The Diff has all the changes since the last FULL backup. You always apply the last FULL backup and then the most recent Diff if you have one. Then any logs since the last Diff. Since it is imposable to tell the real order from this post it is hard to say if restoring the log would actually do anything or not in regards to transactions that the Diff may not have caught. If you attempt to restore the log and it is out of sequence it will warn you. I suggest you stagger your backups and ensure you have a proper schedule of Log backups to cover your needs. -- Show quoteHide quoteAndrew J. Kelly SQL MVP "Steve Martin" <SteveM_Sanj***@hotmail.com> wrote in message news:u73ESQyYFHA.3920@TK2MSFTNGP10.phx.gbl... > Differential and Transaction backup restore. > > > > I have scheduled differential backup to run at 3PM and the transactional > backup to run at 3pm. In the event of data restore work, should I restore > the data from differential as well as transactional backups. > > My questions, is it required to run data restore from the 3Pm > transactional > backup? > > Thanks, > > Steve > > I will rethink bout my backup scheduling. Thanks for the suggestions.
By the way, I have taken one complete backup at 8 am, some one at 9.00 am truncated the log file. SQL Server crashes at 10am, then all my transaction log files are not restorable since 9.00 onwards. Is it correct? Steve Show quoteHide quote "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:uhkbT4yYFHA.3620@TK2MSFTNGP09.phx.gbl... > Why are you doing a Differential and a Log backup at the same time? Are you > only issuing one Log backup during the entire day? Depending on which kicked > in first or finished first and if there was any activity during that time it > could be either one or both. Transaction logs keep a running chain of all > transactions since the first FULL backup assuming you never truncated the > log outside of a log backup. The Diff has all the changes since the last > FULL backup. You always apply the last FULL backup and then the most recent > Diff if you have one. Then any logs since the last Diff. Since it is > imposable to tell the real order from this post it is hard to say if > restoring the log would actually do anything or not in regards to > transactions that the Diff may not have caught. If you attempt to restore > the log and it is out of sequence it will warn you. I suggest you stagger > your backups and ensure you have a proper schedule of Log backups to cover > your needs. > > -- > Andrew J. Kelly SQL MVP > > > "Steve Martin" <SteveM_Sanj***@hotmail.com> wrote in message > news:u73ESQyYFHA.3920@TK2MSFTNGP10.phx.gbl... > > Differential and Transaction backup restore. > > > > > > > > I have scheduled differential backup to run at 3PM and the transactional > > backup to run at 3pm. In the event of data restore work, should I restore > > the data from differential as well as transactional backups. > > > > My questions, is it required to run data restore from the 3Pm > > transactional > > backup? > > > > Thanks, > > > > Steve > > > > > > What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG WITH NO_LOG?
-- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Steve Martin" <SteveM_Sanj***@hotmail.com> wrote in message news:OLwVbSzYFHA.3624@tk2msftngp13.phx.gbl... >I will rethink bout my backup scheduling. Thanks for the suggestions. > > > > By the way, I have taken one complete backup at 8 am, some one at 9.00 am > truncated the log file. SQL Server crashes at 10am, then all my transaction > log files are not restorable since 9.00 onwards. Is it correct? > > Steve > > > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:uhkbT4yYFHA.3620@TK2MSFTNGP09.phx.gbl... >> Why are you doing a Differential and a Log backup at the same time? Are > you >> only issuing one Log backup during the entire day? Depending on which > kicked >> in first or finished first and if there was any activity during that time > it >> could be either one or both. Transaction logs keep a running chain of all >> transactions since the first FULL backup assuming you never truncated the >> log outside of a log backup. The Diff has all the changes since the last >> FULL backup. You always apply the last FULL backup and then the most > recent >> Diff if you have one. Then any logs since the last Diff. Since it is >> imposable to tell the real order from this post it is hard to say if >> restoring the log would actually do anything or not in regards to >> transactions that the Diff may not have caught. If you attempt to restore >> the log and it is out of sequence it will warn you. I suggest you stagger >> your backups and ensure you have a proper schedule of Log backups to cover >> your needs. >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "Steve Martin" <SteveM_Sanj***@hotmail.com> wrote in message >> news:u73ESQyYFHA.3920@TK2MSFTNGP10.phx.gbl... >> > Differential and Transaction backup restore. >> > >> > >> > >> > I have scheduled differential backup to run at 3PM and the transactional >> > backup to run at 3pm. In the event of data restore work, should I > restore >> > the data from differential as well as transactional backups. >> > >> > My questions, is it required to run data restore from the 3Pm >> > transactional >> > backup? >> > >> > Thanks, >> > >> > Steve >> > >> > >> >> > > If someone actually truncates the log at 9am, you can only restore til 8am,
as you suspect. -- Show quoteHide quoteWayne Snyder, MCDBA, SQL Server MVP Mariner, Charlotte, NC www.mariner-usa.com (Please respond only to the newsgroups.) I support the Professional Association of SQL Server (PASS) and it's community of SQL Server professionals. www.sqlpass.org "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:Oo5wCJ2YFHA.3164@TK2MSFTNGP09.phx.gbl... > What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG > WITH NO_LOG? > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > > "Steve Martin" <SteveM_Sanj***@hotmail.com> wrote in message > news:OLwVbSzYFHA.3624@tk2msftngp13.phx.gbl... >>I will rethink bout my backup scheduling. Thanks for the suggestions. >> >> >> >> By the way, I have taken one complete backup at 8 am, some one at 9.00 am >> truncated the log file. SQL Server crashes at 10am, then all my >> transaction >> log files are not restorable since 9.00 onwards. Is it correct? >> >> Steve >> >> >> >> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message >> news:uhkbT4yYFHA.3620@TK2MSFTNGP09.phx.gbl... >>> Why are you doing a Differential and a Log backup at the same time? Are >> you >>> only issuing one Log backup during the entire day? Depending on which >> kicked >>> in first or finished first and if there was any activity during that >>> time >> it >>> could be either one or both. Transaction logs keep a running chain of >>> all >>> transactions since the first FULL backup assuming you never truncated >>> the >>> log outside of a log backup. The Diff has all the changes since the last >>> FULL backup. You always apply the last FULL backup and then the most >> recent >>> Diff if you have one. Then any logs since the last Diff. Since it is >>> imposable to tell the real order from this post it is hard to say if >>> restoring the log would actually do anything or not in regards to >>> transactions that the Diff may not have caught. If you attempt to >>> restore >>> the log and it is out of sequence it will warn you. I suggest you >>> stagger >>> your backups and ensure you have a proper schedule of Log backups to >>> cover >>> your needs. >>> >>> -- >>> Andrew J. Kelly SQL MVP >>> >>> >>> "Steve Martin" <SteveM_Sanj***@hotmail.com> wrote in message >>> news:u73ESQyYFHA.3920@TK2MSFTNGP10.phx.gbl... >>> > Differential and Transaction backup restore. >>> > >>> > >>> > >>> > I have scheduled differential backup to run at 3PM and the >>> > transactional >>> > backup to run at 3pm. In the event of data restore work, should I >> restore >>> > the data from differential as well as transactional backups. >>> > >>> > My questions, is it required to run data restore from the 3Pm >>> > transactional >>> > backup? >>> > >>> > Thanks, >>> > >>> > Steve >>> > >>> > >>> >>> >> >> > > When I say truncate the log file means....
In Enterprise Manager, Right click the database, All Tasks... Shrink Database.... and Shrink the log file here. Thanks, Martin Show quoteHide quote "Wayne Snyder" <wayne.nospam.snyder@mariner-usa.com> wrote in message news:%23pfQ76EZFHA.1088@TK2MSFTNGP14.phx.gbl... > If someone actually truncates the log at 9am, you can only restore til 8am, > as you suspect. > > -- > Wayne Snyder, MCDBA, SQL Server MVP > Mariner, Charlotte, NC > www.mariner-usa.com > (Please respond only to the newsgroups.) > > I support the Professional Association of SQL Server (PASS) and it's > community of SQL Server professionals. > www.sqlpass.org > > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in > message news:Oo5wCJ2YFHA.3164@TK2MSFTNGP09.phx.gbl... > > What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG > > WITH NO_LOG? > > > > -- > > Tibor Karaszi, SQL Server MVP > > http://www.karaszi.com/sqlserver/default.asp > > http://www.solidqualitylearning.com/ > > > > > > "Steve Martin" <SteveM_Sanj***@hotmail.com> wrote in message > > news:OLwVbSzYFHA.3624@tk2msftngp13.phx.gbl... > >>I will rethink bout my backup scheduling. Thanks for the suggestions. > >> > >> > >> > >> By the way, I have taken one complete backup at 8 am, some one at 9.00 am > >> truncated the log file. SQL Server crashes at 10am, then all my > >> transaction > >> log files are not restorable since 9.00 onwards. Is it correct? > >> > >> Steve > >> > >> > >> > >> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > >> news:uhkbT4yYFHA.3620@TK2MSFTNGP09.phx.gbl... > >>> Why are you doing a Differential and a Log backup at the same time? Are > >> you > >>> only issuing one Log backup during the entire day? Depending on which > >> kicked > >>> in first or finished first and if there was any activity during that > >>> time > >> it > >>> could be either one or both. Transaction logs keep a running chain of > >>> all > >>> transactions since the first FULL backup assuming you never truncated > >>> the > >>> log outside of a log backup. The Diff has all the changes since the last > >>> FULL backup. You always apply the last FULL backup and then the most > >> recent > >>> Diff if you have one. Then any logs since the last Diff. Since it is > >>> imposable to tell the real order from this post it is hard to say if > >>> restoring the log would actually do anything or not in regards to > >>> transactions that the Diff may not have caught. If you attempt to > >>> restore > >>> the log and it is out of sequence it will warn you. I suggest you > >>> stagger > >>> your backups and ensure you have a proper schedule of Log backups to > >>> cover > >>> your needs. > >>> > >>> -- > >>> Andrew J. Kelly SQL MVP > >>> > >>> > >>> "Steve Martin" <SteveM_Sanj***@hotmail.com> wrote in message > >>> news:u73ESQyYFHA.3920@TK2MSFTNGP10.phx.gbl... > >>> > Differential and Transaction backup restore. > >>> > > >>> > > >>> > > >>> > I have scheduled differential backup to run at 3PM and the > >>> > transactional > >>> > backup to run at 3pm. In the event of data restore work, should I > >> restore > >>> > the data from differential as well as transactional backups. > >>> > > >>> > My questions, is it required to run data restore from the 3Pm > >>> > transactional > >>> > backup? > >>> > > >>> > Thanks, > >>> > > >>> > Steve > >>> > > >>> > > >>> > >>> > >> > >> > > > > > > Shrink doesn't break the log backup sequence. This assumes, of course that EM doesn't do anything
strange (run profiler trace to see). But there are disadvantages to shrink: http://www.karaszi.com/SQLServer/info_dont_shrink.asp -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Steve Martin" <SteveM_Sanj***@hotmail.com> wrote in message news:eMWe3pgZFHA.1384@TK2MSFTNGP09.phx.gbl... > When I say truncate the log file means.... > In Enterprise Manager, Right click the database, All Tasks... Shrink > Database.... and Shrink the log file here. > Thanks, > Martin > > "Wayne Snyder" <wayne.nospam.snyder@mariner-usa.com> wrote in message > news:%23pfQ76EZFHA.1088@TK2MSFTNGP14.phx.gbl... >> If someone actually truncates the log at 9am, you can only restore til > 8am, >> as you suspect. >> >> -- >> Wayne Snyder, MCDBA, SQL Server MVP >> Mariner, Charlotte, NC >> www.mariner-usa.com >> (Please respond only to the newsgroups.) >> >> I support the Professional Association of SQL Server (PASS) and it's >> community of SQL Server professionals. >> www.sqlpass.org >> >> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote > in >> message news:Oo5wCJ2YFHA.3164@TK2MSFTNGP09.phx.gbl... >> > What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG >> > WITH NO_LOG? >> > >> > -- >> > Tibor Karaszi, SQL Server MVP >> > http://www.karaszi.com/sqlserver/default.asp >> > http://www.solidqualitylearning.com/ >> > >> > >> > "Steve Martin" <SteveM_Sanj***@hotmail.com> wrote in message >> > news:OLwVbSzYFHA.3624@tk2msftngp13.phx.gbl... >> >>I will rethink bout my backup scheduling. Thanks for the suggestions. >> >> >> >> >> >> >> >> By the way, I have taken one complete backup at 8 am, some one at 9.00 > am >> >> truncated the log file. SQL Server crashes at 10am, then all my >> >> transaction >> >> log files are not restorable since 9.00 onwards. Is it correct? >> >> >> >> Steve >> >> >> >> >> >> >> >> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message >> >> news:uhkbT4yYFHA.3620@TK2MSFTNGP09.phx.gbl... >> >>> Why are you doing a Differential and a Log backup at the same time? > Are >> >> you >> >>> only issuing one Log backup during the entire day? Depending on which >> >> kicked >> >>> in first or finished first and if there was any activity during that >> >>> time >> >> it >> >>> could be either one or both. Transaction logs keep a running chain of >> >>> all >> >>> transactions since the first FULL backup assuming you never truncated >> >>> the >> >>> log outside of a log backup. The Diff has all the changes since the > last >> >>> FULL backup. You always apply the last FULL backup and then the most >> >> recent >> >>> Diff if you have one. Then any logs since the last Diff. Since it is >> >>> imposable to tell the real order from this post it is hard to say if >> >>> restoring the log would actually do anything or not in regards to >> >>> transactions that the Diff may not have caught. If you attempt to >> >>> restore >> >>> the log and it is out of sequence it will warn you. I suggest you >> >>> stagger >> >>> your backups and ensure you have a proper schedule of Log backups to >> >>> cover >> >>> your needs. >> >>> >> >>> -- >> >>> Andrew J. Kelly SQL MVP >> >>> >> >>> >> >>> "Steve Martin" <SteveM_Sanj***@hotmail.com> wrote in message >> >>> news:u73ESQyYFHA.3920@TK2MSFTNGP10.phx.gbl... >> >>> > Differential and Transaction backup restore. >> >>> > >> >>> > >> >>> > >> >>> > I have scheduled differential backup to run at 3PM and the >> >>> > transactional >> >>> > backup to run at 3pm. In the event of data restore work, should I >> >> restore >> >>> > the data from differential as well as transactional backups. >> >>> > >> >>> > My questions, is it required to run data restore from the 3Pm >> >>> > transactional >> >>> > backup? >> >>> > >> >>> > Thanks, >> >>> > >> >>> > Steve >> >>> > >> >>> > >> >>> >> >>> >> >> >> >> >> > >> > >> >> > >  
Other interesting topics
Veritas Backup Exec. 9.1 SQL Agent - Use of...
Minimum level of rights for a SQL Server DBA. SQL brain twister Maintanence Plans for Backups SQL Server EM/Query Analyzer Access Error 21776 [SQL DMO] blocking alerts Re: Strange problems accessing SQL server with windows 2000 window SQL Server 2000 on Xeon x64 system Error 15401 |
|||||||||||||||||||||||
