I've had a big problem in replicating a simple SQL Server 2008 R2 Express database for use on a development server. I thought I had it sorted but it turns out that each table has lost it's 'Identity' value somewhere along the line, and it's not possible to add those back in now. This is pretty much useless. So I'm back at square 1; having to get a copy of a MSSQL database plus data from a web server to another web server.
I've read that SQL Server Publishing Wizard does this, and maintains crucial things like identity settings etc. Trouble is, I'm working with SQL Server 2008 R2 Express and I can't actually seem to find a way to access that program anywhere - even though when I go to 'control panel > remove programs' it's in there. When I try to find it on my system (e.g. via start > find programs / files) it's nowhere.
Does anyone know how to access this program, and will it do what I need?
Thanks!
Sure thing, thanks Michael. So the solution was to connect to the database through VWD 2010 Express, which has the options required to do this. There are actually some really great third party tools which do database migrations from one system to another detailed here: http://erikej.blogspot.co.uk/2009/04/sql-compact-3rd-party-tools.html. The ones on this page are geared specifically at SQLCE migrations, but several of the tools also support other full SQL versions too.