Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How do we "export" a complete Microsoft SQL server database from one server to a "remote" server (including all the "stored procedures" and "triggers"). The export facility provided in the management studio seem to only export the tables and nothing else.

EXTRA NOTE:

Many thanks guys for your kind and detailed response. To be more specific I'm trying to upload a local database that I created along with my .NET application to my web hosting server which I have limited access to. I can only connect to my database through SQL Management Studio and there is no cPanel or Plesk type configuration that would allow me to perform a backup/restore operation or anything fancy. I did as Kin suggested and generated a script and ran it on the remote database. The following is the list of errors generated which seem to be a permission issues. Is there any way I could elevate the security of my user account to bypass these errors on my side. OR are there any other way of performing this export?

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'CONTAINMENT'.
Msg 15247, Level 16, State 1, Line 1
User does not have permission to perform this action.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 15247, Level 16, State 11, Line 1
User does not have permission to perform this action.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 15247, Level 16, State 1, Line 1
User does not have permission to perform this action.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 102, Level 15, State 6, Line 1
Incorrect syntax near 'FILESTREAM'.
Msg 102, Level 15, State 6, Line 1
Incorrect syntax near 'TARGET_RECOVERY_TIME'.
share|improve this question
2  
If you have ability to take backup and restore database that will be quickest method. Or you have use method mentioned by @Kin below. But in any case, you will have to deal with logins and database users as they will not get exported by any of methods. –  JackLock May 8 '13 at 16:54

2 Answers 2

How do we "export" a complete Microsoft SQL server database from one server to a "remote" server (including all the "stored procedures" and "triggers").

Right click database --> tasks --> Generate scripts

The export facility provided in the management studio seem to only export the tables and nothing else.

Incorrect. You can infact script the entire database including data as well (though I would not recommend scripting data -- as the script wont be able to run using SSMS or sqlcmd, etc).

enter image description here

enter image description here

enter image description here

share|improve this answer
    
Many thank Kin for your visual and thorough answer. I have updated the question. Please have a look and comment. –  Sam May 9 '13 at 2:50
    
@Sam there are 2 types of errors you have encountered. Check the user permission and make sure it has alter database permission -- "User does not have permission to perform this action.ALTER DATABASE statement failed". Also, check the script as it has syntax errors near CONTAINMENT, FILESTREAM and TARGET_RECOVERY_TIME tables. –  Kin May 9 '13 at 13:19

Although you could use SSMS's Generate Script option, there are a few things that feature is lacking.

E.g., Non-Clustered Indexes on the table, constraints, permissions that are specific to a table, etc.

I use Red Gate's SQL Compare to take care of all that. For each table, it would include all "CREATE NON-CLUSTERED INDEX" commands and GRANT/ALTER permissions for them, etc.

You could create a Script Folder (or sync) in SQL Compare and totally rebuild the DB structure on the other side.

share|improve this answer
    
Thanks dng, but Red Gate seem to be a very pricy software which I can't afford at this stage. –  Sam May 9 '13 at 2:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.