COPY DATABASE WIZARD

Hello,

I have been trying to use the Copy Database wizard, once I get through all the steps of defining the databases to use and hit finish (using SMO transfer), I get an

'Copy Database : No such interface supported'

Error and the action terminates! How can I fix this?

Regards

I am assuming you are copying both from and to SQL 2005 based databases? Not sure if Copy DB Wizard will support DMO for the older sql2000 servers.|||

Hi Raaj,

Please take a look at the existing CDW Feedback issues and either add your comments to a related one you see, or post a new Feedback Defect with the following information:

1. Source and destination server versions (8.0 or Yukon), including Service Packs.

2. Whether SQL Agent is running on either source and/or destination, and what version.

3. Whether 8.0 QA is installed on either machine.

4. A repro database attached, to either sp_attach or as a script to run. The smaller the better as long as it exhibits the problem.

We are working on CDW issues for SQL Server 2005 SP2, and are very interested in having good coverage on issues such as yours.

Ed Dudenhoefer

SQL Server Team

Copy database from SQL Server Standard to SQL Server Express

Hello,

I am currently faced with a problem of copying a database from SQL Server 2000 to SQL Server 2005 Express using the SMO classes. I am using VB.NET and i'm just trying to take an exact copy from the server. Any ideas on how I would do this in code?

Any links would be great.

Thanks.

Rob

Try this code, it should solve your problem:

Dim srv As Server
Dim srvConn As ServerConnection
srv = New Server("MyServer")
srvConn = srv.ConnectionContext
srvConn.LoginSecure = True

Dim db As Database

db = srv.Databases("AdventureWorks")
Dim strDBName As String
strDBName = "TestDatabase"

'Define a Transfer object and set the required options and properties.
Dim xfr As Transfer
xfr = New Transfer(db)
xfr.CopyAllObjects = True
xfr.CopyAllUsers = True
xfr.Options.WithDependencies = True
xfr.Options.ContinueScriptingOnError = True
xfr.DestinationDatabase = strDBName
xfr.DestinationServer = srv.Name
xfr.DestinationLoginSecure = True
xfr.CopySchema = True
'Script the transfer.
xfr.TransferData()

|||

I've tried that code, still no luck.

My current code is the below,

LocalSqlServer2005Connection.Connect()
Dim localDb As New Database(LocalSqlServer2005, "CopyOfDb")
localDb.Create()
Dim xfr As New Transfer(db)
xfr.CopyAllTables = True
xfr.Options.WithDependencies = True
xfr.Options.ContinueScriptingOnError = True
xfr.DestinationDatabase = "CopyOfDb"
xfr.DestinationServer = LocalSqlServer2005.Name
xfr.DestinationLoginSecure = True
xfr.CopySchema = True
'Script the transfer. Alternatively perform immediate data transfer with TransferData method.
xfr.TransferData()

This throws

Invalid access to memory location. (Exception from HRESULT: 0x800703E6)

However if I remove xfr.TransferData() and replace it with xfr.ScriptTransfer() then just a empty database is created with no data.

|||

Hello Guys,

I've managed to fix the problem now, the error caused was because I never had Service Pack 1 installed for SQL Server Express. if you are having the same problem go to http://msdn.microsoft.com/vstudio/express/sql/download/ and download service pack 1 then try.

Least it works now 4 hours later :-)

Rob