how to copy sql database?

Hi there
I make a database called "TEST" for the template in ms sql 2000 server, and
I want to run VB.net to create another database called "TEST_COPY" in same
sql 2000 server and I want to transfer anything the "TEST" has (like tables
with data, views,stored procedure, trigger, role etc), how can I make a copy
in VB.net?
Thanks
Tony
Tony,
If you want everything (as it seems you do), then the easiest (assuming that
you have the needed rights) is to issue the following commands.
BACKUP DATABASE TEST TO TestBackupFileName
RESTORE DATABASE TEST_COPY FROM TestBackupFileName WITH REPLACE
Russell Fields
"tony" <itdong@.hotmail.com> wrote in message
news:eFs$jYhhEHA.3476@.tk2msftngp13.phx.gbl...
> Hi there
> I make a database called "TEST" for the template in ms sql 2000 server,
and
> I want to run VB.net to create another database called "TEST_COPY" in same
> sql 2000 server and I want to transfer anything the "TEST" has (like
tables
> with data, views,stored procedure, trigger, role etc), how can I make a
copy
> in VB.net?
>
> Thanks
> Tony
>
|||Or you can just issue the update query command:
SELECT * INTO test_copy FROM test;
Regards.
|||I'M SORRY FOR THIS INFO, I THOUGHT YOU'RE REFERRING TO A TABLE.
"Jon Gonzales" wrote:

> Or you can just issue the update query command:
> SELECT * INTO test_copy FROM test;
> Regards.
|||Dear Russell
when I use this you suggested
BACKUP DATABASE TEST TO TestBackupFileName
RESTORE DATABASE TEST_COPY FROM TestBackupFileName WITH REPLACE
I change the first one to
BACKUP DATABASE TEST TO disk='C:\TestBackFileName'
and it is works
and then when I use second one,
RESTORE DATABASE TEST_COPY FROM disk='C:\TestBackFileName' WITH REPLACE
I got Error as follow:
Server: Msg 1834, Level 16, State 1, Line 1
The file 'C:\TEST.mdf' cannot be overwritten. It is being used by database
'TEST'.
Server: Msg 3156, Level 16, State 1, Line 1
File 'Store29_TEST_dat' cannot be restored to 'C:\TEST.mdf'. Use WITH MOVE
to identify a valid location for the file.
Server: Msg 1834, Level 16, State 1, Line 1
The file 'C:\TEST.ldf' cannot be overwritten. It is being used by database
'TEST'.
Server: Msg 3156, Level 16, State 1, Line 1
File 'Store29_TEST_log' cannot be restored to 'C:\TEST.ldf'. Use WITH MOVE
to identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
When I use with move as follow:
RESTORE DATABASE TEST_COPY FROM disk ='C:\TestBackupFileName' with Recovery,
move 'TEST.mdf' to 'C:\TEST_COPY.mdf', move 'TEST.ldf' to 'C:\Test_copy.ldf'
I got other error as follow
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'TEST.mdf' is not part of database 'TEST_COPY'. Use RESTORE
FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
How can I keep doing with the copy like you suggested?
Thanks
Tony
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:eJ$4fSihEHA.1964@.tk2msftngp13.phx.gbl...
> Tony,
> If you want everything (as it seems you do), then the easiest (assuming
that[vbcol=seagreen]
> you have the needed rights) is to issue the following commands.
> BACKUP DATABASE TEST TO TestBackupFileName
> RESTORE DATABASE TEST_COPY FROM TestBackupFileName WITH REPLACE
> Russell Fields
> "tony" <itdong@.hotmail.com> wrote in message
> news:eFs$jYhhEHA.3476@.tk2msftngp13.phx.gbl...
> and
same
> tables
> copy
>
|||tony,
Sorry that my shorthand answer was not specific enough.
You will also need to use the option (from the BOL):
MOVE 'logical_file_name' TO 'operating_system_file_name'
Specifies that the given logical_file_name should be moved to
operating_system_file_name. By default, the logical_file_name is restored to
its original location. If the RESTORE statement is used to copy a database
to the same or different server, the MOVE option may be needed to relocate
the database files and to avoid collisions with existing files. Each logical
file in the database can be specified in different MOVE statements.
This keeps the file from trying to overwrite the active database.
Russell Fields
"tony" <itdong@.hotmail.com> wrote in message
news:%23wQZHgthEHA.2812@.tk2msftngp13.phx.gbl...
> Dear Russell
> when I use this you suggested
> BACKUP DATABASE TEST TO TestBackupFileName
> RESTORE DATABASE TEST_COPY FROM TestBackupFileName WITH REPLACE
> I change the first one to
> BACKUP DATABASE TEST TO disk='C:\TestBackFileName'
> and it is works
> and then when I use second one,
> RESTORE DATABASE TEST_COPY FROM disk='C:\TestBackFileName' WITH REPLACE
> I got Error as follow:
> Server: Msg 1834, Level 16, State 1, Line 1
> The file 'C:\TEST.mdf' cannot be overwritten. It is being used by
database
> 'TEST'.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'Store29_TEST_dat' cannot be restored to 'C:\TEST.mdf'. Use WITH MOVE
> to identify a valid location for the file.
> Server: Msg 1834, Level 16, State 1, Line 1
> The file 'C:\TEST.ldf' cannot be overwritten. It is being used by
database
> 'TEST'.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'Store29_TEST_log' cannot be restored to 'C:\TEST.ldf'. Use WITH MOVE
> to identify a valid location for the file.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> When I use with move as follow:
> RESTORE DATABASE TEST_COPY FROM disk ='C:\TestBackupFileName' with
Recovery,
> move 'TEST.mdf' to 'C:\TEST_COPY.mdf', move 'TEST.ldf' to
'C:\Test_copy.ldf'[vbcol=seagreen]
> I got other error as follow
> Server: Msg 3234, Level 16, State 2, Line 1
> Logical file 'TEST.mdf' is not part of database 'TEST_COPY'. Use RESTORE
> FILELISTONLY to list the logical file names.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
>
> How can I keep doing with the copy like you suggested?
> Thanks
> Tony
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:eJ$4fSihEHA.1964@.tk2msftngp13.phx.gbl...
> that
server,[vbcol=seagreen]
> same
a
>