Copy Entire DB on same Server

Hi All,

Could anybody tell me how to make a copy of an entire database on the same server? The copy wizard won't allow the a copy to be made on the same server.

And I believe I won't be able to detach the database and re-attach it to a newly created database with a different name.

Thanks in advance!My solution would be to do a backup, then a restore of your database to a new database name.

-PatPsqlsql

Copy Database Problem SQL SERVER 2005

Hi guys, I am trying to copy an entire SQL SERVER 2000 Database from the SERVER to any place in my laptop in order to run some tests without being connected to the net and the real DB. I am using the "Copy Database" function in Microsoft SQL Server Management Studio, but getting the Error described below: I will appreciate any help. Thanks in advance, Aldo. Failed to connect to server MOSHE-DELL. (Copy Database Wizard) Program Location: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect() at Microsoft.SqlServer.Management.CopyDatabaseWizard.DestinationServerPage.OnLeavePage(LeavePageEventArgs le) An error has occurred while establishing a connection to the server.When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476 Error Number: 2 Severity: 20 State: 0 Program Location: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity) at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

Check your duplicate post here.

Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).

|||

Okay. I just didn't know which one is the right place to post this question.

Thanks anyway.

Copy Database on same machine

How would I copy an entire database to another (using a different
name, of course) on the same server? I am new with working with
databases, so any help would be appreciated. Thanks."Andy Visniewski" <andy@.pepperell.net> wrote in message
news:f66e2e34.0309021046.4afef731@.posting.google.c om...
> How would I copy an entire database to another (using a different
> name, of course) on the same server? I am new with working with
> databases, so any help would be appreciated. Thanks.|||Hi

You could backup/restore or detach/attach

http://support.microsoft.com/defaul...n-us;Q314546#10

With the backup/restore you will have to move the data files (WITH MOVE),
with detach/attach they will have to be copied at OS level.

As they are on the same machine you don't need to worry about
logins/orphaned users.

John

"Andy Visniewski" <andy@.pepperell.net> wrote in message
news:f66e2e34.0309021046.4afef731@.posting.google.c om...
> How would I copy an entire database to another (using a different
> name, of course) on the same server? I am new with working with
> databases, so any help would be appreciated. Thanks.|||On 2 Sep 2003 11:46:11 -0700 in comp.databases.ms-sqlserver,
andy@.pepperell.net (Andy Visniewski) wrote:

>How would I copy an entire database to another (using a different
>name, of course) on the same server? I am new with working with
>databases, so any help would be appreciated. Thanks.

DTS (Enterprise Manager, highlight database, right click, all tasks,
export) Create a new one when prompted on target, use same server.
When you get to a page in the wizard with 3 radio buttons, choose the
bottom one, copy SQL Server objects.

or Again, in the same place, right click, all tasks, backup, backup to
a file. Now in the same place again, choose Restore from the all tasks
menu, the default is to show backups of the currently highlighted
database, which is what you want, at the top of this dialog, type in a
different name for "restore as database...".

--
A)bort, R)etry, I)nfluence with large hammer.

(replace sithlord with trevor for email)

Copy Database Error using SQL SERVER 2005

Hi guys, I am trying to copy an entire SQL SERVER 2000 Database from the net server called "SERVER" to any place in my laptop called "MOSHE-DELL" in order to run some tests without being connected to the net and the real DB. I am using the "Copy Database" function in Microsoft SQL Server Management Studio, but getting the Error described below: I will appreciate any help. Thanks in advance, Aldo. Failed to connect to server MOSHE-DELL. (Copy Database Wizard) Program Location: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect() at Microsoft.SqlServer.Management.CopyDatabaseWizard.DestinationServerPage.OnLeavePage(LeavePageEventArgs le) An error has occurred while establishing a connection to the server.When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476 Error Number: 2 Severity: 20 State: 0 Program Location: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity) at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

Hi,

There are a few things to check to help you along abit.

