Script Database 1

[ca8msm]

I've got a locally based SQL Server 2005 Express database that I need to upload to a web host. The web host doesn't support attaching an .mdf or restoring from a .bak file (well, they do, but I have to create a support ticket and I've got lots of instances of this database to attach and this may continue on a daily basis, so this isn't an ideal option for me).

So, what I'd like to be able to do is script the entire database and execute the script. This would have to include logins, users, sp's, functions, tables and any existing data (this is default data that the db needs). What options do I have in creating these scripts? From what I've tried, using SSMS, the generate script task won't include data, plus it doesn't seem to have any concept of the order in which to create objects (for example, it may create a view based on table before it created the table). Are there any built-in tools methods I can use to do what I need? Alternatively, are there any good 3rd party applications which do this well?

Thanks,
Mark


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[Otto]

Have you tried to make several scripts instead of one?
Script the database to one file, then tables to another, then views, etc.
I cannot manage the SSMS to script data (only structure). If I need to script data, I use the EMS SQL Manager [URL unfurl="true"]http://www.sqlmanager.net/products/mssql/manager[/url]. Try the free version (but it can save only the entire database, you cannot select individual tables). You can find another good tool at [URL unfurl="true"]http://www.toadsoft.com/toadsqlserver/toad_sqlserver.htm [/url]

 

[ca8msm]

I hadn't tried using separate scripts, but I will give that a go. I'll also try out the two products you suggested, although the EMS SQL Manager seems to suggest it can do what I need from the product features page.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[ca8msm]

OK, just as an update, I tried out the EMS SQL Manager and it provides better functionality than I can do with SMSS, i.e. I can include data as part of my script which is good news. However, it still doesn't produce the scripts (unless I've missed an option) to create the database in the correct order as you get errors that certain objects are missing when running the script.

I'll try out the TOAD tool next, but it looks as though I may be stuck generating the scripts for each type of object manually.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[ca8msm]

After trying a few different tools, the one I found the easiest to use, and which produced the best results was SQL Packager from Red Gate.

http://www.red-gate.com/products/SQL_Packager/index.htm

This creates an .NET executable package containing both the structure and data of any or all the objects you deem necessary.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[ptheriault]

Mark,
Aside from getting a 3rd party tool like SQL Change Manager have you tried to set up snap shot replication? That will generate all the script for database objects and data. You would just need to script the db create and users.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ca8msm]

Paul,

No, I haven't tried that as I didn't realise that was an option. I'll have a play about with that later, thanks.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]