Copy Diagrams from One SQL Server to another

Hi,
How can you copy diagrams from one SQL Server to another.
Thanks.
B.
http://support.microsoft.com/default...;en-us;Q320125
HTH, Jens Suessmeyer.
|||Hi,
You can also just copy and paste the diagrams. Open a new diagram in the
destination server. Open the diagram on the source server. Click the
background of the source diagram and the type Ctrl-A to select all objects
and text in the diagram and then type Ctrl-C to copy the objects to the
clipboard. Then click on the blank, new diagram and type Ctrl-V to paste
the objects. Then save the new diagram. This assumes that the database in
the destination server has the same tables as the database in the source
server.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:uhGb2ImyFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> How can you copy diagrams from one SQL Server to another.
> Thanks.
> B.
>

Copy Diagrams from One SQL Server to another

Hi,
How can you copy diagrams from one SQL Server to another.
Thanks.
B.
http://support.microsoft.com/default...;en-us;Q320125
HTH, Jens Suessmeyer.
|||Hi,
You can also just copy and paste the diagrams. Open a new diagram in the
destination server. Open the diagram on the source server. Click the
background of the source diagram and the type Ctrl-A to select all objects
and text in the diagram and then type Ctrl-C to copy the objects to the
clipboard. Then click on the blank, new diagram and type Ctrl-V to paste
the objects. Then save the new diagram. This assumes that the database in
the destination server has the same tables as the database in the source
server.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:uhGb2ImyFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> How can you copy diagrams from one SQL Server to another.
> Thanks.
> B.
>

Copy Diagrams from One SQL Server to another

Hi,
How can you copy diagrams from one SQL Server to another.
Thanks.
B.http://support.microsoft.com/default.aspx?scid=kb;en-us;Q320125
HTH, Jens Suessmeyer.|||Hi,
You can also just copy and paste the diagrams. Open a new diagram in the
destination server. Open the diagram on the source server. Click the
background of the source diagram and the type Ctrl-A to select all objects
and text in the diagram and then type Ctrl-C to copy the objects to the
clipboard. Then click on the blank, new diagram and type Ctrl-V to paste
the objects. Then save the new diagram. This assumes that the database in
the destination server has the same tables as the database in the source
server.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:uhGb2ImyFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> How can you copy diagrams from one SQL Server to another.
> Thanks.
> B.
>

Copy Diagrams from One SQL Server to another

Hi,
How can you copy diagrams from one SQL Server to another.
Thanks.
B.http://support.microsoft.com/defaul...b;en-us;Q320125
HTH, Jens Suessmeyer.|||Hi,
You can also just copy and paste the diagrams. Open a new diagram in the
destination server. Open the diagram on the source server. Click the
background of the source diagram and the type Ctrl-A to select all objects
and text in the diagram and then type Ctrl-C to copy the objects to the
clipboard. Then click on the blank, new diagram and type Ctrl-V to paste
the objects. Then save the new diagram. This assumes that the database in
the destination server has the same tables as the database in the source
server.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:uhGb2ImyFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> How can you copy diagrams from one SQL Server to another.
> Thanks.
> B.
>

Copy database - same server

