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!
>
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.
>
>
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?
>
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
No comments:
Post a Comment