| Author |
 Topic  |
|
|
token
Posting Yak Master
United Kingdom
129 Posts |
 Posted - 10/21/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/21/2012 : 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/918992
If 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
India
48012 Posts |
Posted - 10/21/2012 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
token
Posting Yak Master
United Kingdom
129 Posts |
Posted - 10/22/2012 : 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. |
Edited by - token on 10/22/2012 06:58:37 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/22/2012 : 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
United Kingdom
129 Posts |
Posted - 10/22/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/22/2012 : 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
India
48012 Posts |
Posted - 10/22/2012 : 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 option
but 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 MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Publish from local to remote server 2012