Hi;
I want to copy a database on my server, creating another database (different
name) on the same server copying everything - tables, data, diagrams, stored
procedures - everything. (I am making a test DB that I can use for my unit
tests.)
What's the easiest way to do this? I tried backup/restore but it wants the
database name to stay the same.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Backup and restore work just fine, I do this all the time. Just specify the desired database name in
the RESTORE command and use the MOVE option to specify the names of the database files to be created
for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:BF2CE48C-EB00-4BCC-B9E5-71D9491445C3@.microsoft.com...
> Hi;
> I want to copy a database on my server, creating another database (different
> name) on the same server copying everything - tables, data, diagrams, stored
> procedures - everything. (I am making a test DB that I can use for my unit
> tests.)
> What's the easiest way to do this? I tried backup/restore but it wants the
> database name to stay the same.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
>
|||Try the following:
Assume you have to copy pubs to pubs_2
'create database pubs_2' for the first time.
then,
create a job with following steps:
backup database pubs to disk = 'c:\pubs.bak'
go
restore database pubs_2 from disk ='c:\pubs.bak' with replace,
move 'pubs' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\pubs_2.mdf',
move 'pubs_log' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\pubs_2_log.ldf'
Thanks
Veera
Tibor Karaszi wrote:
[vbcol=seagreen]
> Backup and restore work just fine, I do this all the time. Just specify the desired database name in
> the RESTORE command and use the MOVE option to specify the names of the database files to be created
> for you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "David Thielen" <thielen@.nospam.nospam> wrote in message
> news:BF2CE48C-EB00-4BCC-B9E5-71D9491445C3@.microsoft.com...
|||Hi;
Tried that (thank you) but got this error message:
Processed 120 pages for database 'WindwardPortal', file
'WindwardPortal_Data' on file 5.
Processed 1 pages for database 'WindwardPortal', file 'WindwardPortal_Log'
on file 5.
BACKUP DATABASE successfully processed 121 pages in 0.283 seconds (3.477
MB/sec).
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'WindwardPortal' is not part of database 'PortalTest'. Use
RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
"Veera" wrote:

> Try the following:
> Assume you have to copy pubs to pubs_2
> 'create database pubs_2' for the first time.
> then,
> create a job with following steps:
> backup database pubs to disk = 'c:\pubs.bak'
> go
> restore database pubs_2 from disk ='c:\pubs.bak' with replace,
> move 'pubs' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2.mdf',
> move 'pubs_log' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2_log.ldf'
>
> Thanks
> Veera
> Tibor Karaszi wrote:
>
>
|||ps - script is:
backup database WindwardPortal to disk = 'c:\temp\WindwardPortal.bak'
go
restore database PortalTest from disk ='c:\temp\WindwardPortal.bak' with
replace,
move 'WindwardPortal' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\PortalTest.mdf',
move 'WindwardPortal_log' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\PortalTest_log.ldf'
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
"Veera" wrote:

> Try the following:
> Assume you have to copy pubs to pubs_2
> 'create database pubs_2' for the first time.
> then,
> create a job with following steps:
> backup database pubs to disk = 'c:\pubs.bak'
> go
> restore database pubs_2 from disk ='c:\pubs.bak' with replace,
> move 'pubs' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2.mdf',
> move 'pubs_log' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2_log.ldf'
>
> Thanks
> Veera
> Tibor Karaszi wrote:
>
>
|||> Processed 120 pages for database 'WindwardPortal', file
> 'WindwardPortal_Data' on file 5.
> Processed 1 pages for database 'WindwardPortal', file 'WindwardPortal_Log'
> on file 5.
> BACKUP DATABASE successfully processed 121 pages in 0.283 seconds (3.477
> MB/sec).
> Server: Msg 3234, Level 16, State 2, Line 1
> Logical file 'WindwardPortal' is not part of database 'PortalTest'. Use
> RESTORE FILELISTONLY to list the logical file names.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
The error message included a suggestion. DId you try it? It will show you
the logical names that you need to move. Apparently, you are not using the
correct logical file name for the data file. You tried to move
'WindwardPortal' when it should have been 'WindwardPortal_Data'
|||> 'create database pubs_2' for the first time.
Above is only a waste of time. And that creation will probably not result in the newly created
database having the same file layout as the one to restore, so the restore need to be performed
using REPLACE which means that SQL Server will delete the database and then create it when executing
the restore command. If the database doesn't exist, don't create it before the restore. the restore
process will do this for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Veera" <veerabahu.c@.gmail.com> wrote in message
news:1142873234.396195.127690@.e56g2000cwe.googlegr oups.com...
> Try the following:
> Assume you have to copy pubs to pubs_2
> 'create database pubs_2' for the first time.
> then,
> create a job with following steps:
> backup database pubs to disk = 'c:\pubs.bak'
> go
> restore database pubs_2 from disk ='c:\pubs.bak' with replace,
> move 'pubs' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2.mdf',
> move 'pubs_log' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2_log.ldf'
>
> Thanks
> Veera
> Tibor Karaszi wrote:
>
|||Hi,
Scott pointed out correctly :
the command should be, (as your logical file name is different),
......
.......
move 'WindwardPortal_data' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\PortalTest.mdf'
......
......
Thanks Tibor for your valuable information, Many thanks.

