Recreate local copy of sql server database for server
I have a database that is stored locally on my desktop computer. I’ve made many changes since the original SQL Script and in hindsight I should have kept a record of the changes.
Now I want to recreate this database on a server. Is there any way I can generate a script that will allow me to create the database with it’s contents on the server?
3 Solutions collect form web for “Recreate local copy of sql server database for server”
You could backup the database, copy the .bak file to the server and then restore it there.
If all you need is your local DB on the server, just backup and restore your local DB on the server.
But I’m guessing that you’ve made schema changes and / or static data changes which need to be propogated over the server, since you need to retain interim data on the server.
Schema changes can be found using a tool such as Redgate SQL Compare or VS DBpro, scripted, and run on the server.
Get yourself a SQL compare tool that will replicate the changes on the server.
- AdeptSQL is brilliant, a trial version can be downloaded here: http://www.adeptsql.com/download.htm
- Or a good free tool is OpenDBDiff (http://opendbiff.codeplex.com/releases/view/51995) but it’s not quite as intuitive.
- If you want to replicate data as well, you need a tool that will also do data. The shareware version of SqlDBDiff (30 day trial) will be able to do that: http://www.sqldbtools.com/Downloads.aspx?ProductId=1
- Redgate’s product is talked about a lot, but you have to cough up some $$.
This post might be useful as a reference to other potential tools:
Is there an open source SQL Server DB compare tool?
Or you can create a database backup and restore it on the server, if downtime doesn’t matter. This can cause problems sometimes with logins etc, but it’s not too much hassle.