how do i make a copy of a database on the same server

how do i make a copy the same server, but give the new
copy a different name? I have 8.0.You have many options:
- Backup the old database, create a new database with a different name and
restore into it
- Detach the old database, copy the files to a new location, and attach both
sets of files
- Use DTS to transfer the data
You can look up all of these options in Books Online. The first two options
are probably better than the third.
<anonymous@.discussions.microsoft.com> wrote in message
news:860a01c4321c$fcedc310$a301280a@.phx.gbl...
> how do i make a copy the same server, but give the new
> copy a different name? I have 8.0.|||Hi,
Mention the new name when you do RESTORE.
In Query analyzer :
RESTORE DATABASE <newname>
.....
In enterprise manager, right click on database node| all tasks|restore
database, feed in the new name and point to the backup location.
Make sure to mention the new path for mdf and ldf files.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
<anonymous@.discussions.microsoft.com> wrote in message
news:860a01c4321c$fcedc310$a301280a@.phx.gbl...
> how do i make a copy the same server, but give the new
> copy a different name? I have 8.0.|||This will run in query analyzer - this example takes a DB called FUNDS and
restores it as FUNDS_COPY.
BACKUP DATABASE Funds
TO DISK = 'c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Funds_Copy.bak'
RESTORE FILELISTONLY
FROM DISK = 'd:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Funds_Copy.bak'
RESTORE DATABASE Funds_Copy
FROM DISK = 'd:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Funds_Copy.bak'
WITH MOVE 'Funds_data' TO 'd:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Funds_Copy.mdf',
MOVE 'Funds_log' TO 'd:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Funds_Copy.ldf'
GO
<anonymous@.discussions.microsoft.com> wrote in message
news:860a01c4321c$fcedc310$a301280a@.phx.gbl...
> how do i make a copy the same server, but give the new
> copy a different name? I have 8.0.

how do i make a copy of a database on the same server

how do i make a copy the same server, but give the new
copy a different name? I have 8.0.
You have many options:
- Backup the old database, create a new database with a different name and
restore into it
- Detach the old database, copy the files to a new location, and attach both
sets of files
- Use DTS to transfer the data
You can look up all of these options in Books Online. The first two options
are probably better than the third.
<anonymous@.discussions.microsoft.com> wrote in message
news:860a01c4321c$fcedc310$a301280a@.phx.gbl...
> how do i make a copy the same server, but give the new
> copy a different name? I have 8.0.
|||Hi,
Mention the new name when you do RESTORE.
In Query analyzer :
RESTORE DATABASE <newname>
......
In enterprise manager, right click on database node| all tasks|restore
database, feed in the new name and point to the backup location.
Make sure to mention the new path for mdf and ldf files.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
<anonymous@.discussions.microsoft.com> wrote in message
news:860a01c4321c$fcedc310$a301280a@.phx.gbl...
> how do i make a copy the same server, but give the new
> copy a different name? I have 8.0.
|||This will run in query analyzer - this example takes a DB called FUNDS and
restores it as FUNDS_COPY.
BACKUP DATABASE Funds
TO DISK = 'c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Funds_Copy.bak'
RESTORE FILELISTONLY
FROM DISK = 'd:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Funds_Copy.bak'
RESTORE DATABASE Funds_Copy
FROM DISK = 'd:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Funds_Copy.bak'
WITH MOVE 'Funds_data' TO 'd:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Funds_Copy.mdf',
MOVE 'Funds_log' TO 'd:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Funds_Copy.ldf'
GO
<anonymous@.discussions.microsoft.com> wrote in message
news:860a01c4321c$fcedc310$a301280a@.phx.gbl...
> how do i make a copy the same server, but give the new
> copy a different name? I have 8.0.

how do i make a copy of a database on the same server

how do i make a copy the same server, but give the new
copy a different name? I have 8.0.You have many options:
- Backup the old database, create a new database with a different name and
restore into it
- Detach the old database, copy the files to a new location, and attach both
sets of files
- Use DTS to transfer the data
You can look up all of these options in Books Online. The first two options
are probably better than the third.
<anonymous@.discussions.microsoft.com> wrote in message
news:860a01c4321c$fcedc310$a301280a@.phx.gbl...
> how do i make a copy the same server, but give the new
> copy a different name? I have 8.0.|||Hi,
Mention the new name when you do RESTORE.
In Query analyzer :
RESTORE DATABASE <newname>
.....
In enterprise manager, right click on database node| all tasks|restore
database, feed in the new name and point to the backup location.
Make sure to mention the new path for mdf and ldf files.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
<anonymous@.discussions.microsoft.com> wrote in message
news:860a01c4321c$fcedc310$a301280a@.phx.gbl...
> how do i make a copy the same server, but give the new
> copy a different name? I have 8.0.|||This will run in query analyzer - this example takes a DB called FUNDS and
restores it as FUNDS_COPY.
BACKUP DATABASE Funds
TO DISK = 'c:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Funds_Copy.bak'
RESTORE FILELISTONLY
FROM DISK = 'd:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Funds_Copy.bak'
RESTORE DATABASE Funds_Copy
FROM DISK = 'd:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Funds_Copy.bak'
WITH MOVE 'Funds_data' TO 'd:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Funds_Copy.mdf',
MOVE 'Funds_log' TO 'd:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Funds_Copy.ldf'
GO
<anonymous@.discussions.microsoft.com> wrote in message
news:860a01c4321c$fcedc310$a301280a@.phx.gbl...
> how do i make a copy the same server, but give the new
> copy a different name? I have 8.0.

