Copy Database Wizard

I need to copy a database to the same server with a new name. The wizard
does not allow this, I would guess because it does an exact copy. Is there a
simple way to copy it to the same server with a different name?
Thanks,
Doug
The copy database wizard will not work to the same server.
You can stop SQL Server. Make a copy of the files, renaming then in the
process. Then reattach with a different name using sp_attach_db
Or
Backup up the database and restore it with a different name using the WITH
MOVE option. There is an example in Books on Line under teh RESTORE command
of this.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Use the export function within EM to export the database and data into another database with a different name.
STurner
|||you can either use the copy database wizard then do sp_renamedb or
you can use sp_detach_db, copy the files over as new names, the
sp_attach_db.. (both doc'd in books on line)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Doug Stiers" <doug@.nospam.com> wrote in message
news:evt5LaQPEHA.3096@.TK2MSFTNGP09.phx.gbl...
> I need to copy a database to the same server with a new name. The wizard
> does not allow this, I would guess because it does an exact copy. Is there
a
> simple way to copy it to the same server with a different name?
> Thanks,
> Doug
>

Copy Database Wizard

I need to copy a database to the same server with a new name. The wizard
does not allow this, I would guess because it does an exact copy. Is there a
simple way to copy it to the same server with a different name?
Thanks,
DougThe copy database wizard will not work to the same server.
You can stop SQL Server. Make a copy of the files, renaming then in the
process. Then reattach with a different name using sp_attach_db
Or
Backup up the database and restore it with a different name using the WITH
MOVE option. There is an example in Books on Line under teh RESTORE command
of this.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Use the export function within EM to export the database and data into another database with a different name
STurner|||you can either use the copy database wizard then do sp_renamedb or
you can use sp_detach_db, copy the files over as new names, the
sp_attach_db.. (both doc'd in books on line)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Doug Stiers" <doug@.nospam.com> wrote in message
news:evt5LaQPEHA.3096@.TK2MSFTNGP09.phx.gbl...
> I need to copy a database to the same server with a new name. The wizard
> does not allow this, I would guess because it does an exact copy. Is there
a
> simple way to copy it to the same server with a different name?
> Thanks,
> Doug
>

Copy Database Wizard

I need to copy a database to the same server with a new name. The wizard
does not allow this, I would guess because it does an exact copy. Is there a
simple way to copy it to the same server with a different name?
Thanks,
DougThe copy database wizard will not work to the same server.
You can stop SQL Server. Make a copy of the files, renaming then in the
process. Then reattach with a different name using sp_attach_db
Or
Backup up the database and restore it with a different name using the WITH
MOVE option. There is an example in Books on Line under teh RESTORE command
of this.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Use the export function within EM to export the database and data into anoth
er database with a different name.
STurner|||you can either use the copy database wizard then do sp_renamedb or
you can use sp_detach_db, copy the files over as new names, the
sp_attach_db.. (both doc'd in books on line)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Doug Stiers" <doug@.nospam.com> wrote in message
news:evt5LaQPEHA.3096@.TK2MSFTNGP09.phx.gbl...
> I need to copy a database to the same server with a new name. The wizard
> does not allow this, I would guess because it does an exact copy. Is there
a
> simple way to copy it to the same server with a different name?
> Thanks,
> Doug
>

Copy a Table

In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?
Copy is in the popup menu if you right click on a table but there's no PasteOriginally posted by wey97
In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?

Copy is in the popup menu if you right click on a table but there's no Paste|||[QUOTE][SIZE=1]Originally posted by wey97
In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?

You can do this:

SELECT *
INTO B
FROM A

Notes:
B = TABLE NAME of COPY
A = TABLE NAME of ORIGINAL|||Originally posted by wey97
In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?

Copy is in the popup menu if you right click on a table but there's no Paste

You can do paste in QA (shift+Insert or Cntrl+V)|||To copy only the structure with no data (minimally logged)

select * into newtable from oldtable where 0=1

To copy only the structure with data (minimally logged)

select * into newtable from oldtable

To copy data into an existing table (Fully Logged)

insert into newtable select * from oldtable

To Copy data into an existing table where it doesn't already exist (logged)

insert into newtable select * from oldtable where PK not in (select * from newtable)

HTH|||rhigdon: don't do NOT IN, you'll kill everybody else and possibly the server as well. do left outer join on key_field where key_field is null|||I've heard that before, I'll have to test the IO difference. Have you tested it?|||Right click the database then alltasks->export data
A wizard will appear...
Select the table, put the correct source and destination and at the end choose to drop the existing destination object and not append the data.

It's easy and simple. You can also select to mantain permissions, indexes, etc

Paulo

Originally posted by wey97
In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?

Copy is in the popup menu if you right click on a table but there's no Paste|||yup, many times, plus evidence presented by others, plus ms white papers.|||I'm pretty sure you are right, going to do some testing but if you could share any of those white papers I would appreciate it.|||Well, you can't use:

where PK not in (select * from newtable)

anyway.

I'm sure you meant either:
where PK not in (select PK from newtable)
or:
where not exists (select * from oldtable where old.PK = new.PK)

...both of which generate the same optimizer plan, and oddly enough they ran slightly (insignificantly even) faster than the left outer join method, which threw in an extra step for filtering. Maybe because my test table only had about 20,000 rows.

I've use all three methods and never killed a database with them, but I would be interested in reading any MS white papers as well.

blindman|||I believe you are right, Blindman. MS has been tinkering with this part of the query optimizer, and I think the query plans are largely the same. Imagine my surprise when I was about to storm into a developer's cube to have him re-write such a query, when my own re-write did nothing to help.|||Well, the left join method did use a slightly different plan than the other two, so it could potentially be more (or less) efficient for larger tables. If anybody feels the urge to experiment please post the results.

blindman|||Yes BM, I meant

where PK not in (select PK from newtable)

Well, I am a little confused as I rewrote a query that did a NOT IN query to a 1.4 million row table from a 1000 row table and it cost me a total of 3014 logical reads, when I rewrote it in a left outer join it cost me 3126 logical reads so either I am doing something wrong or the difference is insignificant.

I have searched pretty hard but have been unable to find any MS whitepapers discussing this (although I did find an interesting book in my search that I have now ordered!)|||white papers, articles...this issue goes all the way to 6.0 times where non-clustered field was challenged with IN/NOT IN and the optimizer would revert to table scan in both outer and inner queries. it is possible that in the case of the PK, unless it is non-clustered, not in and let outer join would yield the same result, or close to be the same. this is not the point. the point is that IN clause is less preferrable than exists or left outer join. any more requests for articles?|||I agree with Mcrowley in that I beleive this used to be the case but is no longer true. Not sure what you mean by "any more requests for articles" but I take it to mean you don't want to continue this discussion so guess we'll leave it at that...|||I agree with Mcrowley in that I beleive this used to be the case but is no longer true. Not sure what you mean by "any more requests for articles" but I take it to mean you don't want to continue this discussion so guess we'll leave it at that...