Copy database - same server

Hi;
I want to copy a database on my server, creating another database (different
name) on the same server copying everything - tables, data, diagrams, stored
procedures - everything. (I am making a test DB that I can use for my unit
tests.)
What's the easiest way to do this? I tried backup/restore but it wants the
database name to stay the same.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.comBackup and restore work just fine, I do this all the time. Just specify the
desired database name in
the RESTORE command and use the MOVE option to specify the names of the data
base files to be created
for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:BF2CE48C-EB00-4BCC-B9E5-71D9491445C3@.microsoft.com...
> Hi;
> I want to copy a database on my server, creating another database (differe
nt
> name) on the same server copying everything - tables, data, diagrams, stor
ed
> procedures - everything. (I am making a test DB that I can use for my unit
> tests.)
> What's the easiest way to do this? I tried backup/restore but it wants the
> database name to stay the same.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
>|||Try the following:
Assume you have to copy pubs to pubs_2
'create database pubs_2' for the first time.
then,
create a job with following steps:
backup database pubs to disk = 'c:\pubs.bak'
go
restore database pubs_2 from disk ='c:\pubs.bak' with replace,
move 'pubs' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\pubs_2.mdf',
move 'pubs_log' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\pubs_2_log.ldf'
Thanks
Veera
Tibor Karaszi wrote:
[vbcol=seagreen]
> Backup and restore work just fine, I do this all the time. Just specify th
e desired database name in
> the RESTORE command and use the MOVE option to specify the names of the da
tabase files to be created
> for you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "David Thielen" <thielen@.nospam.nospam> wrote in message
> news:BF2CE48C-EB00-4BCC-B9E5-71D9491445C3@.microsoft.com...|||Hi;
Tried that (thank you) but got this error message:
Processed 120 pages for database 'WindwardPortal', file
'WindwardPortal_Data' on file 5.
Processed 1 pages for database 'WindwardPortal', file 'WindwardPortal_Log'
on file 5.
BACKUP DATABASE successfully processed 121 pages in 0.283 seconds (3.477
MB/sec).
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'WindwardPortal' is not part of database 'PortalTest'. Use
RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
"Veera" wrote:

> Try the following:
> Assume you have to copy pubs to pubs_2
> 'create database pubs_2' for the first time.
> then,
> create a job with following steps:
> backup database pubs to disk = 'c:\pubs.bak'
> go
> restore database pubs_2 from disk ='c:\pubs.bak' with replace,
> move 'pubs' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2.mdf',
> move 'pubs_log' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2_log.ldf'
>
> Thanks
> Veera
> Tibor Karaszi wrote:
>
>|||ps - script is:
backup database WindwardPortal to disk = 'c:\temp\WindwardPortal.bak'
go
restore database PortalTest from disk ='c:\temp\WindwardPortal.bak' with
replace,
move 'WindwardPortal' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\PortalTest.mdf',
move 'WindwardPortal_log' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\PortalTest_log.ldf'
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
"Veera" wrote:

