Copying databases

Hello,

I'm attempting to launch a new ASP .NET site and I'm having problems moving my database, caused I'm sure by my own ignorance.

I built the database in SQL Server 2005 Express but found that I couldn't use it to copy the database to the deployment server, so I uninstalled it and now I'm using a 6 month trial version of SQL Server 2005 Enterprise Edition.

The server to which I want to copy my database is SQL Server 2003 (version 8.0.760).

My first attempt succeeded in uploading the tables and their data, I then used the "Generate Scripts" command to produce a large query detailing how to create my stored procedures, I ran this script on the destination server and it eventually worked ok.

However, for some reason my Primary Keys, Foreign Keys, Constraints and Identities were not copied to the database on the deployment server at all. The columns all arrived, with the correct datatypes, but the other objects weren't defined at all.

Obviously, these are crucial, so what went wrong and how do I fix it please?

Many thanks

Ben

Are you using "Generate Scripts" or the "Import/Export Data"?

If you're using Generate Scripts, make sure all the options under Table/View Options are true.

If you're using Import/Export Data, It doesn't work so well. Use the Generate Scripts option to actually create the tables/Stored Procedures/Views, etc., then copy the data using the Import/Export option.

Another option is to backup the database, then restore it to the other server.

|||

Thank you for responding.

I initially used "import/export data" but when I found out that it hadn't done my stored procedures I used the "generate scripts" to get the stored procedures as well.

I've tried using generate scripts the way you suggest, and I think it would work IF I didn't already have a load of tables and procedures in the target database. Is there a quick way to drop everything in the database, without deleting the database itself so that I can have a second go at copying my objects?

I daren't delete the database and recreate it because it's on a remote host that I can't get to and only have permissions to alter my own database.

|||

BenCh1: "Is there a quick way to drop everything in the database, without deleting the database itself so that I can have a second go at copying my objects?"

I believe this is enabled with SP2 - November CTP. You can script a "DROP" statement before each create statement. You can obtain a CTP of Service Pack 2 here: http://www.microsoft.com/sql/ctp.mspx.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Copy SQL Server 2005 DB to SQL Server Mobile - is it possible?

I'm creating a prototype of a PDA app using CF.Net 2.0 and SQL Server Mobile for a prospective client and I've gone through and designed,

built and populated a database using SQL Server 2005... and then

realised where I really need it is on the PDA! Without going through

all the pain of setting up SQL replication or writing all the code is there a way to export a

2005 database to SQL Server Mobile so it results in an .SDF file?

I know I can script each table out and run it via the SS2005 management studio when connected to a local .sdf file then copy that to the PDA but the thing is I really want to keep my test data so I don't have to re-enter it again.

TIA for any help... there must be a way to do this but I'm quite new to SQL Server Mobile and 2005.

Mike

you can use Remote Data Access to pull all of the tables from the server or you can use merge replication with AddSubscription(AddOption.CreateDatabase) to dynamically create the SQL Mobile database on your device.

In this MSDN webcast, I demonstrate the latter option: http://msreadiness.com/WS_abstract.asp?eid=15003229

Darren

|||Thanks Darren - but I can't see a link on that to a webcast.... it has details of the event but nothing that I can see to get the webcast - what am I missing? I even went through and registered (for an event that took place last year!) so I'm a bit confused to say the least.

In the meantime I'll google and read up on RDA - I've not used it but it sounds like I'm going to need to work with it to do this app.

Mike|||

after you register, they send you an email with instructions to view the on-demand webcast. digging in to RDA is also a good approach.

Darren

|||

You can use the EXPORT Wizard in SQLServer

Open SQLServer (Full version)

Create a new SDF database for Mobile - no tables required

Click on the database you want to export

Right click on TASKS - EXPORT

Go through the wizard and export to the sdf its all very easy and DOESNT WORK for me

I have founfd that it creates the tables with no problem but when it tries to insert the data into the table it looks for tables called

"table1" complete with the "" round the table!!!

I have let MS know but have not had a reply yet

Can you let me know if it works

You can also get a wizard from PRIMEWORKS.COM that does it all for you

Richar@.bmwdrivers.co.uk

|||Richard,

Thanks - but for me when I run the Export Data wizard from the database I need to export to an .sdf file and then go through to the "Choose a destination" dialog there is no option for SQL Server Mobile at all... what option did you use? (BTW , I used SQL server 2005 not SS 2000 - which version did you mean?)

Mike|||

I've never seen the export option Richard describes work but would love to know if someone gets it to work.

Darren

|||

