CODEWITHSUNDEEP
sqlsql-serverdatabase
Bash
Bash

Reputation: 1

Copy SQL Server database for development but smaller

We have production database above 100Gig. I want to duplicate this database and give it to every developer to test its code, but the size is too large. Is there anyway I can backup just top 1000 rows with FK's and restore it to new DB? Or duplicate the DB first and delete all records from all tables, but keep 1000 rows with FK's or any other way to keep size below 5Gig.

I did search, but none of solutions were for tables having foreign keys.

Thanks, Basheer

Upvotes: 0

Views: 1210

Answers (2)

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4211

This the IDEA:

First:

Create new database:

Second: Select small records only like:

select top 500 from allYourTables
then insert to each every table to your new Database Created.

Third:

Dump the new database and give to its every developer

Hope it helps:

Assuming that you have a new db_to_dev Database name and you are working to your current database: This procedure will insert all the data from your working database, making sure that you had already a database created. db_to_dev:

Using Information_Schema you can select all your tables:

CREATE PROCEDURE PROC_TRANSFER_DATA @NUM_OF_RECORDS nvarchar(255)  as
BEGIN
SET NOCOUNT ON;  
DECLARE @message varchar(80), @tablename nvarchar(50);  
Declare @sqlstmt nvarchar(255);
PRINT '-------- List of tables --------';  
    DECLARE Schema_cursor CURSOR FOR   
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 

    OPEN Schema_cursor  
    FETCH NEXT FROM Schema_cursor INTO @tablename 

    IF @@FETCH_STATUS <> 0   
        PRINT '         <<None>>'       
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
        SELECT @message = '         ' + @tablename  
        set @sqlstmt =   'select top ' + @NUM_OF_RECORDS + ' * into [db_to_dev].[dbo].['+ @tablename +'] from ' + @tablename;
        EXEC  sp_executesql  @sqlstmt
        PRINT @message  
        FETCH NEXT FROM Schema_cursor INTO @tablename  
        END  

    CLOSE Schema_cursor  
    DEALLOCATE Schema_cursor  

END

To use:

With an option parameter:

EXEC PROC_TRANSFER_DATA '500'

Parameter value is depend on you if how many records you want to transfer into your new database db_to_dev.

This Stored Proc is tested.

Good luck

Upvotes: 1

Bryan Newman
Bryan Newman

Reputation: 631

There are a number of projects on github which seek to do exactly that: make a subset that preserves referential integrity. Here is one such project:

https://github.com/18F/rdbms-subsetter

Upvotes: 0

Related Questions