> Try the following:
> Assume you have to copy pubs to pubs_2
> 'create database pubs_2' for the first time.
> then,
> create a job with following steps:
> backup database pubs to disk = 'c:\pubs.bak'
> go
> restore database pubs_2 from disk ='c:\pubs.bak' with replace,
> move 'pubs' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2.mdf',
> move 'pubs_log' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2_log.ldf'
>
> Thanks
> Veera
> Tibor Karaszi wrote:
>
>|||> Processed 120 pages for database 'WindwardPortal', file
> 'WindwardPortal_Data' on file 5.
> Processed 1 pages for database 'WindwardPortal', file 'WindwardPortal_Log'
> on file 5.
> BACKUP DATABASE successfully processed 121 pages in 0.283 seconds (3.477
> MB/sec).
> Server: Msg 3234, Level 16, State 2, Line 1
> Logical file 'WindwardPortal' is not part of database 'PortalTest'. Use
> RESTORE FILELISTONLY to list the logical file names.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
The error message included a suggestion. DId you try it? It will show you
the logical names that you need to move. Apparently, you are not using the
correct logical file name for the data file. You tried to move
'WindwardPortal' when it should have been 'WindwardPortal_Data'|||> 'create database pubs_2' for the first time.
Above is only a waste of time. And that creation will probably not result in
the newly created
database having the same file layout as the one to restore, so the restore n
eed to be performed
using REPLACE which means that SQL Server will delete the database and then
create it when executing
the restore command. If the database doesn't exist, don't create it before t
he restore. the restore
process will do this for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Veera" <veerabahu.c@.gmail.com> wrote in message
news:1142873234.396195.127690@.e56g2000cwe.googlegroups.com...
> Try the following:
> Assume you have to copy pubs to pubs_2
> 'create database pubs_2' for the first time.
> then,
> create a job with following steps:
> backup database pubs to disk = 'c:\pubs.bak'
> go
> restore database pubs_2 from disk ='c:\pubs.bak' with replace,
> move 'pubs' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2.mdf',
> move 'pubs_log' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2_log.ldf'
>
> Thanks
> Veera
> Tibor Karaszi wrote:
>
>|||Hi,
Scott pointed out correctly :
the command should be, (as your logical file name is different),
......
......
move 'WindwardPortal_data' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\PortalTest.mdf'
.....
.....
--
Thanks Tibor for your valuable information, Many thanks.

Coping Databases

Hi,
My questions is we want to make a copy of a database onto the same
server while preserving the diagrams, stored procs, etc.
We stopped the SQL service and made a copy of the data and log files.
We attempted to ATTACH the file copies (after re-naming them), but the
embedded file information tells SQL that the database already exists.
Our database is a piece of junk, but we must use it. If we don't want
to use the IMPORT/EXPORT Wizard to Copy Objects (because we get some
errors during the transfer), how can we make an exact copy of the
Database onto the same server while giving the "new" database a
different name?
Thanks
:DHRUVFirstly create a new database
Then backup old database
Then restore the old database backup file on newly created database (check f
orce restore over existing database)|||There's no need to create the database fist, it will be created during the
restore. Just do a backup and restore, when the db name is altered in EM's
restore dialog, EM will also specify new filenames for the database files.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"mahruti" <anonymous@.discussions.microsoft.com> wrote in message
news:88953F00-4B6F-480F-A3A7-2A9FEB93EA8B@.microsoft.com...
> Firstly create a new database
> Then backup old database
> Then restore the old database backup file on newly created database (check
force restore over existing database)
>|||Correct, I recommend him the long method beacuse I think maybe he can restor
e to an existing database by accident|||"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0402110919.4a03a3f1@.posting.google.com...
> Hi,
> My questions is we want to make a copy of a database onto the same
> server while preserving the diagrams, stored procs, etc.
> We stopped the SQL service and made a copy of the data and log files.
> We attempted to ATTACH the file copies (after re-naming them), but the
> embedded file information tells SQL that the database already exists.
> Our database is a piece of junk, but we must use it. If we don't want
> to use the IMPORT/EXPORT Wizard to Copy Objects (because we get some
> errors during the transfer), how can we make an exact copy of the
> Database onto the same server while giving the "new" database a
> different name?
> Thanks
> :DHRUV
Probably the quickest and easiest way is to back up the source database,
then restore it with a different name. You can do this from Enterprise
Manager, or using RESTORE. See "Copying Databases" in Books Online.
Simon|||[posted and mailed, please reply in news]
Dhruv (dmalhotr2001@.yahoo.com) writes:
> My questions is we want to make a copy of a database onto the same
> server while preserving the diagrams, stored procs, etc.
> We stopped the SQL service and made a copy of the data and log files.
> We attempted to ATTACH the file copies (after re-naming them), but the
> embedded file information tells SQL that the database already exists.
>
I use sp_attach_db rarely, but I fail to see see why it would work.
Then again, I've been wrong before.
Anyway, the way I copy databases is BACKUP/RESTORE. The BACKUP command
is a breeze, the RESTORE command was too in SQL 6.5, but these days it's
a bit complex.
First use sp_helpdb to see what the logical names of your data files are;
that's the first column. If your database is named yourdb, then the logical
names are typilcally yourdb and yourdb_log.
Then the backup:
BACKUP DATABASE yourdb TO DISK = 'C:\BACKUPS\yourdb.bak'
(Use the file path that is good for your machine.)
Then the RESTORE:
RESTORE DATABASE yourdbcopy FROM DISK = 'C:\BACKUPS\yourdb.bak'
WITH MOVE 'yourdb' TO 'C:\databasefiles\yourdbcopy.mdf',
MOVE 'yourdb_log' TO 'D:\databaselogs\yourdbcopy.ldf',
REPLACE
Note that you don't to create yourdbcopy in advance.
Again, use the file paths that works on your machine.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||THANKS ALOT GUYS, IT WORKED
SORRY FOR NOT REPLYING SOONER
THANK YOU
Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns948CED96F4639Yazorman@.12
7.0.0.1>...
> [posted and mailed, please reply in news]
> Dhruv (dmalhotr2001@.yahoo.com) writes:
> I use sp_attach_db rarely, but I fail to see see why it would work.
> Then again, I've been wrong before.
> Anyway, the way I copy databases is BACKUP/RESTORE. The BACKUP command
> is a breeze, the RESTORE command was too in SQL 6.5, but these days it's
> a bit complex.
> First use sp_helpdb to see what the logical names of your data files are;
> that's the first column. If your database is named yourdb, then the logica
l
> names are typilcally yourdb and yourdb_log.
> Then the backup:
> BACKUP DATABASE yourdb TO DISK = 'C:\BACKUPS\yourdb.bak'
> (Use the file path that is good for your machine.)
> Then the RESTORE:
> RESTORE DATABASE yourdbcopy FROM DISK = 'C:\BACKUPS\yourdb.bak'
> WITH MOVE 'yourdb' TO 'C:\databasefiles\yourdbcopy.mdf',
> MOVE 'yourdb_log' TO 'D:\databaselogs\yourdbcopy.ldf',
> REPLACE
> Note that you don't to create yourdbcopy in advance.
> Again, use the file paths that works on your machine.