How do I get Copy Database Wizard to work?

This is SO frustrating. Go through the wizard selecting the db's I want to pull down from production, gives me an option to get logins (great), but only gives option to pull down stored procedures in Master db (I am a DB owner on these database btw), when I execute it creates the database, and the logins, but doesn't pull down any of the tables or the stored procedures or views unless they were ones I created recently. It's like theres some wierd permissions thing, but like I said, I'm a DB on these databases.

Try using Copy Database Wizard (CDW) in the Community Technology Preview for SP2. It has not released yet, but check www.microsoft.com/sql/ for it in the coming months. Many issues were fixed including areas like login/user SID orphaning, permissions and roles. It is hard to tell if we fixed your specific issue, although I have not seen cases where tables, views or SPs inside the database being transferred did not copy in the way you describe. There was also an issue prior to SP2 of trying to copy objects which were already in the model database on the destination server instance.

If you see the problem after trying the CTP, please open a defect against it and include:

1. sufficient T-SQL scripting or attach the .mdf file of a minimal case.
2. copy/paste the Summary page just before the transfer begins with the details of the run.
3. any relevant error lines from the Windows Event Application Log (just the error ones is fine).
4. the <MyDocumentsAndSettings>\Local Settings\Application Data\Microsoft\SQL Server\Smo\TransferDump*.sql files. There will be one file for the Extra Server Objects, and optionally one file for each database if the Smo method of transfer was used.

Hope this helps,

Ed Dudenhoefer
Microsoft SQL Server

How do I get Copy Database Wizard to work?

This is SO frustrating. Go through the wizard selecting the db's I want to pull down from production, gives me an option to get logins (great), but only gives option to pull down stored procedures in Master db (I am a DB owner on these database btw), when I execute it creates the database, and the logins, but doesn't pull down any of the tables or the stored procedures or views unless they were ones I created recently. It's like theres some wierd permissions thing, but like I said, I'm a DB on these databases.

Try using Copy Database Wizard (CDW) in the Community Technology Preview for SP2. It has not released yet, but check www.microsoft.com/sql/ for it in the coming months. Many issues were fixed including areas like login/user SID orphaning, permissions and roles. It is hard to tell if we fixed your specific issue, although I have not seen cases where tables, views or SPs inside the database being transferred did not copy in the way you describe. There was also an issue prior to SP2 of trying to copy objects which were already in the model database on the destination server instance.

If you see the problem after trying the CTP, please open a defect against it and include:

1. sufficient T-SQL scripting or attach the .mdf file of a minimal case.
2. copy/paste the Summary page just before the transfer begins with the details of the run.
3. any relevant error lines from the Windows Event Application Log (just the error ones is fine).
4. the <MyDocumentsAndSettings>\Local Settings\Application Data\Microsoft\SQL Server\Smo\TransferDump*.sql files. There will be one file for the Extra Server Objects, and optionally one file for each database if the Smo method of transfer was used.

Hope this helps,

Ed Dudenhoefer
Microsoft SQL Server

how do I get a trigger just to copy the inserted row.

HI I have a trigger which I want to copy any rows in the MSmerge_history into a archieve table. I am using this trigger:

CREATE TRIGGER History_replication ON
[dbo].[MSmerge_history]
FOR INSERT
AS
INSERT [dbo].[MSmerge_history_archive]
(
agent_id,
runstatus,
start_time,
[time],
duration,
comments,
delivery_time,
delivery_rate,
publisher_insertcount,
publisher_updatecount,
publisher_deletecount,
publisher_conflictcount,
subscriber_insertcount,
subscriber_updatecount,
subscriber_deletecount,
subscriber_conflictcount,
error_id,
[timestamp] ,
updateable_row
)
SELECT
agent_id,
runstatus,
start_time,
[time] ,
duration,
comments,
delivery_time,
delivery_rate,
publisher_insertcount,
publisher_updatecount,
publisher_deletecount,
publisher_conflictcount,
subscriber_insertcount,
subscriber_updatecount,
subscriber_deletecount,
subscriber_conflictcount,
error_id,
[timestamp],