1) Connect to your SQL server via the "SQL Server Configuration Manager" and check services and that "Client Protocols" have TCP/IP and named pipes configured.
2)You should also ensure yiu are connecting via the correct machie and instance name be default it will be MOSHE-DELL\MSSQLSERVER.

CU

tosc

|||

Hi Tosc,

Where should I configure my SQL SERVER 2005 (in the local machine) in order to allow remote connections?

|||check the firewall on the Moshe-Dell server and verify that the correct ports are open.|||I have tried following the indications in: http://www.west-wind.com/WebLog/posts/4493.aspx and: http://support.microsoft.com/kb/914277 but this time getting the Error below: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) SQLServerAgent is not currently running so it cannot be notified of this action. (.Net SqlClient Data Provider)
|||

Hi,

By default, the SQL Server Agent service is disabled when SQL Server 2005 is installed unless the user explicitly chooses to autostart the service

By using the SQL Server 2005 Surface Area Configuration you could configure the SQLSERVERAGENT service!

Configuring SQL Server Agent

cu

tosc

|||

Using SSMS, on the left pane (Object Explorer), right click on SQL Server Agent. Select Start.

Then again right click on SQL Server Agent, select Properties. Verify that the AutoStart boxes are checked for both SQL Server and SQL Server Agent.

Copy database compability

Hi,

In sql 2000 was possible to copy entire database objects from one db to another. This includes tables, pk, indexes, views, sp, functions, logins and so on.

This is very usefull because at the same time it creates a full log with entire database scripting, it can be used to track changes in databases.

In sql 2005, I can't find a solution like this.

Someone have an idea to this?

Hi

Even in SQL2005 you can copy whole database with complete schema intact using SSIS (Transfer database task).

Thanx

Copy database

What is the best way to copy an entire SQL Server 2K DB? I want to take an
existing DB on one computer, and move its entirety to another. It seems
that DTS will only import/export tables... I'd like to be able to get the
whole enchilata, schema, tables, etc.
Best way is either do backup and restore or attach it.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/featu...le.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/colu...rdatabases.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
http://www.sqlservercentral.com/scri...tions/1598.asp Script
Roles and Permissions
Andrew J. Kelly SQL MVP
"William F. O'Neill" <wfoneill001@.bellsouth.net> wrote in message
news:enSR%23%236FGHA.1088@.tk2msftngp13.phx.gbl...
> What is the best way to copy an entire SQL Server 2K DB? I want to take
> an existing DB on one computer, and move its entirety to another. It
> seems that DTS will only import/export tables... I'd like to be able to
> get the whole enchilata, schema, tables, etc.
>

Coplicated view - picking from a=b or from c&d =e

I realized there was something wrong with my view when an entire line
was missing. I believe it is caused by an inner join I did on one
column to create another column which may not actually have a value.
let me show you.
Table A ViewA ViewB
type1 type1
type2 type2
type3 type3
answer answer
Pretend that any / all items in Table A can be null. What I want the
view to do is either 1) have both viewA and ViewB answer show up even
if its null or 2) try to get viewB answer but if not, then go to ViewA
answer, then null.
Yes, that is a VIEW doing this...or so I hope.
Any ideas?
John
RocketMan,
Try:
select
coalesce(c.c1, b.c1, a.c1)
from
tableA as a
full outer join
viewA as b
a.c1 = b.c1
full outer join
viewB as c
on a.c1 = c.c1
GO
AMB
"RocketMan" wrote:

