 | |
#41
 
| |||
| |||
|
Re: Point in time recovery -
01-25-2008
, 10:17 AM
|
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" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:5ACAC0ED-970C-4BAD-BAD4-AFB7C675A81F (AT) microsoft (DOT) com... 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" <naissani (AT) hotmail (DOT) co> wrote in message news:3C7DF62C-9595-4750-A6B4-6D7F695B4D85 (AT) microsoft (DOT) com... 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' |
#42
| |||
| |||
|
|
Thanks.. that means differential backup doesn't help me in this scanario? Thanks "Sean McCown" <SeanMcCown (AT) discussions (DOT) microsoft.com> wrote in message news:CB701821-3B57-410A-B2BF-901A464CD455 (AT) microsoft (DOT) com... 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" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:5ACAC0ED-970C-4BAD-BAD4-AFB7C675A81F (AT) microsoft (DOT) com... 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" <naissani (AT) hotmail (DOT) co> wrote in message news:3C7DF62C-9595-4750-A6B4-6D7F695B4D85 (AT) microsoft (DOT) com... 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' |
#43
| |||
| |||
|
|
Thanks.. that means differential backup doesn't help me in this scanario? Thanks "Sean McCown" <SeanMcCown (AT) discussions (DOT) microsoft.com> wrote in message news:CB701821-3B57-410A-B2BF-901A464CD455 (AT) microsoft (DOT) com... 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" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:5ACAC0ED-970C-4BAD-BAD4-AFB7C675A81F (AT) microsoft (DOT) com... 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" <naissani (AT) hotmail (DOT) co> wrote in message news:3C7DF62C-9595-4750-A6B4-6D7F695B4D85 (AT) microsoft (DOT) com... 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' |
#44
| |||
| |||
|
|
Thanks.. that means differential backup doesn't help me in this scanario? Thanks "Sean McCown" <SeanMcCown (AT) discussions (DOT) microsoft.com> wrote in message news:CB701821-3B57-410A-B2BF-901A464CD455 (AT) microsoft (DOT) com... 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" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:5ACAC0ED-970C-4BAD-BAD4-AFB7C675A81F (AT) microsoft (DOT) com... 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" <naissani (AT) hotmail (DOT) co> wrote in message news:3C7DF62C-9595-4750-A6B4-6D7F695B4D85 (AT) microsoft (DOT) com... 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' |
#45
| |||
| |||
|
|
Thanks.. that means differential backup doesn't help me in this scanario? Thanks "Sean McCown" <SeanMcCown (AT) discussions (DOT) microsoft.com> wrote in message news:CB701821-3B57-410A-B2BF-901A464CD455 (AT) microsoft (DOT) com... 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" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:5ACAC0ED-970C-4BAD-BAD4-AFB7C675A81F (AT) microsoft (DOT) com... 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" <naissani (AT) hotmail (DOT) co> wrote in message news:3C7DF62C-9595-4750-A6B4-6D7F695B4D85 (AT) microsoft (DOT) com... 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' |
#46
| |||
| |||
|
|
Thanks.. that means differential backup doesn't help me in this scanario? Thanks "Sean McCown" <SeanMcCown (AT) discussions (DOT) microsoft.com> wrote in message news:CB701821-3B57-410A-B2BF-901A464CD455 (AT) microsoft (DOT) com... 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" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:5ACAC0ED-970C-4BAD-BAD4-AFB7C675A81F (AT) microsoft (DOT) com... 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" <naissani (AT) hotmail (DOT) co> wrote in message news:3C7DF62C-9595-4750-A6B4-6D7F695B4D85 (AT) microsoft (DOT) com... 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' |
#47
| |||
| |||
|
|
Thanks.. that means differential backup doesn't help me in this scanario? Thanks "Sean McCown" <SeanMcCown (AT) discussions (DOT) microsoft.com> wrote in message news:CB701821-3B57-410A-B2BF-901A464CD455 (AT) microsoft (DOT) com... 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" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:5ACAC0ED-970C-4BAD-BAD4-AFB7C675A81F (AT) microsoft (DOT) com... 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" <naissani (AT) hotmail (DOT) co> wrote in message news:3C7DF62C-9595-4750-A6B4-6D7F695B4D85 (AT) microsoft (DOT) com... 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' |
#48
| |||
| |||
|
|
Thanks.. that means differential backup doesn't help me in this scanario? Thanks "Sean McCown" <SeanMcCown (AT) discussions (DOT) microsoft.com> wrote in message news:CB701821-3B57-410A-B2BF-901A464CD455 (AT) microsoft (DOT) com... 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" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:5ACAC0ED-970C-4BAD-BAD4-AFB7C675A81F (AT) microsoft (DOT) com... 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" <naissani (AT) hotmail (DOT) co> wrote in message news:3C7DF62C-9595-4750-A6B4-6D7F695B4D85 (AT) microsoft (DOT) com... 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' |
#49
| |||
| |||
|
|
Thanks.. that means differential backup doesn't help me in this scanario? Thanks "Sean McCown" <SeanMcCown (AT) discussions (DOT) microsoft.com> wrote in message news:CB701821-3B57-410A-B2BF-901A464CD455 (AT) microsoft (DOT) com... 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" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:5ACAC0ED-970C-4BAD-BAD4-AFB7C675A81F (AT) microsoft (DOT) com... 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" <naissani (AT) hotmail (DOT) co> wrote in message news:3C7DF62C-9595-4750-A6B4-6D7F695B4D85 (AT) microsoft (DOT) com... 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' |
#50
| |||
| |||
|
|
Thanks.. that means differential backup doesn't help me in this scanario? Thanks "Sean McCown" <SeanMcCown (AT) discussions (DOT) microsoft.com> wrote in message news:CB701821-3B57-410A-B2BF-901A464CD455 (AT) microsoft (DOT) com... 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" <tibor_please.no.email_karaszi (AT) hotmail (DOT) nomail.com> wrote in message news:5ACAC0ED-970C-4BAD-BAD4-AFB7C675A81F (AT) microsoft (DOT) com... 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" <naissani (AT) hotmail (DOT) co> wrote in message news:3C7DF62C-9595-4750-A6B4-6D7F695B4D85 (AT) microsoft (DOT) com... 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' |
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.