Truncated copy of a DB for a developer I have an access to a DB (SQL Server 2008 R2). 
The size of the DB is about 40GB.
I am a developer and I'd like to have a local copy of that DB to be able to work offline.

Is there an easy way to get truncated version of that DB (all db objects and about 1000rows per table)? 

I have a backup of that DB on a test server, but I am not able to copy 40GB over my slow connection. 
I don't have permission to restore that backup and delete rows manually and backup again after.

I probably can script schema by Task -> Generate scripts, but how to script data and limit it to ~1000 rows per table(taking into account the fact that there are about a hundred tables and bounded through FK rows should remain safe without loosing a row at one end)
Couple of options :

A. Native to sql server (no third party):





-- make sure to have it in order due to Foreign keys + if your tables have Identity columns then Identity insert should be ON + you have to order by date to get matching data from all the tables.

select 'select top(100) * from '+ name +char(10)+ 'go' from sysobjects 
where type = 'U' -- for user tables


Option 2: BCP OUT and BULK Insert.

See my script at https://dba.stackexchange.com/a/43232/8783

Option 3: SSIS wherein you can specify top rows to be extracted from the source database using T-SQL Script task. Sill here you have to manage FK's , Identity columns, etc.

Option 4: Use third party or tools from codeplex (all free)


SSMS Tool pack. Very good and freebie (till SQL Server 2008R2) and it has Insert statement generator
DBScripter
Sql Script Generator



  Licensing only applies to SSMS 2012 and higher versions.
  For previous SSMS versions the SSMS Tools Pack is still FREE.