Coping Databases

Hi,
My questions is we want to make a copy of a database onto the same
server while preserving the diagrams, stored procs, etc.
We stopped the SQL service and made a copy of the data and log files.
We attempted to ATTACH the file copies (after re-naming them), but the
embedded file information tells SQL that the database already exists.
Our database is a piece of junk, but we must use it. If we don't want
to use the IMPORT/EXPORT Wizard to Copy Objects (because we get some
errors during the transfer), how can we make an exact copy of the
Database onto the same server while giving the "new" database a
different name?
Thanks
:DHRUVFirstly create a new databas
Then backup old databas
Then restore the old database backup file on newly created database (check force restore over existing database|||There's no need to create the database fist, it will be created during the
restore. Just do a backup and restore, when the db name is altered in EM's
restore dialog, EM will also specify new filenames for the database files.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"mahruti" <anonymous@.discussions.microsoft.com> wrote in message
news:88953F00-4B6F-480F-A3A7-2A9FEB93EA8B@.microsoft.com...
> Firstly create a new database
> Then backup old database
> Then restore the old database backup file on newly created database (check
force restore over existing database)
>|||Correct, I recommend him the long method beacuse I think maybe he can restore to an existing database by accident|||"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0402110919.4a03a3f1@.posting.google.com...
> Hi,
> My questions is we want to make a copy of a database onto the same
> server while preserving the diagrams, stored procs, etc.
> We stopped the SQL service and made a copy of the data and log files.
> We attempted to ATTACH the file copies (after re-naming them), but the
> embedded file information tells SQL that the database already exists.
> Our database is a piece of junk, but we must use it. If we don't want
> to use the IMPORT/EXPORT Wizard to Copy Objects (because we get some
> errors during the transfer), how can we make an exact copy of the
> Database onto the same server while giving the "new" database a
> different name?
> Thanks
> :DHRUV
Probably the quickest and easiest way is to back up the source database,
then restore it with a different name. You can do this from Enterprise
Manager, or using RESTORE. See "Copying Databases" in Books Online.
Simon|||[posted and mailed, please reply in news]
Dhruv (dmalhotr2001@.yahoo.com) writes:
> My questions is we want to make a copy of a database onto the same
> server while preserving the diagrams, stored procs, etc.
> We stopped the SQL service and made a copy of the data and log files.
> We attempted to ATTACH the file copies (after re-naming them), but the
> embedded file information tells SQL that the database already exists.
>
I use sp_attach_db rarely, but I fail to see see why it would work.
Then again, I've been wrong before.
Anyway, the way I copy databases is BACKUP/RESTORE. The BACKUP command
is a breeze, the RESTORE command was too in SQL 6.5, but these days it's
a bit complex.
First use sp_helpdb to see what the logical names of your data files are;
that's the first column. If your database is named yourdb, then the logical
names are typilcally yourdb and yourdb_log.
Then the backup:
BACKUP DATABASE yourdb TO DISK = 'C:\BACKUPS\yourdb.bak'
(Use the file path that is good for your machine.)
Then the RESTORE:
RESTORE DATABASE yourdbcopy FROM DISK = 'C:\BACKUPS\yourdb.bak'
WITH MOVE 'yourdb' TO 'C:\databasefiles\yourdbcopy.mdf',
MOVE 'yourdb_log' TO 'D:\databaselogs\yourdbcopy.ldf',
REPLACE
Note that you don't to create yourdbcopy in advance.
Again, use the file paths that works on your machine.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||D'you have the same data-file names are you trying to save
them on the same folder.. if so just move the copy of the
datafiles to some other folder (assume D:\SQL2K)
Now attach the files using the following statement (Make
sure that you don't specify the name of the database which
already exists)
Exec SP_Attach_DB 'NewDBName','D:\SQL2K\NewDBFile.Mdb',
'D:\SQL2K\NewDBFile.Ldf'
HTH
Thank you,
Saleem Hakani
>--Original Message--
>Hi,
>My questions is we want to make a copy of a database onto
the same
>server while preserving the diagrams, stored procs, etc.
>We stopped the SQL service and made a copy of the data
and log files.
>We attempted to ATTACH the file copies (after re-naming
them), but the
>embedded file information tells SQL that the database
already exists.
>Our database is a piece of junk, but we must use it. If
we don't want
>to use the IMPORT/EXPORT Wizard to Copy Objects (because
we get some
>errors during the transfer), how can we make an exact
copy of the
>Database onto the same server while giving the "new"
database a
>different name?
>Thanks
>:DHRUV
>.
>|||THANKS ALOT GUYS, IT WORKED :):)
SORRY FOR NOT REPLYING SOONER
THANK YOU
Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns948CED96F4639Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> Dhruv (dmalhotr2001@.yahoo.com) writes:
> > My questions is we want to make a copy of a database onto the same
> > server while preserving the diagrams, stored procs, etc.
> >
> > We stopped the SQL service and made a copy of the data and log files.
> >
> > We attempted to ATTACH the file copies (after re-naming them), but the
> > embedded file information tells SQL that the database already exists.
> >
> I use sp_attach_db rarely, but I fail to see see why it would work.
> Then again, I've been wrong before.
> Anyway, the way I copy databases is BACKUP/RESTORE. The BACKUP command
> is a breeze, the RESTORE command was too in SQL 6.5, but these days it's
> a bit complex.
> First use sp_helpdb to see what the logical names of your data files are;
> that's the first column. If your database is named yourdb, then the logical
> names are typilcally yourdb and yourdb_log.
> Then the backup:
> BACKUP DATABASE yourdb TO DISK = 'C:\BACKUPS\yourdb.bak'
> (Use the file path that is good for your machine.)
> Then the RESTORE:
> RESTORE DATABASE yourdbcopy FROM DISK = 'C:\BACKUPS\yourdb.bak'
> WITH MOVE 'yourdb' TO 'C:\databasefiles\yourdbcopy.mdf',
> MOVE 'yourdb_log' TO 'D:\databaselogs\yourdbcopy.ldf',
> REPLACE
> Note that you don't to create yourdbcopy in advance.
> Again, use the file paths that works on your machine.

