Point in time recovery

FAQ

Profile

Point in time recovery

Database Forums (Home) -> Client

Next: How to make URL cloaking

Author

Message

Smith

External

Since: Jan 22, 2008
Posts: 17

(Msg. 1) Posted: Thu Jan 24, 2008 10:37 am Post subject: Point in time recovery Archived from groups: microsoft>public>sqlserver>clients (more info?)

Hi Professionals, Following are my queries and I want to recover the table (Test2) with data and recover all the test1 data. I would appreciate if you can let me know the commands. use eZSale go create table TEST1 (ID INT, NAME VARCHAR(50)) create table TEST2(ID INT, NAME VARCHAR(50)) --Time:10:01 BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_FullDb.BAK' --Time:10:02 INSERT INTO TEST1 SELECT 1, 'AFTER FULL BACKUP' INSERT INTO TEST2 SELECT 1, 'AFTER FULL BACKUP' --Time:10:03 BACKUP LOG eZsale to disk = 'c:\Temp\test_Logbk.BAK' --Time: 10:04 INSERT INTO TEST1 SELECT 2, 'AFTER LOG BACKUP' INSERT INTO TEST2 SELECT 2, 'AFTER LOG BACKUP' INSERT INTO TEST2 SELECT 3, 'AFTER LOG BACKUP..SECOND ENTRY' --Time: 10:05 DROP TABLE TEST2 --Time: 10:06 BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_DiffDb.BAK' with differential --Time: 10:07 INSERT INTO TEST1 SELECT 4, 'AFTER DIFFERNTIAL BACKUP' INSERT INTO TEST1 SELECT 5, 'AFTER DIFFERNTIAL BACKUP..SECOND ENTRY' >> Stay informed about: Point in time recovery

Tibor Karaszi

External

Since: Jan 29, 2004
Posts: 891