> I realized there was something wrong with my view when an entire line
> was missing. I believe it is caused by an inner join I did on one
> column to create another column which may not actually have a value.
> let me show you.
> Table A ViewA ViewB
> type1 type1
> type2 type2
> type3 type3
> answer answer
> Pretend that any / all items in Table A can be null. What I want the
> view to do is either 1) have both viewA and ViewB answer show up even
> if its null or 2) try to get viewB answer but if not, then go to ViewA
> answer, then null.
> Yes, that is a VIEW doing this...or so I hope.
> Any ideas?
> John
>
|||Better try this one:
SELECT
coalesce(c.c1, b.c1, a.c1) AS whatever
from
tableA as a
full outer JOIN
(
viewA as b
full outer join
viewB as c
ON b.c1 = c.c1
)
ON a.c1 = COALESCE(c.c1, b.c1)
GO
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> RocketMan,
> Try:
> select
> coalesce(c.c1, b.c1, a.c1)
> from
> tableA as a
> full outer join
> viewA as b
> a.c1 = b.c1
> full outer join
> viewB as c
> on a.c1 = c.c1
> GO
> AMB
> "RocketMan" wrote:
|||In a way, I answered my own question with a question...
When is a null not a null? When its a ''
Adding this to the views ( of other tables) made the join work and I
was able to get the line to appear.
THANKS for the help. I KNOW someone sometime will be searching for
this kind of help

Coplicated view - picking from a=b or from c&d =e

I realized there was something wrong with my view when an entire line
was missing. I believe it is caused by an inner join I did on one
column to create another column which may not actually have a value.
let me show you.
Table A ViewA ViewB
type1 type1
type2 type2
type3 type3
answer answer
Pretend that any / all items in Table A can be null. What I want the
view to do is either 1) have both viewA and ViewB answer show up even
if its null or 2) try to get viewB answer but if not, then go to ViewA
answer, then null.
Yes, that is a VIEW doing this...or so I hope.
Any ideas?
JohnRocketMan,
Try:
select
coalesce(c.c1, b.c1, a.c1)
from
tableA as a
full outer join
viewA as b
a.c1 = b.c1
full outer join
viewB as c
on a.c1 = c.c1
GO
AMB
"RocketMan" wrote:
> I realized there was something wrong with my view when an entire line
> was missing. I believe it is caused by an inner join I did on one
> column to create another column which may not actually have a value.
> let me show you.
> Table A ViewA ViewB
> type1 type1
> type2 type2
> type3 type3
> answer answer
> Pretend that any / all items in Table A can be null. What I want the
> view to do is either 1) have both viewA and ViewB answer show up even
> if its null or 2) try to get viewB answer but if not, then go to ViewA
> answer, then null.
> Yes, that is a VIEW doing this...or so I hope.
> Any ideas?
> John
>|||Better try this one:
SELECT
coalesce(c.c1, b.c1, a.c1) AS whatever
from
tableA as a
full outer JOIN
(
viewA as b
full outer join
viewB as c
ON b.c1 = c.c1
)
ON a.c1 = COALESCE(c.c1, b.c1)
GO
AMB
"Alejandro Mesa" wrote:
> RocketMan,
> Try:
> select
> coalesce(c.c1, b.c1, a.c1)
> from
> tableA as a
> full outer join
> viewA as b
> a.c1 = b.c1
> full outer join
> viewB as c
> on a.c1 = c.c1
> GO
> AMB
> "RocketMan" wrote:
> > I realized there was something wrong with my view when an entire line
> > was missing. I believe it is caused by an inner join I did on one
> > column to create another column which may not actually have a value.
> > let me show you.
> >
> > Table A ViewA ViewB
> > type1 type1
> > type2 type2
> > type3 type3
> > answer answer
> >
> > Pretend that any / all items in Table A can be null. What I want the
> > view to do is either 1) have both viewA and ViewB answer show up even
> > if its null or 2) try to get viewB answer but if not, then go to ViewA
> > answer, then null.
> >
> > Yes, that is a VIEW doing this...or so I hope.
> >
> > Any ideas?
> >
> > John
> >|||In a way, I answered my own question with a question...
When is a null not a null? When its a ''
Adding this to the views ( of other tables) made the join work and I
was able to get the line to appear.
THANKS for the help. I KNOW someone sometime will be searching for
this kind of help