SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Publish from local to remote server 2012
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

token
Posting Yak Master

United Kingdom
133 Posts

Posted - 10/21/2012 :  19:12:37  Show Profile  Reply with Quote
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

5155 Posts

Posted - 10/21/2012 :  20:46:28  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/21/2012 :  20:49:20  Show Profile  Reply with Quote
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/

Go to Top of Page

token
Posting Yak Master

United Kingdom
133 Posts

Posted - 10/22/2012 :  06:50:34  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/22/2012 :  07:31:21  Show Profile  Reply with Quote
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.
Go to Top of Page

token
Posting Yak Master

United Kingdom
133 Posts

Posted - 10/22/2012 :  07:41:56  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/22/2012 :  09:56:31  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/22/2012 :  12:28:36  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000