(Msg. 2) Posted: Thu Jan 24, 2008 4:00 pm Post subject: Re: Point in time recovery [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Restore will not let you get an inconsistent views of a database. But that I mean that it won't allow for a restore where some objects are from one point in time and other objects are from another point in time. You can restore up to 10:05 into a new database name and move the desired data (TEST2) from there into the production database. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Smith" wrote in message > Hi Professionals, > > Following are my queries and I want to recover the table (Test2) with data and recover all the > test1 data. I would appreciate if you can let me know the commands. > > > use eZSale > go > create table TEST1 (ID INT, NAME VARCHAR(50)) > create table TEST2(ID INT, NAME VARCHAR(50)) > > --Time:10:01 > BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_FullDb.BAK' > > --Time:10:02 > INSERT INTO TEST1 SELECT 1, 'AFTER FULL BACKUP' > INSERT INTO TEST2 SELECT 1, 'AFTER FULL BACKUP' > > --Time:10:03 > BACKUP LOG eZsale to disk = 'c:\Temp\test_Logbk.BAK' > > --Time: 10:04 > INSERT INTO TEST1 SELECT 2, 'AFTER LOG BACKUP' > INSERT INTO TEST2 SELECT 2, 'AFTER LOG BACKUP' > INSERT INTO TEST2 SELECT 3, 'AFTER LOG BACKUP..SECOND ENTRY' > > --Time: 10:05 > DROP TABLE TEST2 > > --Time: 10:06 > BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_DiffDb.BAK' with differential > > --Time: 10:07 > INSERT INTO TEST1 SELECT 4, 'AFTER DIFFERNTIAL BACKUP' > INSERT INTO TEST1 SELECT 5, 'AFTER DIFFERNTIAL BACKUP..SECOND ENTRY' >> Stay informed about: Point in time recovery

Smith

External

Since: Jan 22, 2008
Posts: 17

(Msg. 3) Posted: Fri Jan 25, 2008 9:20 am Post subject: Re: Point in time recovery [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Can you tell me the step? how I can achieve this. Thanks "Tibor Karaszi" wrote in message > Restore will not let you get an inconsistent views of a database. But that > I mean that it won't allow for a restore where some objects are from one > point in time and other objects are from another point in time. You can > restore up to 10:05 into a new database name and move the desired data > (TEST2) from there into the production database. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > "Smith" wrote in message > >> Hi Professionals, >> >> Following are my queries and I want to recover the table (Test2) with >> data and recover all the test1 data. I would appreciate if you can let me >> know the commands. >> >> >> use eZSale >> go >> create table TEST1 (ID INT, NAME VARCHAR(50)) >> create table TEST2(ID INT, NAME VARCHAR(50)) >> >> --Time:10:01 >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_FullDb.BAK' >> >> --Time:10:02 >> INSERT INTO TEST1 SELECT 1, 'AFTER FULL BACKUP' >> INSERT INTO TEST2 SELECT 1, 'AFTER FULL BACKUP' >> >> --Time:10:03 >> BACKUP LOG eZsale to disk = 'c:\Temp\test_Logbk.BAK' >> >> --Time: 10:04 >> INSERT INTO TEST1 SELECT 2, 'AFTER LOG BACKUP' >> INSERT INTO TEST2 SELECT 2, 'AFTER LOG BACKUP' >> INSERT INTO TEST2 SELECT 3, 'AFTER LOG BACKUP..SECOND ENTRY' >> >> --Time: 10:05 >> DROP TABLE TEST2 >> >> --Time: 10:06 >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_DiffDb.BAK' with >> differential >> >> --Time: 10:07 >> INSERT INTO TEST1 SELECT 4, 'AFTER DIFFERNTIAL BACKUP' >> INSERT INTO TEST1 SELECT 5, 'AFTER DIFFERNTIAL BACKUP..SECOND ENTRY' > >> Stay informed about: Point in time recovery

Sean McCown

External

Since: Nov 21, 2007
Posts: 82

(Msg. 4) Posted: Fri Jan 25, 2008 9:20 am Post subject: Re: Point in time recovery [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

In order to do a point in time recovery, you'll need to use the STOPAT command. Here's an example I pulled out of BOL: RESTORE DATABASE AdventureWorks FROM AdventureWorksBackups WITH NORECOVERY; RESTORE LOG AdventureWorks FROM AdventureWorksBackups WITH RECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM'; If you have several logs before the last one that you run with recovery, just do them with norecovery like you did the full backup. So if you had previous logs to restore before you get to the one you want to stop at just say: RESTORE LOG AdventureWorks FROM AdventureWorksBackups with NORECOVERY You can replace that device name with a physical path if you need to so you would also do this if you had a physical path to the file: RESTORE LOG AdventureWorks FROM disk='c:\mylog.trn' with NORECOVERY It's worth it to mention that you lose the STOPAT functionality if you're not in full recovery mode. And as Tibor said, you can only recovery to one point in time. So if you need 2 separate times, you'll have to do 2 restores and merge them manually at the table level. -- Read my book reviews at: www.ITBookworm.com Blog Author of: Database Underground -- http://weblog.infoworld.com/dbunderground/ DBA Rant – http://dbarant.blogspot.com "Smith" wrote: > Can you tell me the step? how I can achieve this. > > Thanks > "Tibor Karaszi" wrote in > message > > Restore will not let you get an inconsistent views of a database. But that > > I mean that it won't allow for a restore where some objects are from one > > point in time and other objects are from another point in time. You can > > restore up to 10:05 into a new database name and move the desired data > > (TEST2) from there into the production database. > > > > -- > > Tibor Karaszi, SQL Server MVP > > http://www.karaszi.com/sqlserver/default.asp > > http://sqlblog.com/blogs/tibor_karaszi > > > > > > "Smith" wrote in message > > > >> Hi Professionals, > >> > >> Following are my queries and I want to recover the table (Test2) with > >> data and recover all the test1 data. I would appreciate if you can let me > >> know the commands. > >> > >> > >> use eZSale > >> go > >> create table TEST1 (ID INT, NAME VARCHAR(50)) > >> create table TEST2(ID INT, NAME VARCHAR(50)) > >> > >> --Time:10:01 > >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_FullDb.BAK' > >> > >> --Time:10:02 > >> INSERT INTO TEST1 SELECT 1, 'AFTER FULL BACKUP' > >> INSERT INTO TEST2 SELECT 1, 'AFTER FULL BACKUP' > >> > >> --Time:10:03 > >> BACKUP LOG eZsale to disk = 'c:\Temp\test_Logbk.BAK' > >> > >> --Time: 10:04 > >> INSERT INTO TEST1 SELECT 2, 'AFTER LOG BACKUP' > >> INSERT INTO TEST2 SELECT 2, 'AFTER LOG BACKUP' > >> INSERT INTO TEST2 SELECT 3, 'AFTER LOG BACKUP..SECOND ENTRY' > >> > >> --Time: 10:05 > >> DROP TABLE TEST2 > >> > >> --Time: 10:06 > >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_DiffDb.BAK' with > >> differential > >> > >> --Time: 10:07 > >> INSERT INTO TEST1 SELECT 4, 'AFTER DIFFERNTIAL BACKUP' > >> INSERT INTO TEST1 SELECT 5, 'AFTER DIFFERNTIAL BACKUP..SECOND ENTRY' > > > >> Stay informed about: Point in time recovery

Smith

External

Since: Jan 22, 2008
Posts: 17

(Msg. 5) Posted: Fri Jan 25, 2008 11:17 am Post subject: Re: Point in time recovery [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Thanks.. that means differential backup doesn't help me in this scanario? Thanks "Sean McCown" wrote in message > In order to do a point in time recovery, you'll need to use the STOPAT > command. > Here's an example I pulled out of BOL: > > RESTORE DATABASE AdventureWorks > FROM AdventureWorksBackups > WITH NORECOVERY; > > RESTORE LOG AdventureWorks > FROM AdventureWorksBackups > WITH RECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM'; > > > If you have several logs before the last one that you run with recovery, > just do them with norecovery like you did the full backup. So if you had > previous logs to restore before you get to the one you want to stop at > just > say: > > RESTORE LOG AdventureWorks FROM AdventureWorksBackups with NORECOVERY > > You can replace that device name with a physical path if you need to so > you > would also do this if you had a physical path to the file: > > RESTORE LOG AdventureWorks FROM disk='c:\mylog.trn' with NORECOVERY > > It's worth it to mention that you lose the STOPAT functionality if you're > not in full recovery mode. > > And as Tibor said, you can only recovery to one point in time. So if you > need 2 separate times, you'll have to do 2 restores and merge them > manually > at the table level. > > > -- > Read my book reviews at: > www.ITBookworm.com > > Blog Author of: > Database Underground -- http://weblog.infoworld.com/dbunderground/ > DBA Rant – http://dbarant.blogspot.com > > > > > "Smith" wrote: > >> Can you tell me the step? how I can achieve this. >> >> Thanks >> "Tibor Karaszi" wrote >> in >> message >> > Restore will not let you get an inconsistent views of a database. But >> > that >> > I mean that it won't allow for a restore where some objects are from >> > one >> > point in time and other objects are from another point in time. You can >> > restore up to 10:05 into a new database name and move the desired data >> > (TEST2) from there into the production database. >> > >> > -- >> > Tibor Karaszi, SQL Server MVP >> > http://www.karaszi.com/sqlserver/default.asp >> > http://sqlblog.com/blogs/tibor_karaszi >> > >> > >> > "Smith" wrote in message >> > >> >> Hi Professionals, >> >> >> >> Following are my queries and I want to recover the table (Test2) with >> >> data and recover all the test1 data. I would appreciate if you can let >> >> me >> >> know the commands. >> >> >> >> >> >> use eZSale >> >> go >> >> create table TEST1 (ID INT, NAME VARCHAR(50)) >> >> create table TEST2(ID INT, NAME VARCHAR(50)) >> >> >> >> --Time:10:01 >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_FullDb.BAK' >> >> >> >> --Time:10:02 >> >> INSERT INTO TEST1 SELECT 1, 'AFTER FULL BACKUP' >> >> INSERT INTO TEST2 SELECT 1, 'AFTER FULL BACKUP' >> >> >> >> --Time:10:03 >> >> BACKUP LOG eZsale to disk = 'c:\Temp\test_Logbk.BAK' >> >> >> >> --Time: 10:04 >> >> INSERT INTO TEST1 SELECT 2, 'AFTER LOG BACKUP' >> >> INSERT INTO TEST2 SELECT 2, 'AFTER LOG BACKUP' >> >> INSERT INTO TEST2 SELECT 3, 'AFTER LOG BACKUP..SECOND ENTRY' >> >> >> >> --Time: 10:05 >> >> DROP TABLE TEST2 >> >> >> >> --Time: 10:06 >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_DiffDb.BAK' with >> >> differential >> >> >> >> --Time: 10:07 >> >> INSERT INTO TEST1 SELECT 4, 'AFTER DIFFERNTIAL BACKUP' >> >> INSERT INTO TEST1 SELECT 5, 'AFTER DIFFERNTIAL BACKUP..SECOND ENTRY' >> > >> >> Stay informed about: Point in time recovery

Sean McCown

External

Since: Nov 21, 2007
Posts: 82

(Msg. 6) Posted: Fri Jan 25, 2008 11:17 am Post subject: Re: Point in time recovery [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

You are correct. You lost STOPAT with diffs. Don't forget to mark the issue as resolved if you got your answer. thx. -- Read my book reviews at: www.ITBookworm.com Blog Author of: Database Underground -- http://weblog.infoworld.com/dbunderground/ DBA Rant – http://dbarant.blogspot.com "Smith" wrote: > Thanks.. that means differential backup doesn't help me in this scanario? > > Thanks > > "Sean McCown" wrote in message > > > In order to do a point in time recovery, you'll need to use the STOPAT > > command. > > Here's an example I pulled out of BOL: > > > > RESTORE DATABASE AdventureWorks > > FROM AdventureWorksBackups > > WITH NORECOVERY; > > > > RESTORE LOG AdventureWorks > > FROM AdventureWorksBackups > > WITH RECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM'; > > > > > > If you have several logs before the last one that you run with recovery, > > just do them with norecovery like you did the full backup. So if you had > > previous logs to restore before you get to the one you want to stop at > > just > > say: > > > > RESTORE LOG AdventureWorks FROM AdventureWorksBackups with NORECOVERY > > > > You can replace that device name with a physical path if you need to so > > you > > would also do this if you had a physical path to the file: > > > > RESTORE LOG AdventureWorks FROM disk='c:\mylog.trn' with NORECOVERY > > > > It's worth it to mention that you lose the STOPAT functionality if you're > > not in full recovery mode. > > > > And as Tibor said, you can only recovery to one point in time. So if you > > need 2 separate times, you'll have to do 2 restores and merge them > > manually > > at the table level. > > > > > > -- > > Read my book reviews at: > > www.ITBookworm.com > > > > Blog Author of: > > Database Underground -- http://weblog.infoworld.com/dbunderground/ > > DBA Rant – http://dbarant.blogspot.com > > > > > > > > > > "Smith" wrote: > > > >> Can you tell me the step? how I can achieve this. > >> > >> Thanks > >> "Tibor Karaszi" wrote > >> in > >> message > >> > Restore will not let you get an inconsistent views of a database. But > >> > that > >> > I mean that it won't allow for a restore where some objects are from > >> > one > >> > point in time and other objects are from another point in time. You can > >> > restore up to 10:05 into a new database name and move the desired data > >> > (TEST2) from there into the production database. > >> > > >> > -- > >> > Tibor Karaszi, SQL Server MVP > >> > http://www.karaszi.com/sqlserver/default.asp > >> > http://sqlblog.com/blogs/tibor_karaszi > >> > > >> > > >> > "Smith" wrote in message > >> > > >> >> Hi Professionals, > >> >> > >> >> Following are my queries and I want to recover the table (Test2) with > >> >> data and recover all the test1 data. I would appreciate if you can let > >> >> me > >> >> know the commands. > >> >> > >> >> > >> >> use eZSale > >> >> go > >> >> create table TEST1 (ID INT, NAME VARCHAR(50)) > >> >> create table TEST2(ID INT, NAME VARCHAR(50)) > >> >> > >> >> --Time:10:01 > >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_FullDb.BAK' > >> >> > >> >> --Time:10:02 > >> >> INSERT INTO TEST1 SELECT 1, 'AFTER FULL BACKUP' > >> >> INSERT INTO TEST2 SELECT 1, 'AFTER FULL BACKUP' > >> >> > >> >> --Time:10:03 > >> >> BACKUP LOG eZsale to disk = 'c:\Temp\test_Logbk.BAK' > >> >> > >> >> --Time: 10:04 > >> >> INSERT INTO TEST1 SELECT 2, 'AFTER LOG BACKUP' > >> >> INSERT INTO TEST2 SELECT 2, 'AFTER LOG BACKUP' > >> >> INSERT INTO TEST2 SELECT 3, 'AFTER LOG BACKUP..SECOND ENTRY' > >> >> > >> >> --Time: 10:05 > >> >> DROP TABLE TEST2 > >> >> > >> >> --Time: 10:06 > >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_DiffDb.BAK' with > >> >> differential > >> >> > >> >> --Time: 10:07 > >> >> INSERT INTO TEST1 SELECT 4, 'AFTER DIFFERNTIAL BACKUP' > >> >> INSERT INTO TEST1 SELECT 5, 'AFTER DIFFERNTIAL BACKUP..SECOND ENTRY' > >> > > >> > >> Stay informed about: Point in time recovery

Smith

External

Since: Jan 22, 2008
Posts: 17

(Msg. 7) Posted: Fri Jan 25, 2008 11:39 am Post subject: Re: Point in time recovery [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

That means I can't differential backup when I am trying to do point in time recovery? "Sean McCown" wrote in message > You are correct. You lost STOPAT with diffs. > > Don't forget to mark the issue as resolved if you got your answer. > thx. > -- > Read my book reviews at: > www.ITBookworm.com > > Blog Author of: > Database Underground -- http://weblog.infoworld.com/dbunderground/ > DBA Rant – http://dbarant.blogspot.com > > > > > "Smith" wrote: > >> Thanks.. that means differential backup doesn't help me in this scanario? >> >> Thanks >> >> "Sean McCown" wrote in message >> >> > In order to do a point in time recovery, you'll need to use the STOPAT >> > command. >> > Here's an example I pulled out of BOL: >> > >> > RESTORE DATABASE AdventureWorks >> > FROM AdventureWorksBackups >> > WITH NORECOVERY; >> > >> > RESTORE LOG AdventureWorks >> > FROM AdventureWorksBackups >> > WITH RECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM'; >> > >> > >> > If you have several logs before the last one that you run with >> > recovery, >> > just do them with norecovery like you did the full backup. So if you >> > had >> > previous logs to restore before you get to the one you want to stop at >> > just >> > say: >> > >> > RESTORE LOG AdventureWorks FROM AdventureWorksBackups with NORECOVERY >> > >> > You can replace that device name with a physical path if you need to so >> > you >> > would also do this if you had a physical path to the file: >> > >> > RESTORE LOG AdventureWorks FROM disk='c:\mylog.trn' with NORECOVERY >> > >> > It's worth it to mention that you lose the STOPAT functionality if >> > you're >> > not in full recovery mode. >> > >> > And as Tibor said, you can only recovery to one point in time. So if >> > you >> > need 2 separate times, you'll have to do 2 restores and merge them >> > manually >> > at the table level. >> > >> > >> > -- >> > Read my book reviews at: >> > www.ITBookworm.com >> > >> > Blog Author of: >> > Database Underground -- http://weblog.infoworld.com/dbunderground/ >> > DBA Rant – http://dbarant.blogspot.com >> > >> > >> > >> > >> > "Smith" wrote: >> > >> >> Can you tell me the step? how I can achieve this. >> >> >> >> Thanks >> >> "Tibor Karaszi" >> >> wrote >> >> in >> >> message >> >> > Restore will not let you get an inconsistent views of a database. >> >> > But >> >> > that >> >> > I mean that it won't allow for a restore where some objects are from >> >> > one >> >> > point in time and other objects are from another point in time. You >> >> > can >> >> > restore up to 10:05 into a new database name and move the desired >> >> > data >> >> > (TEST2) from there into the production database. >> >> > >> >> > -- >> >> > Tibor Karaszi, SQL Server MVP >> >> > http://www.karaszi.com/sqlserver/default.asp >> >> > http://sqlblog.com/blogs/tibor_karaszi >> >> > >> >> > >> >> > "Smith" wrote in message >> >> > >> >> >> Hi Professionals, >> >> >> >> >> >> Following are my queries and I want to recover the table (Test2) >> >> >> with >> >> >> data and recover all the test1 data. I would appreciate if you can >> >> >> let >> >> >> me >> >> >> know the commands. >> >> >> >> >> >> >> >> >> use eZSale >> >> >> go >> >> >> create table TEST1 (ID INT, NAME VARCHAR(50)) >> >> >> create table TEST2(ID INT, NAME VARCHAR(50)) >> >> >> >> >> >> --Time:10:01 >> >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_FullDb.BAK' >> >> >> >> >> >> --Time:10:02 >> >> >> INSERT INTO TEST1 SELECT 1, 'AFTER FULL BACKUP' >> >> >> INSERT INTO TEST2 SELECT 1, 'AFTER FULL BACKUP' >> >> >> >> >> >> --Time:10:03 >> >> >> BACKUP LOG eZsale to disk = 'c:\Temp\test_Logbk.BAK' >> >> >> >> >> >> --Time: 10:04 >> >> >> INSERT INTO TEST1 SELECT 2, 'AFTER LOG BACKUP' >> >> >> INSERT INTO TEST2 SELECT 2, 'AFTER LOG BACKUP' >> >> >> INSERT INTO TEST2 SELECT 3, 'AFTER LOG BACKUP..SECOND ENTRY' >> >> >> >> >> >> --Time: 10:05 >> >> >> DROP TABLE TEST2 >> >> >> >> >> >> --Time: 10:06 >> >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_DiffDb.BAK' with >> >> >> differential >> >> >> >> >> >> --Time: 10:07 >> >> >> INSERT INTO TEST1 SELECT 4, 'AFTER DIFFERNTIAL BACKUP' >> >> >> INSERT INTO TEST1 SELECT 5, 'AFTER DIFFERNTIAL BACKUP..SECOND >> >> >> ENTRY' >> >> > >> >> >> >> Stay informed about: Point in time recovery

Sean McCown

External

Since: Nov 21, 2007
Posts: 82

(Msg. 8) Posted: Fri Jan 25, 2008 11:39 am Post subject: Re: Point in time recovery [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

That's exactly what that means. So just to state it to be clear... you have to use log backups to have point in time recovery. You also have to be in full recovery mode. Bulk-logged won't give you point in time. -- Read my book reviews at: www.ITBookworm.com Blog Author of: Database Underground -- http://weblog.infoworld.com/dbunderground/ DBA Rant – http://dbarant.blogspot.com "Smith" wrote: > That means I can't differential backup when I am trying to do point in time > recovery? > > "Sean McCown" wrote in message > > > You are correct. You lost STOPAT with diffs. > > > > Don't forget to mark the issue as resolved if you got your answer. > > thx. > > -- > > Read my book reviews at: > > www.ITBookworm.com > > > > Blog Author of: > > Database Underground -- http://weblog.infoworld.com/dbunderground/ > > DBA Rant – http://dbarant.blogspot.com > > > > > > > > > > "Smith" wrote: > > > >> Thanks.. that means differential backup doesn't help me in this scanario? > >> > >> Thanks > >> > >> "Sean McCown" wrote in message > >> > >> > In order to do a point in time recovery, you'll need to use the STOPAT > >> > command. > >> > Here's an example I pulled out of BOL: > >> > > >> > RESTORE DATABASE AdventureWorks > >> > FROM AdventureWorksBackups > >> > WITH NORECOVERY; > >> > > >> > RESTORE LOG AdventureWorks > >> > FROM AdventureWorksBackups > >> > WITH RECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM'; > >> > > >> > > >> > If you have several logs before the last one that you run with > >> > recovery, > >> > just do them with norecovery like you did the full backup. So if you > >> > had > >> > previous logs to restore before you get to the one you want to stop at > >> > just > >> > say: > >> > > >> > RESTORE LOG AdventureWorks FROM AdventureWorksBackups with NORECOVERY > >> > > >> > You can replace that device name with a physical path if you need to so > >> > you > >> > would also do this if you had a physical path to the file: > >> > > >> > RESTORE LOG AdventureWorks FROM disk='c:\mylog.trn' with NORECOVERY > >> > > >> > It's worth it to mention that you lose the STOPAT functionality if > >> > you're > >> > not in full recovery mode. > >> > > >> > And as Tibor said, you can only recovery to one point in time. So if > >> > you > >> > need 2 separate times, you'll have to do 2 restores and merge them > >> > manually > >> > at the table level. > >> > > >> > > >> > -- > >> > Read my book reviews at: > >> > www.ITBookworm.com > >> > > >> > Blog Author of: > >> > Database Underground -- http://weblog.infoworld.com/dbunderground/ > >> > DBA Rant – http://dbarant.blogspot.com > >> > > >> > > >> > > >> > > >> > "Smith" wrote: > >> > > >> >> Can you tell me the step? how I can achieve this. > >> >> > >> >> Thanks > >> >> "Tibor Karaszi" > >> >> wrote > >> >> in > >> >> message > >> >> > Restore will not let you get an inconsistent views of a database. > >> >> > But > >> >> > that > >> >> > I mean that it won't allow for a restore where some objects are from > >> >> > one > >> >> > point in time and other objects are from another point in time. You > >> >> > can > >> >> > restore up to 10:05 into a new database name and move the desired > >> >> > data > >> >> > (TEST2) from there into the production database. > >> >> > > >> >> > -- > >> >> > Tibor Karaszi, SQL Server MVP > >> >> > http://www.karaszi.com/sqlserver/default.asp > >> >> > http://sqlblog.com/blogs/tibor_karaszi > >> >> > > >> >> > > >> >> > "Smith" wrote in message > >> >> > > >> >> >> Hi Professionals, > >> >> >> > >> >> >> Following are my queries and I want to recover the table (Test2) > >> >> >> with > >> >> >> data and recover all the test1 data. I would appreciate if you can > >> >> >> let > >> >> >> me > >> >> >> know the commands. > >> >> >> > >> >> >> > >> >> >> use eZSale > >> >> >> go > >> >> >> create table TEST1 (ID INT, NAME VARCHAR(50)) > >> >> >> create table TEST2(ID INT, NAME VARCHAR(50)) > >> >> >> > >> >> >> --Time:10:01 > >> >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_FullDb.BAK' > >> >> >> > >> >> >> --Time:10:02 > >> >> >> INSERT INTO TEST1 SELECT 1, 'AFTER FULL BACKUP' > >> >> >> INSERT INTO TEST2 SELECT 1, 'AFTER FULL BACKUP' > >> >> >> > >> >> >> --Time:10:03 > >> >> >> BACKUP LOG eZsale to disk = 'c:\Temp\test_Logbk.BAK' > >> >> >> > >> >> >> --Time: 10:04 > >> >> >> INSERT INTO TEST1 SELECT 2, 'AFTER LOG BACKUP' > >> >> >> INSERT INTO TEST2 SELECT 2, 'AFTER LOG BACKUP' > >> >> >> INSERT INTO TEST2 SELECT 3, 'AFTER LOG BACKUP..SECOND ENTRY' > >> >> >> > >> >> >> --Time: 10:05 > >> >> >> DROP TABLE TEST2 > >> >> >> > >> >> >> --Time: 10:06 > >> >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_DiffDb.BAK' with > >> >> >> differential > >> >> >> > >> >> >> --Time: 10:07 > >> >> >> INSERT INTO TEST1 SELECT 4, 'AFTER DIFFERNTIAL BACKUP' > >> >> >> INSERT INTO TEST1 SELECT 5, 'AFTER DIFFERNTIAL BACKUP..SECOND > >> >> >> ENTRY' > >> >> > > >> >> > >> > >> Stay informed about: Point in time recovery

Smith

External

Since: Jan 22, 2008
Posts: 17

(Msg. 9) Posted: Fri Jan 25, 2008 2:12 pm Post subject: Re: Point in time recovery [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

thanks but if we first restore full backup with norecovery and then differential backup with norecovery and then log backup with recovery with StopAt option, will that give point in time recovery? "Sean McCown" wrote in message > That's exactly what that means. > So just to state it to be clear... you have to use log backups to have > point > in time recovery. You also have to be in full recovery mode. Bulk-logged > won't give you point in time. > > -- > Read my book reviews at: > www.ITBookworm.com > > Blog Author of: > Database Underground -- http://weblog.infoworld.com/dbunderground/ > DBA Rant – http://dbarant.blogspot.com > > > > > "Smith" wrote: > >> That means I can't differential backup when I am trying to do point in >> time >> recovery? >> >> "Sean McCown" wrote in message >> >> > You are correct. You lost STOPAT with diffs. >> > >> > Don't forget to mark the issue as resolved if you got your answer. >> > thx. >> > -- >> > Read my book reviews at: >> > www.ITBookworm.com >> > >> > Blog Author of: >> > Database Underground -- http://weblog.infoworld.com/dbunderground/ >> > DBA Rant – http://dbarant.blogspot.com >> > >> > >> > >> > >> > "Smith" wrote: >> > >> >> Thanks.. that means differential backup doesn't help me in this >> >> scanario? >> >> >> >> Thanks >> >> >> >> "Sean McCown" wrote in message >> >> >> >> > In order to do a point in time recovery, you'll need to use the >> >> > STOPAT >> >> > command. >> >> > Here's an example I pulled out of BOL: >> >> > >> >> > RESTORE DATABASE AdventureWorks >> >> > FROM AdventureWorksBackups >> >> > WITH NORECOVERY; >> >> > >> >> > RESTORE LOG AdventureWorks >> >> > FROM AdventureWorksBackups >> >> > WITH RECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM'; >> >> > >> >> > >> >> > If you have several logs before the last one that you run with >> >> > recovery, >> >> > just do them with norecovery like you did the full backup. So if >> >> > you >> >> > had >> >> > previous logs to restore before you get to the one you want to stop >> >> > at >> >> > just >> >> > say: >> >> > >> >> > RESTORE LOG AdventureWorks FROM AdventureWorksBackups with >> >> > NORECOVERY >> >> > >> >> > You can replace that device name with a physical path if you need to >> >> > so >> >> > you >> >> > would also do this if you had a physical path to the file: >> >> > >> >> > RESTORE LOG AdventureWorks FROM disk='c:\mylog.trn' with NORECOVERY >> >> > >> >> > It's worth it to mention that you lose the STOPAT functionality if >> >> > you're >> >> > not in full recovery mode. >> >> > >> >> > And as Tibor said, you can only recovery to one point in time. So >> >> > if >> >> > you >> >> > need 2 separate times, you'll have to do 2 restores and merge them >> >> > manually >> >> > at the table level. >> >> > >> >> > >> >> > -- >> >> > Read my book reviews at: >> >> > www.ITBookworm.com >> >> > >> >> > Blog Author of: >> >> > Database Underground -- http://weblog.infoworld.com/dbunderground/ >> >> > DBA Rant – http://dbarant.blogspot.com >> >> > >> >> > >> >> > >> >> > >> >> > "Smith" wrote: >> >> > >> >> >> Can you tell me the step? how I can achieve this. >> >> >> >> >> >> Thanks >> >> >> "Tibor Karaszi" >> >> >> wrote >> >> >> in >> >> >> message >> >> >> > Restore will not let you get an inconsistent views of a database. >> >> >> > But >> >> >> > that >> >> >> > I mean that it won't allow for a restore where some objects are >> >> >> > from >> >> >> > one >> >> >> > point in time and other objects are from another point in time. >> >> >> > You >> >> >> > can >> >> >> > restore up to 10:05 into a new database name and move the desired >> >> >> > data >> >> >> > (TEST2) from there into the production database. >> >> >> > >> >> >> > -- >> >> >> > Tibor Karaszi, SQL Server MVP >> >> >> > http://www.karaszi.com/sqlserver/default.asp >> >> >> > http://sqlblog.com/blogs/tibor_karaszi >> >> >> > >> >> >> > >> >> >> > "Smith" wrote in message >> >> >> > >> >> >> >> Hi Professionals, >> >> >> >> >> >> >> >> Following are my queries and I want to recover the table (Test2) >> >> >> >> with >> >> >> >> data and recover all the test1 data. I would appreciate if you >> >> >> >> can >> >> >> >> let >> >> >> >> me >> >> >> >> know the commands. >> >> >> >> >> >> >> >> >> >> >> >> use eZSale >> >> >> >> go >> >> >> >> create table TEST1 (ID INT, NAME VARCHAR(50)) >> >> >> >> create table TEST2(ID INT, NAME VARCHAR(50)) >> >> >> >> >> >> >> >> --Time:10:01 >> >> >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_FullDb.BAK' >> >> >> >> >> >> >> >> --Time:10:02 >> >> >> >> INSERT INTO TEST1 SELECT 1, 'AFTER FULL BACKUP' >> >> >> >> INSERT INTO TEST2 SELECT 1, 'AFTER FULL BACKUP' >> >> >> >> >> >> >> >> --Time:10:03 >> >> >> >> BACKUP LOG eZsale to disk = 'c:\Temp\test_Logbk.BAK' >> >> >> >> >> >> >> >> --Time: 10:04 >> >> >> >> INSERT INTO TEST1 SELECT 2, 'AFTER LOG BACKUP' >> >> >> >> INSERT INTO TEST2 SELECT 2, 'AFTER LOG BACKUP' >> >> >> >> INSERT INTO TEST2 SELECT 3, 'AFTER LOG BACKUP..SECOND ENTRY' >> >> >> >> >> >> >> >> --Time: 10:05 >> >> >> >> DROP TABLE TEST2 >> >> >> >> >> >> >> >> --Time: 10:06 >> >> >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_DiffDb.BAK' with >> >> >> >> differential >> >> >> >> >> >> >> >> --Time: 10:07 >> >> >> >> INSERT INTO TEST1 SELECT 4, 'AFTER DIFFERNTIAL BACKUP' >> >> >> >> INSERT INTO TEST1 SELECT 5, 'AFTER DIFFERNTIAL BACKUP..SECOND >> >> >> >> ENTRY' >> >> >> > >> >> >> >> >> >> >> Stay informed about: Point in time recovery

Sean McCown

External

Since: Nov 21, 2007
Posts: 82

(Msg. 10) Posted: Fri Jan 25, 2008 2:12 pm Post subject: Re: Point in time recovery [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Not having covered that specific scenario with STOPAT, I'm not sure. It sounds like it should work. I'm thinking that you just can't use STOPAT when restoring the actual diff... so your chances are high. I'd say y, it should be possible. Just kick off a quick test real quick... shouldn't be that hard, right? -- Read my book reviews at: www.ITBookworm.com Blog Author of: Database Underground -- http://weblog.infoworld.com/dbunderground/ DBA Rant – http://dbarant.blogspot.com "Smith" wrote: > thanks but if we first restore full backup with norecovery and then > differential backup with norecovery and then log backup with recovery with > StopAt option, will that give point in time recovery? > > "Sean McCown" wrote in message > > > That's exactly what that means. > > So just to state it to be clear... you have to use log backups to have > > point > > in time recovery. You also have to be in full recovery mode. Bulk-logged > > won't give you point in time. > > > > -- > > Read my book reviews at: > > www.ITBookworm.com > > > > Blog Author of: > > Database Underground -- http://weblog.infoworld.com/dbunderground/ > > DBA Rant – http://dbarant.blogspot.com > > > > > > > > > > "Smith" wrote: > > > >> That means I can't differential backup when I am trying to do point in > >> time > >> recovery? > >> > >> "Sean McCown" wrote in message > >> > >> > You are correct. You lost STOPAT with diffs. > >> > > >> > Don't forget to mark the issue as resolved if you got your answer. > >> > thx. > >> > -- > >> > Read my book reviews at: > >> > www.ITBookworm.com > >> > > >> > Blog Author of: > >> > Database Underground -- http://weblog.infoworld.com/dbunderground/ > >> > DBA Rant – http://dbarant.blogspot.com > >> > > >> > > >> > > >> > > >> > "Smith" wrote: > >> > > >> >> Thanks.. that means differential backup doesn't help me in this > >> >> scanario? > >> >> > >> >> Thanks > >> >> > >> >> "Sean McCown" wrote in message > >> >> > >> >> > In order to do a point in time recovery, you'll need to use the > >> >> > STOPAT > >> >> > command. > >> >> > Here's an example I pulled out of BOL: > >> >> > > >> >> > RESTORE DATABASE AdventureWorks > >> >> > FROM AdventureWorksBackups > >> >> > WITH NORECOVERY; > >> >> > > >> >> > RESTORE LOG AdventureWorks > >> >> > FROM AdventureWorksBackups > >> >> > WITH RECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM'; > >> >> > > >> >> > > >> >> > If you have several logs before the last one that you run with > >> >> > recovery, > >> >> > just do them with norecovery like you did the full backup. So if > >> >> > you > >> >> > had > >> >> > previous logs to restore before you get to the one you want to stop > >> >> > at > >> >> > just > >> >> > say: > >> >> > > >> >> > RESTORE LOG AdventureWorks FROM AdventureWorksBackups with > >> >> > NORECOVERY > >> >> > > >> >> > You can replace that device name with a physical path if you need to > >> >> > so > >> >> > you > >> >> > would also do this if you had a physical path to the file: > >> >> > > >> >> > RESTORE LOG AdventureWorks FROM disk='c:\mylog.trn' with NORECOVERY > >> >> > > >> >> > It's worth it to mention that you lose the STOPAT functionality if > >> >> > you're > >> >> > not in full recovery mode. > >> >> > > >> >> > And as Tibor said, you can only recovery to one point in time. So > >> >> > if > >> >> > you > >> >> > need 2 separate times, you'll have to do 2 restores and merge them > >> >> > manually > >> >> > at the table level. > >> >> > > >> >> > > >> >> > -- > >> >> > Read my book reviews at: > >> >> > www.ITBookworm.com > >> >> > > >> >> > Blog Author of: > >> >> > Database Underground -- http://weblog.infoworld.com/dbunderground/ > >> >> > DBA Rant – http://dbarant.blogspot.com > >> >> > > >> >> > > >> >> > > >> >> > > >> >> > "Smith" wrote: > >> >> > > >> >> >> Can you tell me the step? how I can achieve this. > >> >> >> > >> >> >> Thanks > >> >> >> "Tibor Karaszi" > >> >> >> wrote > >> >> >> in > >> >> >> message > >> >> >> > Restore will not let you get an inconsistent views of a database. > >> >> >> > But > >> >> >> > that > >> >> >> > I mean that it won't allow for a restore where some objects are > >> >> >> > from > >> >> >> > one > >> >> >> > point in time and other objects are from another point in time. > >> >> >> > You > >> >> >> > can > >> >> >> > restore up to 10:05 into a new database name and move the desired > >> >> >> > data > >> >> >> > (TEST2) from there into the production database. > >> >> >> > > >> >> >> > -- > >> >> >> > Tibor Karaszi, SQL Server MVP > >> >> >> > http://www.karaszi.com/sqlserver/default.asp > >> >> >> > http://sqlblog.com/blogs/tibor_karaszi > >> >> >> > > >> >> >> > > >> >> >> > "Smith" wrote in message > >> >> >> > > >> >> >> >> Hi Professionals, > >> >> >> >> > >> >> >> >> Following are my queries and I want to recover the table (Test2) > >> >> >> >> with > >> >> >> >> data and recover all the test1 data. I would appreciate if you > >> >> >> >> can > >> >> >> >> let > >> >> >> >> me > >> >> >> >> know the commands. > >> >> >> >> > >> >> >> >> > >> >> >> >> use eZSale > >> >> >> >> go > >> >> >> >> create table TEST1 (ID INT, NAME VARCHAR(50)) > >> >> >> >> create table TEST2(ID INT, NAME VARCHAR(50)) > >> >> >> >> > >> >> >> >> --Time:10:01 > >> >> >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_FullDb.BAK' > >> >> >> >> > >> >> >> >> --Time:10:02 > >> >> >> >> INSERT INTO TEST1 SELECT 1, 'AFTER FULL BACKUP' > >> >> >> >> INSERT INTO TEST2 SELECT 1, 'AFTER FULL BACKUP' > >> >> >> >> > >> >> >> >> --Time:10:03 > >> >> >> >> BACKUP LOG eZsale to disk = 'c:\Temp\test_Logbk.BAK' > >> >> >> >> > >> >> >> >> --Time: 10:04 > >> >> >> >> INSERT INTO TEST1 SELECT 2, 'AFTER LOG BACKUP' > >> >> >> >> INSERT INTO TEST2 SELECT 2, 'AFTER LOG BACKUP' > >> >> >> >> INSERT INTO TEST2 SELECT 3, 'AFTER LOG BACKUP..SECOND ENTRY' > >> >> >> >> > >> >> >> >> --Time: 10:05 > >> >> >> >> DROP TABLE TEST2 > >> >> >> >> > >> >> >> >> --Time: 10:06 > >> >> >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_DiffDb.BAK' with > >> >> >> >> differential > >> >> >> >> > >> >> >> >> --Time: 10:07 > >> >> >> >> INSERT INTO TEST1 SELECT 4, 'AFTER DIFFERNTIAL BACKUP' > >> >> >> >> INSERT INTO TEST1 SELECT 5, 'AFTER DIFFERNTIAL BACKUP..SECOND > >> >> >> >> ENTRY' > >> >> >> > > >> >> >> > >> >> > >> > >> Stay informed about: Point in time recovery

Russell Fields

External

Since: Feb 21, 2007
Posts: 457

(Msg. 11) Posted: Fri Jan 25, 2008 4:29 pm Post subject: Re: Point in time recovery [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Smith, Yes, that will work. RLF "Smith" wrote in message > thanks but if we first restore full backup with norecovery and then > differential backup with norecovery and then log backup with recovery with > StopAt option, will that give point in time recovery? > > "Sean McCown" wrote in message > >> That's exactly what that means. >> So just to state it to be clear... you have to use log backups to have >> point >> in time recovery. You also have to be in full recovery mode. >> Bulk-logged >> won't give you point in time. >> >> -- >> Read my book reviews at: >> www.ITBookworm.com >> >> Blog Author of: >> Database Underground -- http://weblog.infoworld.com/dbunderground/ >> DBA Rant - http://dbarant.blogspot.com >> >> >> >> >> "Smith" wrote: >> >>> That means I can't differential backup when I am trying to do point in >>> time >>> recovery? >>> >>> "Sean McCown" wrote in message >>> >>> > You are correct. You lost STOPAT with diffs. >>> > >>> > Don't forget to mark the issue as resolved if you got your answer. >>> > thx. >>> > -- >>> > Read my book reviews at: >>> > www.ITBookworm.com >>> > >>> > Blog Author of: >>> > Database Underground -- http://weblog.infoworld.com/dbunderground/ >>> > DBA Rant - http://dbarant.blogspot.com >>> > >>> > >>> > >>> > >>> > "Smith" wrote: >>> > >>> >> Thanks.. that means differential backup doesn't help me in this >>> >> scanario? >>> >> >>> >> Thanks >>> >> >>> >> "Sean McCown" wrote in message >>> >> >>> >> > In order to do a point in time recovery, you'll need to use the >>> >> > STOPAT >>> >> > command. >>> >> > Here's an example I pulled out of BOL: >>> >> > >>> >> > RESTORE DATABASE AdventureWorks >>> >> > FROM AdventureWorksBackups >>> >> > WITH NORECOVERY; >>> >> > >>> >> > RESTORE LOG AdventureWorks >>> >> > FROM AdventureWorksBackups >>> >> > WITH RECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM'; >>> >> > >>> >> > >>> >> > If you have several logs before the last one that you run with >>> >> > recovery, >>> >> > just do them with norecovery like you did the full backup. So if >>> >> > you >>> >> > had >>> >> > previous logs to restore before you get to the one you want to stop >>> >> > at >>> >> > just >>> >> > say: >>> >> > >>> >> > RESTORE LOG AdventureWorks FROM AdventureWorksBackups with >>> >> > NORECOVERY >>> >> > >>> >> > You can replace that device name with a physical path if you need >>> >> > to so >>> >> > you >>> >> > would also do this if you had a physical path to the file: >>> >> > >>> >> > RESTORE LOG AdventureWorks FROM disk='c:\mylog.trn' with NORECOVERY >>> >> > >>> >> > It's worth it to mention that you lose the STOPAT functionality if >>> >> > you're >>> >> > not in full recovery mode. >>> >> > >>> >> > And as Tibor said, you can only recovery to one point in time. So >>> >> > if >>> >> > you >>> >> > need 2 separate times, you'll have to do 2 restores and merge them >>> >> > manually >>> >> > at the table level. >>> >> > >>> >> > >>> >> > -- >>> >> > Read my book reviews at: >>> >> > www.ITBookworm.com >>> >> > >>> >> > Blog Author of: >>> >> > Database Underground -- http://weblog.infoworld.com/dbunderground/ >>> >> > DBA Rant - http://dbarant.blogspot.com >>> >> > >>> >> > >>> >> > >>> >> > >>> >> > "Smith" wrote: >>> >> > >>> >> >> Can you tell me the step? how I can achieve this. >>> >> >> >>> >> >> Thanks >>> >> >> "Tibor Karaszi" >>> >> >> wrote >>> >> >> in >>> >> >> message >>> >> >> > Restore will not let you get an inconsistent views of a >>> >> >> > database. >>> >> >> > But >>> >> >> > that >>> >> >> > I mean that it won't allow for a restore where some objects are >>> >> >> > from >>> >> >> > one >>> >> >> > point in time and other objects are from another point in time. >>> >> >> > You >>> >> >> > can >>> >> >> > restore up to 10:05 into a new database name and move the >>> >> >> > desired >>> >> >> > data >>> >> >> > (TEST2) from there into the production database. >>> >> >> > >>> >> >> > -- >>> >> >> > Tibor Karaszi, SQL Server MVP >>> >> >> > http://www.karaszi.com/sqlserver/default.asp >>> >> >> > http://sqlblog.com/blogs/tibor_karaszi >>> >> >> > >>> >> >> > >>> >> >> > "Smith" wrote in message >>> >> >> > >>> >> >> >> Hi Professionals, >>> >> >> >> >>> >> >> >> Following are my queries and I want to recover the table >>> >> >> >> (Test2) >>> >> >> >> with >>> >> >> >> data and recover all the test1 data. I would appreciate if you >>> >> >> >> can >>> >> >> >> let >>> >> >> >> me >>> >> >> >> know the commands. >>> >> >> >> >>> >> >> >> >>> >> >> >> use eZSale >>> >> >> >> go >>> >> >> >> create table TEST1 (ID INT, NAME VARCHAR(50)) >>> >> >> >> create table TEST2(ID INT, NAME VARCHAR(50)) >>> >> >> >> >>> >> >> >> --Time:10:01 >>> >> >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_FullDb.BAK' >>> >> >> >> >>> >> >> >> --Time:10:02 >>> >> >> >> INSERT INTO TEST1 SELECT 1, 'AFTER FULL BACKUP' >>> >> >> >> INSERT INTO TEST2 SELECT 1, 'AFTER FULL BACKUP' >>> >> >> >> >>> >> >> >> --Time:10:03 >>> >> >> >> BACKUP LOG eZsale to disk = 'c:\Temp\test_Logbk.BAK' >>> >> >> >> >>> >> >> >> --Time: 10:04 >>> >> >> >> INSERT INTO TEST1 SELECT 2, 'AFTER LOG BACKUP' >>> >> >> >> INSERT INTO TEST2 SELECT 2, 'AFTER LOG BACKUP' >>> >> >> >> INSERT INTO TEST2 SELECT 3, 'AFTER LOG BACKUP..SECOND ENTRY' >>> >> >> >> >>> >> >> >> --Time: 10:05 >>> >> >> >> DROP TABLE TEST2 >>> >> >> >> >>> >> >> >> --Time: 10:06 >>> >> >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_DiffDb.BAK' with >>> >> >> >> differential >>> >> >> >> >>> >> >> >> --Time: 10:07 >>> >> >> >> INSERT INTO TEST1 SELECT 4, 'AFTER DIFFERNTIAL BACKUP' >>> >> >> >> INSERT INTO TEST1 SELECT 5, 'AFTER DIFFERNTIAL BACKUP..SECOND >>> >> >> >> ENTRY' >>> >> >> > >>> >> >> >>> >> >>> > >> Stay informed about: Point in time recovery

Tibor Karaszi

External

Since: Jan 29, 2004
Posts: 891

(Msg. 12) Posted: Sat Jan 26, 2008 6:10 am Post subject: Re: Point in time recovery [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

.... but to make it perfectly clear: for the entire database. There's no "restore only these table to this point in time" functionality in SQL Server. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Russell Fields" wrote in message > Smith, > > Yes, that will work. > > RLF > > "Smith" wrote in message > >> thanks but if we first restore full backup with norecovery and then differential backup with >> norecovery and then log backup with recovery with StopAt option, will that give point in time >> recovery? >> >> "Sean McCown" wrote in message >> >>> That's exactly what that means. >>> So just to state it to be clear... you have to use log backups to have point >>> in time recovery. You also have to be in full recovery mode. Bulk-logged >>> won't give you point in time. >>> >>> -- >>> Read my book reviews at: >>> www.ITBookworm.com >>> >>> Blog Author of: >>> Database Underground -- http://weblog.infoworld.com/dbunderground/ >>> DBA Rant - http://dbarant.blogspot.com >>> >>> >>> >>> >>> "Smith" wrote: >>> >>>> That means I can't differential backup when I am trying to do point in time >>>> recovery? >>>> >>>> "Sean McCown" wrote in message >>>> >>>> > You are correct. You lost STOPAT with diffs. >>>> > >>>> > Don't forget to mark the issue as resolved if you got your answer. >>>> > thx. >>>> > -- >>>> > Read my book reviews at: >>>> > www.ITBookworm.com >>>> > >>>> > Blog Author of: >>>> > Database Underground -- http://weblog.infoworld.com/dbunderground/ >>>> > DBA Rant - http://dbarant.blogspot.com >>>> > >>>> > >>>> > >>>> > >>>> > "Smith" wrote: >>>> > >>>> >> Thanks.. that means differential backup doesn't help me in this scanario? >>>> >> >>>> >> Thanks >>>> >> >>>> >> "Sean McCown" wrote in message >>>> >> >>>> >> > In order to do a point in time recovery, you'll need to use the STOPAT >>>> >> > command. >>>> >> > Here's an example I pulled out of BOL: >>>> >> > >>>> >> > RESTORE DATABASE AdventureWorks >>>> >> > FROM AdventureWorksBackups >>>> >> > WITH NORECOVERY; >>>> >> > >>>> >> > RESTORE LOG AdventureWorks >>>> >> > FROM AdventureWorksBackups >>>> >> > WITH RECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM'; >>>> >> > >>>> >> > >>>> >> > If you have several logs before the last one that you run with >>>> >> > recovery, >>>> >> > just do them with norecovery like you did the full backup. So if you >>>> >> > had >>>> >> > previous logs to restore before you get to the one you want to stop at >>>> >> > just >>>> >> > say: >>>> >> > >>>> >> > RESTORE LOG AdventureWorks FROM AdventureWorksBackups with NORECOVERY >>>> >> > >>>> >> > You can replace that device name with a physical path if you need to so >>>> >> > you >>>> >> > would also do this if you had a physical path to the file: >>>> >> > >>>> >> > RESTORE LOG AdventureWorks FROM disk='c:\mylog.trn' with NORECOVERY >>>> >> > >>>> >> > It's worth it to mention that you lose the STOPAT functionality if >>>> >> > you're >>>> >> > not in full recovery mode. >>>> >> > >>>> >> > And as Tibor said, you can only recovery to one point in time. So if >>>> >> > you >>>> >> > need 2 separate times, you'll have to do 2 restores and merge them >>>> >> > manually >>>> >> > at the table level. >>>> >> > >>>> >> > >>>> >> > -- >>>> >> > Read my book reviews at: >>>> >> > www.ITBookworm.com >>>> >> > >>>> >> > Blog Author of: >>>> >> > Database Underground -- http://weblog.infoworld.com/dbunderground/ >>>> >> > DBA Rant - http://dbarant.blogspot.com >>>> >> > >>>> >> > >>>> >> > >>>> >> > >>>> >> > "Smith" wrote: >>>> >> > >>>> >> >> Can you tell me the step? how I can achieve this. >>>> >> >> >>>> >> >> Thanks >>>> >> >> "Tibor Karaszi" >>>> >> >> wrote >>>> >> >> in >>>> >> >> message >>>> >> >> > Restore will not let you get an inconsistent views of a database. >>>> >> >> > But >>>> >> >> > that >>>> >> >> > I mean that it won't allow for a restore where some objects are from >>>> >> >> > one >>>> >> >> > point in time and other objects are from another point in time. You >>>> >> >> > can >>>> >> >> > restore up to 10:05 into a new database name and move the desired >>>> >> >> > data >>>> >> >> > (TEST2) from there into the production database. >>>> >> >> > >>>> >> >> > -- >>>> >> >> > Tibor Karaszi, SQL Server MVP >>>> >> >> > http://www.karaszi.com/sqlserver/default.asp >>>> >> >> > http://sqlblog.com/blogs/tibor_karaszi >>>> >> >> > >>>> >> >> > >>>> >> >> > "Smith" wrote in message >>>> >> >> > >>>> >> >> >> Hi Professionals, >>>> >> >> >> >>>> >> >> >> Following are my queries and I want to recover the table (Test2) >>>> >> >> >> with >>>> >> >> >> data and recover all the test1 data. I would appreciate if you can >>>> >> >> >> let >>>> >> >> >> me >>>> >> >> >> know the commands. >>>> >> >> >> >>>> >> >> >> >>>> >> >> >> use eZSale >>>> >> >> >> go >>>> >> >> >> create table TEST1 (ID INT, NAME VARCHAR(50)) >>>> >> >> >> create table TEST2(ID INT, NAME VARCHAR(50)) >>>> >> >> >> >>>> >> >> >> --Time:10:01 >>>> >> >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_FullDb.BAK' >>>> >> >> >> >>>> >> >> >> --Time:10:02 >>>> >> >> >> INSERT INTO TEST1 SELECT 1, 'AFTER FULL BACKUP' >>>> >> >> >> INSERT INTO TEST2 SELECT 1, 'AFTER FULL BACKUP' >>>> >> >> >> >>>> >> >> >> --Time:10:03 >>>> >> >> >> BACKUP LOG eZsale to disk = 'c:\Temp\test_Logbk.BAK' >>>> >> >> >> >>>> >> >> >> --Time: 10:04 >>>> >> >> >> INSERT INTO TEST1 SELECT 2, 'AFTER LOG BACKUP' >>>> >> >> >> INSERT INTO TEST2 SELECT 2, 'AFTER LOG BACKUP' >>>> >> >> >> INSERT INTO TEST2 SELECT 3, 'AFTER LOG BACKUP..SECOND ENTRY' >>>> >> >> >> >>>> >> >> >> --Time: 10:05 >>>> >> >> >> DROP TABLE TEST2 >>>> >> >> >> >>>> >> >> >> --Time: 10:06 >>>> >> >> >> BACKUP DATABASE eZSale to disk = 'C:\TEMP\test_DiffDb.BAK' with >>>> >> >> >> differential >>>> >> >> >> >>>> >> >> >> --Time: 10:07 >>>> >> >> >> INSERT INTO TEST1 SELECT 4, 'AFTER DIFFERNTIAL BACKUP' >>>> >> >> >> INSERT INTO TEST1 SELECT 5, 'AFTER DIFFERNTIAL BACKUP..SECOND >>>> >> >> >> ENTRY' >>>> >> >> > >>>> >> >> >>>> >> >>>> >> > > >> Stay informed about: Point in time recovery

Display posts from previous:

Related Topics:

Disaster recovery - Just thinking about disaster recovery, if I want to move the database into another server then how I get the Master Key, Certificate and Symmetric Keys? if I restore into another box will it work or I have to to do couple of configuration settings? ..

Disaster Recovery Encrypted DB - I have SQLServer 2005 installed that has database in which I implemented Symmetric AES_128 encryption and database works fine, when I took the backup and restore into different box it doesn't work, is there any other thing which I have to do? I took..

Procedure update time - hi How can I Identify when the procedure was updated last time. Farhan Iqbal

date/time fields - Hallo, I know a lot has already been told about date/time fields in a database but still confuses me, specif when dealing with SQLserver(Express). It seems that sqlserver only accepts the date in a "yyyyMMdd" format? (difference between Expre...

Convert Oracle Number to Time - Hi, I have a Oracle table that is a number which i would like to convert into a valid time? for example 8495638 should read like 10:59 37 AM Ive spent all day trying to work this our & would appreciate any assistance. Thanks Todd

	Database Forums (Home) -> Client	All times are: Pacific Time (US & Canada)
Page 1 of 1

You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum