Backup and Restore with STOPAT

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
Read this article
http://www.karaszi.com/SQLServer/in...veral_times.asp
"George Homorozeanu" <george_homorozeanu@.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.
"George Homorozeanu" <george_homorozeanu@.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.
>