Automation of database restoration

We have sql server standard edition , I want to automate the task of data
restoration on a daily basis .
How can I automate copying and restoration of the data on standalone PC .
Kindly suggest me the best option for the same .
Regards,
SwatiI assume that you have two database servers and you want to keep them in
synch.
You have to do two things
BACKUP your database(s) on your primary server
RESTORE your backups to your standby server.
It is simple to set up a job which issues BACKUP commands. In fact you
should be doing that now as part of your backup routine.
The RESTORE is easy. Assuming that the account that SQL Server is running
as on your second server has rights to the machine where your primary
backups are stored you can do something like this: RESTORE DATABASE foo FROM
DISK = '\\primaryserver\x$\foo.bak' WITH REPLACE
If you have a share on the server you can access the share:
RESTORE DATABASE foo FROM DISK = '\\primaryserver\sharename\foo.bak' WITH
REPLACE
If your database servers have a different disk layout you might have to use
the WITH MOVE option to move the physical files to a location that works on
your secondary machine.
Once you are able to issue the appropriate BACKUP and RESTORE commands the
next thing you have to do is create a couple of jobs which will automate the
process for you. You can start a job (on another server) from your main
server, but the easiest method might be to have the jobs be independent of
each other. Schedule your backup job to run at, say 10pm. Lets say that it
takes 30 minutes. You could schedule the restore job on the other server to
start at 10:45pm.
You may find these articles helpful:
314515 INF: Frequently Asked Questions - SQL Server 2000 - Log Shipping
http://support.microsoft.com/?id=314515
323135 INF: Microsoft SQL Server 2000 - How to Set Up Log Shipping (White
Paper)
http://support.microsoft.com/?id=323135
325220 Support WebCast: Microsoft SQL Server 2000 Log Shipping
http://support.microsoft.com/?id=325220
821786 Support WebCast: Microsoft SQL Server 2000: Using Log Shipping
http://support.microsoft.com/?id=821786
321247 HOW TO: Configure Security for Log Shipping
http://support.microsoft.com/?id=321247
329133 INF: Troubleshooting SQL Server 2000 Log Shipping "Out of Sync"
Errors
http://support.microsoft.com/?id=329133
Keith
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:OefdTe1uEHA.3828@.TK2MSFTNGP12.phx.gbl...
> We have sql server standard edition , I want to automate the task of data
> restoration on a daily basis .
> How can I automate copying and restoration of the data on standalone PC .
> Kindly suggest me the best option for the same .
>
> Regards,
> Swati
>
>|||Keith,
Thanks for response.
I agree that we can write manual commands for restoring the database on
standby server . Let me explain you our backup stategy
Full backup : 12:00 AM
Diff backup : evry 2 hours
Txn log backup : every 15 minute .this is done using DB maintenance plan .
so every time we get a new file. e.g mydb_db_200410260000.bak
Now I want to restore my full backup and diff backup which is done at
interval of 2 hours . How can I restore my full db backup and diff backup.
Regards,
Swati
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OUnaol1uEHA.2684@.TK2MSFTNGP12.phx.gbl...
> I assume that you have two database servers and you want to keep them in
> synch.
> You have to do two things
> BACKUP your database(s) on your primary server
> RESTORE your backups to your standby server.
> It is simple to set up a job which issues BACKUP commands. In fact you
> should be doing that now as part of your backup routine.
> The RESTORE is easy. Assuming that the account that SQL Server is running
> as on your second server has rights to the machine where your primary
> backups are stored you can do something like this: RESTORE DATABASE foo
FROM
> DISK = '\\primaryserver\x$\foo.bak' WITH REPLACE
> If you have a share on the server you can access the share:
> RESTORE DATABASE foo FROM DISK = '\\primaryserver\sharename\foo.bak' WITH
> REPLACE
> If your database servers have a different disk layout you might have to
use
> the WITH MOVE option to move the physical files to a location that works
on
> your secondary machine.
> Once you are able to issue the appropriate BACKUP and RESTORE commands the
> next thing you have to do is create a couple of jobs which will automate
the
> process for you. You can start a job (on another server) from your main
> server, but the easiest method might be to have the jobs be independent of
> each other. Schedule your backup job to run at, say 10pm. Lets say that
it
> takes 30 minutes. You could schedule the restore job on the other server
to
> start at 10:45pm.
> You may find these articles helpful:
>
> 314515 INF: Frequently Asked Questions - SQL Server 2000 - Log Shipping
> http://support.microsoft.com/?id=314515
> 323135 INF: Microsoft SQL Server 2000 - How to Set Up Log Shipping (White
> Paper)
> http://support.microsoft.com/?id=323135
> 325220 Support WebCast: Microsoft SQL Server 2000 Log Shipping
> http://support.microsoft.com/?id=325220
> 821786 Support WebCast: Microsoft SQL Server 2000: Using Log Shipping
> http://support.microsoft.com/?id=821786
> 321247 HOW TO: Configure Security for Log Shipping
> http://support.microsoft.com/?id=321247
> 329133 INF: Troubleshooting SQL Server 2000 Log Shipping "Out of Sync"
> Errors
> http://support.microsoft.com/?id=329133
>
> --
> Keith
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:OefdTe1uEHA.3828@.TK2MSFTNGP12.phx.gbl...
data[vbcol=seagreen]
.[vbcol=seagreen]
>|||swati
Create a job that does RESTORE full and DIFF database
For more details please refer to the BOL
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:upWyC21uEHA.3276@.TK2MSFTNGP15.phx.gbl...
> Keith,
> Thanks for response.
> I agree that we can write manual commands for restoring the database on
> standby server . Let me explain you our backup stategy
> Full backup : 12:00 AM
> Diff backup : evry 2 hours
> Txn log backup : every 15 minute .this is done using DB maintenance plan .
> so every time we get a new file. e.g mydb_db_200410260000.bak
> Now I want to restore my full backup and diff backup which is done at
> interval of 2 hours . How can I restore my full db backup and diff backup.
> Regards,
> Swati
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:OUnaol1uEHA.2684@.TK2MSFTNGP12.phx.gbl...
running[vbcol=seagreen]
> FROM
WITH[vbcol=seagreen]
> use
> on
the[vbcol=seagreen]
> the
of[vbcol=seagreen]
that[vbcol=seagreen]
> it
server[vbcol=seagreen]
> to
(White[vbcol=seagreen]
> data
PC[vbcol=seagreen]
> .
>|||If you want to use the second server for Disaster Recovery, you should also
write a script to export your logins from the source SQL Server to the
Destination SQL Server.
Sasan Saidi, MSc in CS
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Uri Dimant" wrote:

> swati
> Create a job that does RESTORE full and DIFF database
> For more details please refer to the BOL
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:upWyC21uEHA.3276@.TK2MSFTNGP15.phx.gbl...
> running
> WITH
> the
> of
> that
> server
> (White
> PC
>
>