|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Backup and Restore with STOPATI want to implement the following simple scenario as a test prior the real implementation to see if these scenario is plausible. I want to test the STOPAT option of the RESTORE LOG command. 1. First I want to full backup the database (the "Recovery model" option is set to "Full") using the following code: BACKUP DATABASE atsTables TO DISK = 'c:\temp\20060318_1236_atsTables.dat' WITH DESCRIPTION = 'atsTables Backup1' 2. After backup I will insert a new row in one table of the db with the following command (clock time: 12:37:xx): INSERT INTO Classes(Title) VALUES('Test1') 3. After at least one minute from the first insert I will insert another row to the db (now the time is: 12:38:xx) INSERT INTO Classes(Title) VALUES('Test2') 4. At 12:39:xx I will backup the Transaction protocol with the following command: BACKUP LOG atsTables TO DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat' 5. Now I will restore the db: RESTORE DATABASE atsTables FROM DISK = 'c:\temp\20060318_1236_atsTables.dat' WITH NORECOVERY , REPLACE 6. I will now restore the transaction protocol and stop at 12:37:59 DECLARE @dtm as DateTime SET @dtm = CONVERT(DateTime, '18.03.2006 12:37:59') RESTORE LOG atsTABLES FROM DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat' WITH Recovery , STOPAT = @dtm 7. I expect to have now in the "Classes" table only the "Test1" class "Title". USE atsTables SELECT * FROM Classes 8. But I get the error that the DB cannot be opened because it is still restoring 9. Ok, so I don't know for sure what's wrong so I use the RESTORE LOG command again as before: DECLARE @dtm as DateTime SET @dtm = CONVERT(DateTime, '18.03.2006 12:37:59') RESTORE LOG atsTABLES FROM DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat' WITH Recovery , STOPAT = @dtm 10. I select again the Classes table: USE atsTables SELECT * FROM Classes 11. Now it works but I get both classes ("Test1" and "Test2") instead of only the "Test1" class title The question is: Why is the STOPAT option of the RESTORE LOG not working as I expected? Is my scenario wrong? Can someone help me further? Any answer can help me a lot. Thanks, George. Hi
Read this article http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp Show quote "George Homorozeanu" <george_homoroze***@hotmail.com> wrote in message news:e68eWErSGHA.196@TK2MSFTNGP10.phx.gbl... > Hi > > > > I want to implement the following simple scenario as a test prior the real > implementation to see if these scenario is plausible. I want to test the > STOPAT option of the RESTORE LOG command. > > > > 1. First I want to full backup the database (the "Recovery model" > option is set to "Full") using the following code: > > > > BACKUP DATABASE atsTables > > TO DISK = 'c:\temp\20060318_1236_atsTables.dat' > > WITH > > DESCRIPTION = 'atsTables Backup1' > > > > 2. After backup I will insert a new row in one table of the db with > the following command (clock time: 12:37:xx): > > > > INSERT INTO Classes(Title) > > VALUES('Test1') > > > > 3. After at least one minute from the first insert I will insert > another row to the db (now the time is: 12:38:xx) > > > > INSERT INTO Classes(Title) > > VALUES('Test2') > > > > 4. At 12:39:xx I will backup the Transaction protocol with the > following command: > > > > BACKUP LOG atsTables > > TO DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat' > > > > 5. Now I will restore the db: > > > > RESTORE DATABASE atsTables > > FROM DISK = 'c:\temp\20060318_1236_atsTables.dat' > > WITH > > NORECOVERY > > , REPLACE > > > > 6. I will now restore the transaction protocol and stop at 12:37:59 > > > > DECLARE @dtm as DateTime > > SET @dtm = CONVERT(DateTime, '18.03.2006 12:37:59') > > > > RESTORE LOG atsTABLES > > FROM DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat' > > WITH > > Recovery > > , STOPAT = @dtm > > > > 7. I expect to have now in the "Classes" table only the "Test1" class > "Title". > > > > USE atsTables > > SELECT * > > FROM Classes > > > > 8. But I get the error that the DB cannot be opened because it is > still restoring > > 9. Ok, so I don't know for sure what's wrong so I use the RESTORE LOG > command again as before: > > > > DECLARE @dtm as DateTime > > SET @dtm = CONVERT(DateTime, '18.03.2006 12:37:59') > > > > RESTORE LOG atsTABLES > > FROM DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat' > > WITH > > Recovery > > , STOPAT = @dtm > > > > 10. I select again the Classes table: > > > > USE atsTables > > SELECT * > > FROM Classes > > > > 11. Now it works but I get both classes ("Test1" and "Test2") instead of > only the "Test1" class title > > > > The question is: > > Why is the STOPAT option of the RESTORE LOG not working as I expected? Is > my scenario wrong? Can someone help me further? > > > > Any answer can help me a lot. > > Thanks, George. > > Hi, George
You should verify that the @dtm variable contains the expected value. I guess that the conversion of the varchar value to a datetime resulted in a different value than expected. You should specify the dates in a language neutral format, for example '20060318 12:37:59' or '2006-03-18T12:37:59'. See the following article for more informations: http://www.karaszi.com/SQLServer/info_datetime.asp Razvan After reading the article everithing is clear now and works.
Thanks for help. George. Show quote "George Homorozeanu" <george_homoroze***@hotmail.com> wrote in message news:e68eWErSGHA.196@TK2MSFTNGP10.phx.gbl... > Hi > > > > I want to implement the following simple scenario as a test prior the real > implementation to see if these scenario is plausible. I want to test the > STOPAT option of the RESTORE LOG command. > > > > 1. First I want to full backup the database (the "Recovery model" > option is set to "Full") using the following code: > > > > BACKUP DATABASE atsTables > > TO DISK = 'c:\temp\20060318_1236_atsTables.dat' > > WITH > > DESCRIPTION = 'atsTables Backup1' > > > > 2. After backup I will insert a new row in one table of the db with > the following command (clock time: 12:37:xx): > > > > INSERT INTO Classes(Title) > > VALUES('Test1') > > > > 3. After at least one minute from the first insert I will insert > another row to the db (now the time is: 12:38:xx) > > > > INSERT INTO Classes(Title) > > VALUES('Test2') > > > > 4. At 12:39:xx I will backup the Transaction protocol with the > following command: > > > > BACKUP LOG atsTables > > TO DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat' > > > > 5. Now I will restore the db: > > > > RESTORE DATABASE atsTables > > FROM DISK = 'c:\temp\20060318_1236_atsTables.dat' > > WITH > > NORECOVERY > > , REPLACE > > > > 6. I will now restore the transaction protocol and stop at 12:37:59 > > > > DECLARE @dtm as DateTime > > SET @dtm = CONVERT(DateTime, '18.03.2006 12:37:59') > > > > RESTORE LOG atsTABLES > > FROM DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat' > > WITH > > Recovery > > , STOPAT = @dtm > > > > 7. I expect to have now in the "Classes" table only the "Test1" class > "Title". > > > > USE atsTables > > SELECT * > > FROM Classes > > > > 8. But I get the error that the DB cannot be opened because it is > still restoring > > 9. Ok, so I don't know for sure what's wrong so I use the RESTORE LOG > command again as before: > > > > DECLARE @dtm as DateTime > > SET @dtm = CONVERT(DateTime, '18.03.2006 12:37:59') > > > > RESTORE LOG atsTABLES > > FROM DISK = 'c:\temp\20060318_12:39_atsTablesLOG.dat' > > WITH > > Recovery > > , STOPAT = @dtm > > > > 10. I select again the Classes table: > > > > USE atsTables > > SELECT * > > FROM Classes > > > > 11. Now it works but I get both classes ("Test1" and "Test2") instead of > only the "Test1" class title > > > > The question is: > > Why is the STOPAT option of the RESTORE LOG not working as I expected? Is > my scenario wrong? Can someone help me further? > > > > Any answer can help me a lot. > > Thanks, George. > >  |
|||||||||||||||||||||||
Other interesting topics