Copy db with dtswizard doesnt export triggers and stored procs

I am using dtswizard to export my db but it doesn't export triggers and all stored procedures. Am I doing anything wrong or is there a better way to do an exact copy of my DB? I am not able to use backup/restore because my db is on a server to which I don't have admin-right, and I have to pay my hoster to create a backup and email me.

TIA

Thomas

The SQL Server team have created a tool to move your database to a hosting server, try the link below for details. Post again if you still need help.

http://weblogs.asp.net/scottgu/archive/2007/01/11/tip-trick-how-to-upload-a-sql-file-to-a-hoster-and-execute-it-to-deploy-a-sql-database.aspx

|||

If you know IP or dns name of SQL server on your Host and you have user name and password to connect to your database I would recommend to script database to Query window next switch connection for this window to you host SQL server and just run it.

I did it for my web host SQL server and it worked perfectly.

If you can connect to your web SQL server from management studio it will pay in the future because you can modify you database using Microsoft tools which is probably better than any asp.net page.

Thanks

|||Actually I am trying to export the db (incl. triggers and stored procs) FROM my webhoster. I don't have permission to script the db :-(|||

thomasabcd:

Actually I am trying to export the db (incl. triggers and stored procs) FROM my webhoster. I don't have permission to script the db :-(

If you can see the stored procs and triggers just right click copy all and paste in your Management Studio. Hope this helps.

|||

Isn't there a way to copy the entire db with tables, rows, stored procedures and triggers? It seems cumbersome to e.g. go to every table, open triggers, script each trigger instead of just doing a bulk copy of the entire DB.

tia

|||

If you have access to your MDF and LDF you can use the code in thread below just put the files in the data sub folder in Microsoft SQL Server folder in programs and modify the code below for your database and box. Hope this helps.

http://forums.asp.net/thread/977493.aspx

|||

OR you could open up your query analyser ,set the output to text and then run this:

declare @.objNamesysnamedeclare objCurcursor for select [name]fromsysobjectswhere xtypein ('P','TR')OPEN objCurwhile 1=1begin-- Fetch datafetch objCurinto @.objNameif@.@.fetch_status <> 0break;execsp_helptext @.objNameENDCLOSE objCurDEALLOCATE objCur

The output then contains all the stored procs xtype = P and triggers xtype='TR'.Each objects script will be separated by the text, but I'm sure you can deal with that.

|||

Sounds like you have very limited permission on the hosted database. The best bet is to do just copy and paste all the stored proc and views.

If the data is important, i would pay your hoster for a backup or mdf of your database.

Most hosters nowaday offers free database backup.