Coping Databases

Hi,

My questions is we want to make a copy of a database onto the same
server while preserving the diagrams, stored procs, etc.

We stopped the SQL service and made a copy of the data and log files.

We attempted to ATTACH the file copies (after re-naming them), but the
embedded file information tells SQL that the database already exists.

Our database is a piece of junk, but we must use it. If we don't want
to use the IMPORT/EXPORT Wizard to Copy Objects (because we get some
errors during the transfer), how can we make an exact copy of the
Database onto the same server while giving the "new" database a
different name?

Thanks

:DHRUV"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0402110919.4a03a3f1@.posting.google.co m...
> Hi,
> My questions is we want to make a copy of a database onto the same
> server while preserving the diagrams, stored procs, etc.
> We stopped the SQL service and made a copy of the data and log files.
> We attempted to ATTACH the file copies (after re-naming them), but the
> embedded file information tells SQL that the database already exists.
> Our database is a piece of junk, but we must use it. If we don't want
> to use the IMPORT/EXPORT Wizard to Copy Objects (because we get some
> errors during the transfer), how can we make an exact copy of the
> Database onto the same server while giving the "new" database a
> different name?
> Thanks
> :DHRUV

Probably the quickest and easiest way is to back up the source database,
then restore it with a different name. You can do this from Enterprise
Manager, or using RESTORE. See "Copying Databases" in Books Online.

