CODEWITHSUNDEEP
sqlsql-servervisual-studio
echoBr00tal
echoBr00tal

Reputation: 67

Creating a local database from server database in visual studio

I have a rather large database I am working with and I am about ready to break something. To prevent this affecting live data, how would I use the live database to setup a local database? Not sure if this is even possible but I do know you can setup a local db.

Upvotes: 0

Views: 6070

Answers (2)

Design.Garden
Design.Garden

Reputation: 4207

Answer

Use Visual Studio's Data Comparison tool to synchronize data to your target database from your source after you've created the database (schema only, no data) in your local database server.

Steps

From the Visual Studio's SQL Server Object Explorer:

A. Create the local database

  1. Add two SQL Server Objects: One that connects to your production server and one that connects to a local (development/testing) server. If you need help setting up a local server then take a look at SQL Server LocalDB
  2. Add a New database in your local server to receive the data (don't over think this step).

B. Migrate the Schema

  1. Right-click the source (production) database and click Schema Compare...
  2. From the SQlSchemaCompare tab that opens, use the Select Target dropdown to select your local database as the target.
  3. From the SQlSchemaCompare tab, click Compare.
  4. Uncheck everything in the comparison results except for the Tables, Views, and Procedures (unless you know what you're doing) then click Update.

C. Migrate the Data

  1. Right-click the source (production) database and click Data Comparison...
  2. Follow through the prompts to select the Tables to migrate then click Finish.
  3. From the SQlDataCompare tab that opens, review the comparison results (it should make sense to you) then click Update Target

That's it! Either your local database is ready with data, or you confused your target/source and wiped out all of your data in production. Either way, you're done for the day.

Upvotes: 2

N West
N West

Reputation: 6819

You can create a SQL Server Data Tools database project type, then right click the project file and do an "Import..." to import the database to your local machine. Then you can deploy the local DB and it will be available in the SQL Server Object Explorer locally. This way you don't have to install SQL server on your machine - everything's in Visual Studio. Hopefully you are developing with a small set of data locally.

Upvotes: 1

Related Questions