look in SSEV (SqlSrver Everywhere

It adds it to the SQLKExport Wizard

But it doesnt work and MS are not going to get it to work (Bastards)

Try Primeworks.com

|||

Import/Export from/to SQL Server Everywhere Edition (SSEv) is not implemented.

The immediate question would be why is it shown their in the list of data providers?

All the data related tools (ex: SQL Server Management Studio, Microsoft Office Word, Microsoft Office Excel ..etc) show a list of data providers from a OLEDB Provider database of the system. Since, SSEv also registers its OLEDB Provider to system, it gets listed automatically.

Goof-ups like this happen as the product is still in CTP stage. We are taking care to NOT show up in these tools.

Thanks,

Laxmi

Copy SQL Server 2005 DB to SQL Server Mobile - is it possible?

I'm creating a prototype of a PDA app using CF.Net 2.0 and SQL Server Mobile for a prospective client and I've gone through and designed,

built and populated a database using SQL Server 2005... and then

realised where I really need it is on the PDA! Without going through

all the pain of setting up SQL replication or writing all the code is there a way to export a

2005 database to SQL Server Mobile so it results in an .SDF file?

I know I can script each table out and run it via the SS2005 management studio when connected to a local .sdf file then copy that to the PDA but the thing is I really want to keep my test data so I don't have to re-enter it again.

TIA for any help... there must be a way to do this but I'm quite new to SQL Server Mobile and 2005.

Mike

you can use Remote Data Access to pull all of the tables from the server or you can use merge replication with AddSubscription(AddOption.CreateDatabase) to dynamically create the SQL Mobile database on your device.

In this MSDN webcast, I demonstrate the latter option: http://msreadiness.com/WS_abstract.asp?eid=15003229

Darren

|||Thanks Darren - but I can't see a link on that to a webcast.... it has details of the event but nothing that I can see to get the webcast - what am I missing? I even went through and registered (for an event that took place last year!) so I'm a bit confused to say the least.

In the meantime I'll google and read up on RDA - I've not used it but it sounds like I'm going to need to work with it to do this app.

Mike|||

after you register, they send you an email with instructions to view the on-demand webcast. digging in to RDA is also a good approach.

Darren

|||

You can use the EXPORT Wizard in SQLServer

Open SQLServer (Full version)

Create a new SDF database for Mobile - no tables required

Click on the database you want to export

Right click on TASKS - EXPORT

Go through the wizard and export to the sdf its all very easy and DOESNT WORK for me

I have founfd that it creates the tables with no problem but when it tries to insert the data into the table it looks for tables called

"table1" complete with the "" round the table!!!

I have let MS know but have not had a reply yet

Can you let me know if it works

You can also get a wizard from PRIMEWORKS.COM that does it all for you

Richar@.bmwdrivers.co.uk

|||Richard,

Thanks - but for me when I run the Export Data wizard from the database I need to export to an .sdf file and then go through to the "Choose a destination" dialog there is no option for SQL Server Mobile at all... what option did you use? (BTW , I used SQL server 2005 not SS 2000 - which version did you mean?)

Mike|||

I've never seen the export option Richard describes work but would love to know if someone gets it to work.

Darren

|||

look in SSEV (SqlSrver Everywhere

It adds it to the SQLKExport Wizard

But it doesnt work and MS are not going to get it to work (Bastards)

Try Primeworks.com

|||

Import/Export from/to SQL Server Everywhere Edition (SSEv) is not implemented.

The immediate question would be why is it shown their in the list of data providers?

All the data related tools (ex: SQL Server Management Studio, Microsoft Office Word, Microsoft Office Excel ..etc) show a list of data providers from a OLEDB Provider database of the system. Since, SSEv also registers its OLEDB Provider to system, it gets listed automatically.

Goof-ups like this happen as the product is still in CTP stage. We are taking care to NOT show up in these tools.

Thanks,

Laxmi

Copy SQL Server 2005 DB to SQL Server Mobile - is it possible?

I'm creating a prototype of a PDA app using CF.Net 2.0 and SQL Server Mobile for a prospective client and I've gone through and designed,

built and populated a database using SQL Server 2005... and then

realised where I really need it is on the PDA! Without going through

all the pain of setting up SQL replication or writing all the code is there a way to export a

2005 database to SQL Server Mobile so it results in an .SDF file?

I know I can script each table out and run it via the SS2005 management studio when connected to a local .sdf file then copy that to the PDA but the thing is I really want to keep my test data so I don't have to re-enter it again.

TIA for any help... there must be a way to do this but I'm quite new to SQL Server Mobile and 2005.

Mike

you can use Remote Data Access to pull all of the tables from the server or you can use merge replication with AddSubscription(AddOption.CreateDatabase) to dynamically create the SQL Mobile database on your device.

In this MSDN webcast, I demonstrate the latter option: http://msreadiness.com/WS_abstract.asp?eid=15003229

Darren

|||Thanks Darren - but I can't see a link on that to a webcast.... it has details of the event but nothing that I can see to get the webcast - what am I missing? I even went through and registered (for an event that took place last year!) so I'm a bit confused to say the least.

In the meantime I'll google and read up on RDA - I've not used it but it sounds like I'm going to need to work with it to do this app.

Mike|||

after you register, they send you an email with instructions to view the on-demand webcast. digging in to RDA is also a good approach.

Darren

|||

You can use the EXPORT Wizard in SQLServer

Open SQLServer (Full version)

Create a new SDF database for Mobile - no tables required

Click on the database you want to export

Right click on TASKS - EXPORT

Go through the wizard and export to the sdf its all very easy and DOESNT WORK for me

I have founfd that it creates the tables with no problem but when it tries to insert the data into the table it looks for tables called

"table1" complete with the "" round the table!!!

I have let MS know but have not had a reply yet

Can you let me know if it works

You can also get a wizard from PRIMEWORKS.COM that does it all for you

Richar@.bmwdrivers.co.uk

|||Richard,

Thanks - but for me when I run the Export Data wizard from the database I need to export to an .sdf file and then go through to the "Choose a destination" dialog there is no option for SQL Server Mobile at all... what option did you use? (BTW , I used SQL server 2005 not SS 2000 - which version did you mean?)

Mike|||

I've never seen the export option Richard describes work but would love to know if someone gets it to work.

Darren

|||

look in SSEV (SqlSrver Everywhere

It adds it to the SQLKExport Wizard

But it doesnt work and MS are not going to get it to work (Bastards)

Try Primeworks.com

|||

Import/Export from/to SQL Server Everywhere Edition (SSEv) is not implemented.

The immediate question would be why is it shown their in the list of data providers?

All the data related tools (ex: SQL Server Management Studio, Microsoft Office Word, Microsoft Office Excel ..etc) show a list of data providers from a OLEDB Provider database of the system. Since, SSEv also registers its OLEDB Provider to system, it gets listed automatically.

Goof-ups like this happen as the product is still in CTP stage. We are taking care to NOT show up in these tools.

Thanks,

Laxmi