| Author |
Topic |
|
token
Posting Yak Master
133 Posts |
 Posted - 2012-10-21 : 19:12:37
|
| I am sorry if my question is obvious but I have searched Google and not found an answer.I have SQL Server 2012 developer on my local machine. I want to publish my database to a server which has SQL Server 2012 Enterprise running. Whats the best way to do this? I have multiple databases with numerous tables that need to be exported. My initial idea was to just do a backup of my local databases and a restore on the remote, but this is probably not good practice.Any suggestions please I would be very thankful! |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-21 : 20:46:28
|
| The usual practice is to backup from the development/UAT server and restore on to the production server when you do this initially.If you have logins and users that you want to port from one server to another, have a look at this page: http://support.microsoft.com/kb/918992If you are making changes and want to apply the changes from UAT to production, there are commercially available source control tools (Red Gate Source Control for example) that can assist in doing this. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-21 : 20:49:20
|
| you can do backup restore or attach detach depending on whether you want to move db or make a copy of it------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
token
Posting Yak Master
133 Posts |
Posted - 2012-10-22 : 06:50:34
|
| So I've figured it out....You should right click on the database, go to Tasks then Generate Scripts. Choose what you want to script and under options go to Advanced and change the setting in the 'Types of Data to Script' column to 'Schema and Data'.Done! Run the code on the remote server. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-22 : 07:31:21
|
| That would copy only the schema and not data. But that is probably what you want.The other issue with that approach is that if the objects are not created in the correct order the creation can fail. There was a thread on SQL Team a while ago that discussed this issue at length with some examples, but I don't seem to be able to find it at the moment. |
|
|
|
token
Posting Yak Master
133 Posts |
Posted - 2012-10-22 : 07:41:56
|
quote:
Originally posted by sunitabeck
That would copy only the schema and not data. But that is probably what you want.The other issue with that approach is that if the objects are not created in the correct order the creation can fail. There was a thread on SQL Team a while ago that discussed this issue at length with some examples, but I don't seem to be able to find it at the moment.
No it copies the data as well. You change the setting under Advanced option to generate script for the SCHEMA AND DATA. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-22 : 09:56:31
|
| Nice! I didn't know that.The thing I noticed is that, it is generating one insert statement for each row in a table. So if you have lot of data, it may be slow. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-22 : 12:28:36
|
quote:
Originally posted by sunitabeck
Nice! I didn't know that.The thing I noticed is that, it is generating one insert statement for each row in a table. So if you have lot of data, it may be slow.
it does if you choose script data optionbut it would be again separate inserts as you stated and would be slow for large datasets (sometines SSMS itself blows up giving out of memory exception)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Publish from local to remote server 2012