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.
>

Backup and restore database hourly: how to implement it?

I have two servers, using SQL server 2000.
I was asked for implementing hourly Backup 3 databases in one server
and restore those databases to another server.

Could anyone give me the detailed steps to do that?
Thanks a lot in advance!<danceli@.gmail.comwrote in message
news:1171500668.532022.161870@.l53g2000cwa.googlegr oups.com...

Quote:

Originally Posted by

>I have two servers, using SQL server 2000.
I was asked for implementing hourly Backup 3 databases in one server
and restore those databases to another server.

Google log-shipping.

You really only want to do a ful restore to server B, and then transaction
log backup/restores after that.

Quote:

Originally Posted by

>
Could anyone give me the detailed steps to do that?
Thanks a lot in advance!
>

|||Log shipping is a process that takes transaction logs from a primary
SQL Server and applies them sequentially on a scheduled basis to
another SQL Server.

Does this do same thing for Backup/Restore the database?? Thanks a lot
for any explaination. I do have no idea about that.

Quote:

Originally Posted by

Google log-shipping.
>
You really only want to do a ful restore to server B, and then transaction
logbackup/restores after that.
>
>

|||<danceli@.gmail.comwrote in message
news:1171503793.953050.302190@.q2g2000cwa.googlegro ups.com...

Quote:

Originally Posted by

Log shipping is a process that takes transaction logs from a primary
SQL Server and applies them sequentially on a scheduled basis to
another SQL Server.
>
Does this do same thing for Backup/Restore the database?? Thanks a lot
for any explaination. I do have no idea about that.

Generally you don't want to do a full backup/restore due to the time.

Is there a specific reasony ou want to do a full backup/restore rather than
simply log-shipping?

Quote:

Originally Posted by

>

Quote:

Originally Posted by

>Google log-shipping.
>>
>You really only want to do a ful restore to server B, and then
>transaction
>logbackup/restores after that.
>>
>>

>

|||You mean logshipping could also do backup database and restore the
databases to another server also?

On Feb 14, 7:36 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.comwrote:

Quote:

Originally Posted by

Generally you don't want to do a fullbackup/restore due to the time.
>
Is there a specific reasony ou want to do a fullbackup/restore rather than
simply log-shipping?
>

|||<danceli@.gmail.comwrote in message
news:1171561856.821679.57780@.a75g2000cwd.googlegro ups.com...

Quote:

Originally Posted by

You mean logshipping could also do backup database and restore the
databases to another server also?

Generally you have to do the initial backup/restore yourself.

I've written scripts in the past to do a full backup/restore but that was a
specialized case that I replaced with a log-shipping script the first chance
I got.

I'm still unclear why you want to go the full backup/restore route rather
than using log-shipping.

Quote:

Originally Posted by

>
>
On Feb 14, 7:36 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.comwrote:
>

Quote:

Originally Posted by

>Generally you don't want to do a fullbackup/restore due to the time.
>>
>Is there a specific reasony ou want to do a fullbackup/restore rather
>than
>simply log-shipping?
>>

>

|||Actually we need replicate 3 databases from server A to server B. But
no idea why replication does not work fine.

So, my boss asked me to do hourly full backup/restore to server B,
which is kind of relication.

Now I still can schedule a job for hourly full backup on server A, but
i have no idea how to schedule a job for hourly full restore dbs to
server B.

How to make those hourly .Bak files in server A to server B, and then
do restore in server B??

BTW, thank you so much to keep helping me out!!

On Feb 15, 10:48 am, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.comwrote:

Quote:

Originally Posted by

>
Generally you have to do the initialbackup/restore yourself.
>
I've written scripts in the past to do a fullbackup/restore but that was a
specialized case that I replaced with a log-shipping script the first chance
I got.
>
I'm still unclear why you want to go the fullbackup/restore route rather
than using log-shipping.

|||
--
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com

<danceli@.gmail.comwrote in message
news:1171570480.195547.91540@.v33g2000cwv.googlegro ups.com...

Quote:

Originally Posted by

Actually we need replicate 3 databases from server A to server B. But
no idea why replication does not work fine.
>

I'd focus on fixing that.

Quote:

Originally Posted by

So, my boss asked me to do hourly full backup/restore to server B,
which is kind of relication.

Questions to ask then:
1) What do you do on Server B while the restore is taking place?
2) What do you do if the backup/restore cycle takes more than an hour?

With log-shipping at least you can put the databases on server B into
"read-only" mode between log restores.

Quote:

