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