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.
2
-
Backup the live database, restore it to a local instance and update your connection string to use the local instance.drneel– drneel2016-02-04 17:37:42 +00:00Commented Feb 4, 2016 at 17:37
-
sometimes if the scope of you program permits you can just copy the tables to a local empty DB.Dave Kelly– Dave Kelly2016-02-04 17:42:09 +00:00Commented Feb 4, 2016 at 17:42
Add a comment
|
2 Answers
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
- 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
- Add a New database in your local server to receive the data (don't over think this step).
B. Migrate the Schema
- Right-click the source (production) database and click Schema Compare...
- From the SQlSchemaCompare tab that opens, use the Select Target dropdown to select your local database as the target.
- From the SQlSchemaCompare tab, click Compare.
- 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
- Right-click the source (production) database and click Data Comparison...
- Follow through the prompts to select the Tables to migrate then click Finish.
- 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.
Comments
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.
2 Comments
echoBr00tal
i have visual studio, i have sql server... this doesnt seem helpful
N West
It answers your question. You want to run the database locally. Visual Studio creates a Sql Express database instance and lets you connect directly to it. All you need to do is import the database into the project and deploy to your local machine. Or you install SQL Server on your desktop and configure it, then take a backup of the live DB and restore it to your DB. As long as you are using developer edition on your PC...