Simon|||[posted and mailed, please reply in news]

Dhruv (dmalhotr2001@.yahoo.com) writes:
> My questions is we want to make a copy of a database onto the same
> server while preserving the diagrams, stored procs, etc.
> We stopped the SQL service and made a copy of the data and log files.
> We attempted to ATTACH the file copies (after re-naming them), but the
> embedded file information tells SQL that the database already exists.

I use sp_attach_db rarely, but I fail to see see why it would work.
Then again, I've been wrong before.

Anyway, the way I copy databases is BACKUP/RESTORE. The BACKUP command
is a breeze, the RESTORE command was too in SQL 6.5, but these days it's
a bit complex.

First use sp_helpdb to see what the logical names of your data files are;
that's the first column. If your database is named yourdb, then the logical
names are typilcally yourdb and yourdb_log.

Then the backup:

BACKUP DATABASE yourdb TO DISK = 'C:\BACKUPS\yourdb.bak'

(Use the file path that is good for your machine.)

Then the RESTORE:

RESTORE DATABASE yourdbcopy FROM DISK = 'C:\BACKUPS\yourdb.bak'
WITH MOVE 'yourdb' TO 'C:\databasefiles\yourdbcopy.mdf',
MOVE 'yourdb_log' TO 'D:\databaselogs\yourdbcopy.ldf',
REPLACE

Note that you don't to create yourdbcopy in advance.

Again, use the file paths that works on your machine.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||THANKS ALOT GUYS, IT WORKED :):)

SORRY FOR NOT REPLYING SOONER

THANK YOU

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns948CED96F4639Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> Dhruv (dmalhotr2001@.yahoo.com) writes:
> > My questions is we want to make a copy of a database onto the same
> > server while preserving the diagrams, stored procs, etc.
> > We stopped the SQL service and made a copy of the data and log files.
> > We attempted to ATTACH the file copies (after re-naming them), but the
> > embedded file information tells SQL that the database already exists.
> I use sp_attach_db rarely, but I fail to see see why it would work.
> Then again, I've been wrong before.
> Anyway, the way I copy databases is BACKUP/RESTORE. The BACKUP command
> is a breeze, the RESTORE command was too in SQL 6.5, but these days it's
> a bit complex.
> First use sp_helpdb to see what the logical names of your data files are;
> that's the first column. If your database is named yourdb, then the logical
> names are typilcally yourdb and yourdb_log.
> Then the backup:
> BACKUP DATABASE yourdb TO DISK = 'C:\BACKUPS\yourdb.bak'
> (Use the file path that is good for your machine.)
> Then the RESTORE:
> RESTORE DATABASE yourdbcopy FROM DISK = 'C:\BACKUPS\yourdb.bak'
> WITH MOVE 'yourdb' TO 'C:\databasefiles\yourdbcopy.mdf',
> MOVE 'yourdb_log' TO 'D:\databaselogs\yourdbcopy.ldf',
> REPLACE
> Note that you don't to create yourdbcopy in advance.
> Again, use the file paths that works on your machine.