Copying Database To Database

25 Aug 2005 1:17 PM

Bongee

Hello,

I have two databases, One is full of tables, The Other is empty,

What I am trying to do is To Copy Full DB to Empty DB,

Manual way is Backup DB and Restore to Target DB

But, I wanna do it using a script and trying to make this process Automatic.

What this automatic process might do is; Every Day One time ;

   First; Delete Target DB s all Tables
   Second; Backup Source DB
   Third; Restore (Overwrite) DB to Target DB

Ýs it possible in MS SQL Server ?

Thank you.

25 Aug 2005 3:06 AM

Hari Prasad

Hi,

It is possible but it is very time consuming if you have Foreign key
relation ship. So it is recommended to do a
Backup and Restore every day. Steps:-

1. BACKUP source DB (See BACKUP DATABASE command)
2. Set the destination DB to sinlg user (ALTER DATABASE DBNAME SET
SINGLE_USER with ROLLBACK IMMEDIATE)
3. RESTORE the BACKUP (See RESTORE DATABASE command)
4. Set back te database to Multi user (ALTER DATABASE DBNAME SET MULTI_USER)

Thanks
Hari
SQL Server MVP

Show quote

"Bongee" <bong***@bonbon.net> wrote in message
news:ePsYYdXqFHA.1328@tk2msftngp13.phx.gbl...
> Hello,
>
> I have two databases, One is full of tables, The Other is empty,
>
> What I am trying to do is To Copy Full DB to Empty DB,
>
> Manual way is Backup DB and Restore to Target DB
>
> But, I wanna do it using a script and trying to make this process
> Automatic.
>
> What this automatic process might do is; Every Day One time ;
>
>   First; Delete Target DB s all Tables
>   Second; Backup Source DB
>   Third; Restore (Overwrite) DB to Target DB
>
> Ýs it possible in MS SQL Server ?
>
> Thank you.
>
>

25 Aug 2005 1:41 PM

B.M

You don't need to delete any objects in your target DB, the restore will do
Another easy way is Snapshot replication

Show quote

"Bongee" <bong***@bonbon.net> wrote in message
news:ePsYYdXqFHA.1328@tk2msftngp13.phx.gbl...
> Hello,
>
> I have two databases, One is full of tables, The Other is empty,
>
> What I am trying to do is To Copy Full DB to Empty DB,
>
> Manual way is Backup DB and Restore to Target DB
>
> But, I wanna do it using a script and trying to make this process
> Automatic.
>
> What this automatic process might do is; Every Day One time ;
>
>   First; Delete Target DB s all Tables
>   Second; Backup Source DB
>   Third; Restore (Overwrite) DB to Target DB
>
> Ýs it possible in MS SQL Server ?
>
> Thank you.
>
>

25 Aug 2005 1:53 PM

Alejandro Mesa

You do not need first steep. You can use "Backup Database" or "Maintenance
Plan" wizard to setup a schedule backup, and create a schedule job that will
restore the last database backup to another db. This job has to be scheduled
to run after the backup one. See "restore" in BOL, to see learn how to use it.

AMB

Show quote

"Bongee" wrote:

> Hello,
>
> I have two databases, One is full of tables, The Other is empty,
>
> What I am trying to do is To Copy Full DB to Empty DB,
>
> Manual way is Backup DB and Restore to Target DB
>
> But, I wanna do it using a script and trying to make this process Automatic.
>
> What this automatic process might do is; Every Day One time ;
>
>    First; Delete Target DB s all Tables
>    Second; Backup Source DB
>    Third; Restore (Overwrite) DB to Target DB
>
> Ãs it possible in MS SQL Server ?
>
> Thank you.
>
>
>