copying database

Twice a month we need to take a copy/snapshot of an existing DB on server 1
and place it on a server 2. Each copy/snapshot can overwrite the
pre-existing database on server 2. Right now we are doing this manually wit
h
backups and restores. We would like to automate this process... what are
some options in doing this?
-jonJon,
DTS - Transfer Databases Task or DTS (or script) detach db, copy file,
attach db.
HTH
Jerry
"Jon" <Jon@.discussions.microsoft.com> wrote in message
news:776B382C-7EB7-4A9F-891E-4F8EE3413FBA@.microsoft.com...
> Twice a month we need to take a copy/snapshot of an existing DB on server
> 1
> and place it on a server 2. Each copy/snapshot can overwrite the
> pre-existing database on server 2. Right now we are doing this manually
> with
> backups and restores. We would like to automate this process... what are
> some options in doing this?
> -jon|||Scripts, scripts, and more scripts.
I do this weekly to refresh our development, qa, and demo environments from
production backups. I have tried other techniques and the backup-restore
one works best. I have scripts to fix logins and reset database properties
as part of the overall refresh process.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jon" <Jon@.discussions.microsoft.com> wrote in message
news:776B382C-7EB7-4A9F-891E-4F8EE3413FBA@.microsoft.com...
> Twice a month we need to take a copy/snapshot of an existing DB on server
> 1
> and place it on a server 2. Each copy/snapshot can overwrite the
> pre-existing database on server 2. Right now we are doing this manually
> with
> backups and restores. We would like to automate this process... what are
> some options in doing this?
> -jon|||> backups and restores. We would like to automate this process... what are
> some options in doing this?
Have you seen the section in BOL - Administering SQL Server / Automating
Administrative Tasks?|||What drawbacks do you see with DTS? Does your script take the backup and
create the copy along with fixing logins and resetting db properties?
Finally, where is a good place to go to regarding how to create these type o
f
scripts? Thanks in advance!
"Geoff N. Hiten" wrote:

> Scripts, scripts, and more scripts.
> I do this weekly to refresh our development, qa, and demo environments fro
m
> production backups. I have tried other techniques and the backup-restore
> one works best. I have scripts to fix logins and reset database properti
es
> as part of the overall refresh process.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Jon" <Jon@.discussions.microsoft.com> wrote in message
> news:776B382C-7EB7-4A9F-891E-4F8EE3413FBA@.microsoft.com...
>
>|||Nothing wrong with DTS. I needed to minimize the impact on the production
system so piggybacking on my normal backup rotation seemed to make sense.
Yes, my scripts use sp_change_users_login to remap users and logins
correctly. They handle replication properties, recovery models, and moving
files to the appropriate target folders. The restore script even kicks all
the users out of the target database so I can have exclusive access to
execute the restore command. They even work (with a few adjustments) if you
have backup compression tools like SQLLiteSpeed or SQLSafe.
I did it by noting all the tasks I did manually through Enterprise Mangler
and then creating a script for each task. If you get stuck you can look in
BOL for specifics on the task or you can search online or even ask again
right here. You can always cheat and use Profiler to determine what
Enterprise Mangler is doing "under the covers".
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jon" <Jon@.discussions.microsoft.com> wrote in message
news:D9577E91-BC9C-4536-B8EB-597544A4F355@.microsoft.com...[vbcol=seagreen]
> What drawbacks do you see with DTS? Does your script take the backup and
> create the copy along with fixing logins and resetting db properties?
> Finally, where is a good place to go to regarding how to create these type
> of
> scripts? Thanks in advance!
> "Geoff N. Hiten" wrote:
>