How this copy the entire contence of the table into the archive each time someone insert a row. How do I get it to only insert the row which had triggered the insert? EdCREATE TRIGGER History_replication ON
[dbo].[MSmerge_history]
FOR INSERT
AS
INSERT [dbo].[MSmerge_history_archive]
(
agent_id,
runstatus,
start_time,
[time],
duration,
comments,
delivery_time,
delivery_rate,
publisher_insertcount,
publisher_updatecount,
publisher_deletecount,
publisher_conflictcount,
subscriber_insertcount,
subscriber_updatecount,
subscriber_deletecount,
subscriber_conflictcount,
error_id,
[timestamp] ,
updateable_row
)
SELECT
agent_id,
runstatus,
start_time,
[time] ,
duration,
comments,
delivery_time,
delivery_rate,
publisher_insertcount,
publisher_updatecount,
publisher_deletecount,
publisher_conflictcount,
subscriber_insertcount,
subscriber_updatecount,
subscriber_deletecount,
subscriber_conflictcount,
error_id,
[timestamp]
FROM inserted

Inserted is virtual table used by triggers.|||Excellent thanks I've got that working now, Ed

How do I export/import/copy Diagrams?

Is there a way to copy all the diagrams from one database to a duplicate
database?
Thank you.
Scott
http://support.microsoft.com/default...b;en-us;320125
David Portas
SQL Server MVP
"Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
news:30932B9B-277C-4CF0-9060-A69D8DB86D77@.microsoft.com...
> Is there a way to copy all the diagrams from one database to a duplicate
> database?
> Thank you.
> Scott
|||Thank you very much. It worked.
"David Portas" wrote:

> http://support.microsoft.com/default...b;en-us;320125
> --
> David Portas
> SQL Server MVP
> --
> "Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
> news:30932B9B-277C-4CF0-9060-A69D8DB86D77@.microsoft.com...
>
>

How do I export/import/copy Diagrams?

Is there a way to copy all the diagrams from one database to a duplicate
database?
Thank you.
Scotthttp://support.microsoft.com/defaul...kb;en-us;320125
David Portas
SQL Server MVP
--
"Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
news:30932B9B-277C-4CF0-9060-A69D8DB86D77@.microsoft.com...
> Is there a way to copy all the diagrams from one database to a duplicate
> database?
> Thank you.
> Scott|||Thank you very much. It worked.
"David Portas" wrote:

> http://support.microsoft.com/defaul...kb;en-us;320125
> --
> David Portas
> SQL Server MVP
> --
> "Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
> news:30932B9B-277C-4CF0-9060-A69D8DB86D77@.microsoft.com...
>
>

How do I export/import/copy Diagrams?

Is there a way to copy all the diagrams from one database to a duplicate
database?
Thank you.
Scotthttp://support.microsoft.com/default.aspx?scid=kb;en-us;320125
--
David Portas
SQL Server MVP
--
"Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
news:30932B9B-277C-4CF0-9060-A69D8DB86D77@.microsoft.com...
> Is there a way to copy all the diagrams from one database to a duplicate
> database?
> Thank you.
> Scott|||Thank you very much. It worked.
"David Portas" wrote:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;320125
> --
> David Portas
> SQL Server MVP
> --
> "Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
> news:30932B9B-277C-4CF0-9060-A69D8DB86D77@.microsoft.com...
> > Is there a way to copy all the diagrams from one database to a duplicate
> > database?
> > Thank you.
> >
> > Scott
>
>

How do I do this?

In my DB I have inadvertantly dropped a table. I do however have a backup. How can I get a copy of the (1) table from my backup DB to my current DB?Restore the backup and copy the table to your database, If there are no indexes and complex objects you can do SELECT INTO, SQL Server will create a simple table in your database. The other option create a new table with same name and copy the data from the restored database. Run a search for INTO in the BOL. Hope this helps.|||

Jim:

Create an empty database and restore your database to the newly created empty database. Then run the DDL to re-create the table in your old database and then copy the data from the restored database to the old database. Once you are satisfied that the old table is ok, drop the restored database.

[ Obvioulsy, I agree with Caddre. :-) ]

|||Thanks, here is the part I am missing. what is the syntax for going from one DB to another

i.e.

SELECT * INTO CurrentDB.NewTable FROM BackupDB.ExistingTable|||

Try this

SELECT * INTO CurrentDB.dbo.Newtable FROM BackupDB.dbo.ExistingTable

|||

SQL Server uses a 'four-part' naming convention. It follows this pattern:

Server.Database.schema.table

In 'normal' use, if you supply just the table name,

the 'schema' is assumed to be either the default user/owner or schema, the database is the current connected database, and the server is the current connected server.|||Thanks! That is exactly what I was looking for, issue resolved!