Originally Posted by

>
Now I still can schedule a job for hourly full backup on server A, but
i have no idea how to schedule a job for hourly full restore dbs to
server B.
>
How to make those hourly .Bak files in server A to server B, and then
do restore in server B??

What I basically did was create a job on Server A that performed the backup
and then called a job on ServerB to start the restore. (ServerB would look
to the MSDB on ServerA to find the most recentlly performed backup and
location and restore that.)

Quote:

Originally Posted by

>
BTW, thank you so much to keep helping me out!!
>
>
On Feb 15, 10:48 am, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.comwrote:

Quote:

Originally Posted by

>>
>Generally you have to do the initialbackup/restore yourself.
>>
>I've written scripts in the past to do a fullbackup/restore but that was
>a
>specialized case that I replaced with a log-shipping script the first
>chance
>I got.
>>
>I'm still unclear why you want to go the fullbackup/restore route rather
>than using log-shipping.

>
>
>

|||(danceli@.gmail.com) writes:

Quote:

Originally Posted by

Now I still can schedule a job for hourly full backup on server A, but
i have no idea how to schedule a job for hourly full restore dbs to
server B.

ALTER DATBASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATAHASE dh FROM DISK='locationofbackup'
ALTER DATABASE db SET MULTI_USER

Then use Enterprise Manager or Mgmt Studio to create a job, with this
as the job step and set up a schedule.

Of course, log shipping would be better, but if the databases are small,
less then 1 GB, that's probably not worth the hassle.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The backup file is located in server A.
how should do restore in Server B to catching backup file from serer
A??

Need I copy the backup.bak file from server A to Server B, then
schedule job to restore at server B?
How to set it automatically?

On Feb 15, 2:47 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Now I still can schedule a job for hourly full backup on server A, but
i have no idea how to schedule a job for hourly full restore dbs to
server B.

>
ALTER DATBASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATAHASE dh FROM DISK='locationofbackup'
ALTER DATABASE db SET MULTI_USER
>
Then use Enterprise Manager or Mgmt Studio to create a job, with this
as the job step and set up a schedule.

|||<danceli@.gmail.comwrote in message
news:1171584368.655858.325050@.p10g2000cwp.googlegr oups.com...

Quote:

Originally Posted by

The backup file is located in server A.
how should do restore in Server B to catching backup file from serer
A??
>

Use UNCs to refer to the location of the backup.

I'm assuming you're NOT backing up the file to the same local disk on
ServerA.

Quote:

Originally Posted by

Need I copy the backup.bak file from server A to Server B, then
schedule job to restore at server B?
How to set it automatically?
>
>
On Feb 15, 2:47 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
>

Quote:

Originally Posted by

Quote:

Originally Posted by

Now I still can schedule a job for hourly full backup on server A, but
i have no idea how to schedule a job for hourly full restore dbs to
server B.

>>
>ALTER DATBASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
>RESTORE DATAHASE dh FROM DISK='locationofbackup'
>ALTER DATABASE db SET MULTI_USER
>>
>Then use Enterprise Manager or Mgmt Studio to create a job, with this
>as the job step and set up a schedule.

>

|||Greg D. Moore (Strider) wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>The backup file is located in server A.
>how should do restore in Server B to catching backup file from serer
>A??
>>

>
Use UNCs to refer to the location of the backup.
>

If the servers' drives are not accessible via UNC, FTP can be scripted
and works pretty well. You can create a batch file to compress the
backup and ftp it to the remote server. Your remote server can then
'look' for new backups at a certain interval, uncompress it and restore
it. I believe you could do all of this in batch files if desired or in
SQL jobs. Use whatever you are most familiar with.|||Greg D. Moore (Strider) wrote:

Quote:

Originally Posted by

I'm still unclear why you want to go the full backup/restore route rather
than using log-shipping.

Greg,

Log shipping requires Enterprise Edition, right?

Jonathan|||
--
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com

"Jonathan Roberts" <gremln007@.diynics.comwrote in message
news:YabBh.12926$JF.1221@.newsfe21.lga...

Quote:

Originally Posted by

Greg D. Moore (Strider) wrote:

Quote:

Originally Posted by

>I'm still unclear why you want to go the full backup/restore route rather
>than using log-shipping.

>
Greg,
>
Log shipping requires Enterprise Edition, right?

If you want to use the built-in scripts yes.

But there's nothing to prevent you from rolling your own. Which I've done
in the past.

Quote:

Originally Posted by

>
Jonathan