Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 copy database info from live server to local

Author  Topic 

chedderslam
Posting Yak Master

223 Posts

Posted - 2015-02-07 : 11:36:24
I need to copy my tables, views, and stored procedures from my live server to my new dev machine. When I "script table as", it scripts the table definition, but none of the records.

I also tried "tasks>generate scripts" but it did the same thing, generated definitions, but no records.

I seem to remember being able to script the tables and other objects with the records intact. I'm not sure what I am doing wrong.

How can I generate a script that will have the records that I can run against my local db that will create the tables and other objects with all of the records? This is on a shared host so I do not have full privileges.

I am a web developer so I am not the best with db administration.

I am using Microsoft SQL Management Studio.

Thank you.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-02-07 : 13:48:54
In the tasks -> Generate Scripts dialog, under the Set Scripting options tab, click the advanced button and in "Types of data to script", select Schema and data.

Unless your database is extremely small, I wouldn't recommend the above approach. Instead, backup the database and restore on to the dev machine. When you backup the production database, be sure to check the "Copy Only" option, so you don't interfere with the scheduled backups.
Go to Top of Page

noelson
Starting Member

31 Posts

Posted - 2015-02-09 : 16:38:11
Another option is to use Import and Export Data if the tool is available to you
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-02-10 : 01:46:21
Another option is bcp http://www.sqlserver-dba.com/2014/12/bcp-export-and-import-data-on-sql-server.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -