Partial import of Sql server 2005 database for local developement

Question

So at work, my team is using a central SQL server 2005 database server for integration testing and I want to move to testing on my local database. The only problem is that the central database is in excess of 10 Gb.

I am interested in importing the data objects and a rows for each table to ensure I can be up and running. Can you guys advise me on how I can do this?

I have VS 2008 professional and SQL Server management studio express. Would I need VS 2008 database edition?

Thanks -Venu

EDIT : I will try logging into the central database server and seeing if the host machine has better tools. Thanks kpollock, I didn't think of that.

Answer 1

Looks like an SSIS job to me - which I think Express version of Management Studio doesn't have.

Or you could do it in C# calling Stored procs on the source database to extract the data if that's where your skills lie. (this is how I would do it as I think it's more widely comprehensible - but that's just my opinion).

If the database is live and new data comes in all the time perhaps you could set up the schema and reference data only then use replication to get the new data as it comes in (if you think that will get you a representative sample).

You will have to code up the sample extraction based upon an understanding of the schema - i.e. ensure you get a full set of linked records (e.g detail records for orders and all lookups e.g. customer, addresses, etc.).

Answer 2

Both 2005 and 2008 Express editions have 4 gb limitation.

Have you thought about making a db copy on central server and dealing with it (just restore it into new db with other name)?

See also BACKUP and RESTORE in SQL Server -- Full Backups