Last post Sep 09, 2008 05:27 PM by ]osh42O
Sep 09, 2008 01:56 PM|]osh42O|LINK
In our production usage, we have SQL Server 2005 professional edition. We also have a separate test server setup just for the testing of dangerous SQL stuff. On test server, I've installed SQL 2005 Express edition. I am trying to copy databases from production
server to test server for testing purposes, and it is not working.
Copy database functionality tells me that SQL Agent is not installed with express version. I figure I cannot fix this.
Generate script works well to create the tables, constraints, keys, views, etc. onto the test server. However, when I try to export the data, it removes my primary keys!
I suppose I can write my own code to do this using SqlBulkCopy class, but I would like to take advantage of GUI features. How can I resolve this issue?
Thank you for reading and for helping me with my concerns.
Sep 09, 2008 02:40 PM|jeremyh|LINK
SQL Express does not support SQL Agent.
Copy Database Wizard can not be used towards to a SQL Server Express Edition.
I always use a script more info here
Or you can just backup and restore to the sql express.
But if you want another way try
Hope it helps
Sep 09, 2008 02:48 PM|Naom|LINK
The way we copied the production data to local server (not express, but I think the algorithm is the same) is like this.
1. Run a script to only create database (not tables and SP) (only empty shell database)
2. Create a backup of the production database
3. Use restore option on local SQL Server with the overwrite option checked. That's it.
I also found in the past scripts to create inserts with data, but for huge tables it's not feasible.
Sep 09, 2008 02:54 PM|]osh42O|LINK
Thanks for replies. I've tried the backup and restore but it says "The backup set holds a backup of a database different than existing database". I don't understand this.
I've used the script as suggested in Pinal Dave's blog post to create the tables and stuff, but then when I do export data wizard the primary keys are removed.
That open-source agent on CodeProject is used primarily for job scheduling. I need on-demand DB copies.
It appears I will have to write my own code...
Sep 09, 2008 03:05 PM|jeremyh|LINK
When you run the restore did you check the allow overwrite option?
Sep 09, 2008 03:06 PM|Naom|LINK
Delete your database in local SQL Express. Create a script for the database on your production server. Then run it on your local server to create database exactly the same as in production. Then backup/restore should work.
Sep 09, 2008 03:09 PM|]osh42O|LINK
I have found the option to allow overwrites and it's working.
Sep 09, 2008 03:24 PM|Naom|LINK
That's exactly what I suggested in my first reply.
Sep 09, 2008 05:11 PM|]osh42O|LINK
That's exactly what Jeremy said in his reply, 8 minutes before you. What's the problem? I said thank you for the help, want me to send you money or something?
Sep 09, 2008 05:20 PM|Naom|LINK
I meant that you could have marked my first reply as answer as well since it described exactly the algorithm mentioning this option. But no offense if you don't, no biggie.
Sep 09, 2008 05:27 PM|]osh42O|LINK
Oh yes, sure I can mark as answer. Sorry, I thought I already have done that here.