We have prepared a piece of software that creates a clone of our production database (SQL Server) that contains only the most recent data. We would like to allow developers to instantly create a development environment on their computers.

The first step of the process involves copying table schemas and predefined data. This step takes around 7 seconds. Since this is a production database, users might be committing transactions during these 7 seconds. We would like to prevent the users of doing so, in order to avoid errors that might arise when creating the foreign key constraints at the next step of the cloning process.

We have examined the possibility of making the database temporarily READ_ONLY, but this would drop all connections to the database. This is not desirable because it causes a lot of side effects. We would like to know if there is a query that puts a write-lock on every table of the database, until we decide to manually release the lock. Ideally, we would like the user to just experience a delay on their request, rather that a connection drop.

We have used database backups in the past. The problem is the database size. At the moment it is 17 GB and constantly growing. Even after compression, We have at least 1GB to transfer through the internet. Our internet connection is slow, and offers about 0.5MB/sec upload speed, and this cannot be overcome. Additionally, we would not like to give away all our data.

  • 1) What frequency are you spinning up new development databases? 2) How frequently are you taking database backups (and what types) of your production database? 3) How up-to-date does the development database need to be relative to the production database? 4) What the heck kind of internet are you guys using only gets 0.5MB/sec upload speed?...my cell phone's network is faster than that. Also, presumably your production and development servers would be on the same network, no?...if so, you should be able to setup much faster intranet throughput regardless of your internet service provider.
    – J.D.
    Dec 16, 2021 at 12:55

3 Answers 3


I would go for backup and restore on the same instance, delete and clean up that restore database and then do a backup of that database. I don't know of a supported way to "lock" the database. One thing you could investigate is to configure snapshot replication and see (through a T-SQL trace) how the snapshot agent does exactly this. Possibly through some undocumented command. - Tibor Karaszi

Have a look at DBCC CLONEDATABASE. It copies statistics but no data, which might make sense with limited bandwidth. - Erik Darling.

If you need a consistent snapshot of the data while changes are being made, you could perform the copy inside a transaction running at the SNAPSHOT isolation level.


Since you can't afford to restore the database on the dev machines, what I would be doing is script out all the database objects and create an empty database on the dev machines. You can easly accomplish that by right clicking on the database on SSMS -> Tasks -> Generate script -> Script entire database and all database object.

Be aware that every time that the production database changes, you would have to do this process. If it's a growing app and in continous development, you should be looking for something that automates this process, but that's a whole another topic.

If it's an old in house application that rarely gets updated and you're part of a small team, you can probably bear with the process of updating manually or generating every time a new script.

Then, once the database is created on the dev machines, I would populate it only with the minimun data required for testing, probably with a SSIS package.

And that's the hard part: if your database has foreign keys, you'll have to build your package with some sort of loading order logic.

Otherwise, you could probably dump, let's say, top 1000 rows from every table and check if the application works properly. Be aware that if you want to avoid blocking in production, you should load data in small batches to avoid lock escalation or set the isolation level to Read Commited Snapshot Isolation (so that readers don't block writers).


Searching around I found a way of aqcuiring a shared lock on a specific table by using the template:


With a little bit of experimantation, I managed to achieve what I wanted by making the following query:

DECLARE @TableLockListQueryPart VARCHAR(MAX)

SELECT  @TableLockListQueryPart = STUFF(( SELECT  ', ' + table_name + ' WITH (TABLOCK,HOLDLOCK) '
                FROM    ( SELECT 
                          FROM      INFORMATION_SCHEMA.TABLES
                        ) x
                XML PATH('')
              ), 1, 2, '') 


SET @query='

SELECT 1 FROM ' + @TableLockListQueryPart + ' WHERE 1=2 

--[Copy Table Queries go here]


Everything works fine but I noticed that by encapsulating the queries inside a transaction, the query execution time doubles. I wonder why this happens and if I can somehow eliminate this overhead.

  • Just don't perform the schema and data extract directly from production on demand. Run it against a restored backup. Or create the development database template overnight and allow developers to copy that. Dec 16, 2021 at 21:58
  • @DavidBrowne-Microsoft We are opting for a CI/CD environment where the developers can check out the most recent database configuration in order to be able to perform testing, before they commit their own commits. Unfortunately, we cannot mock the data layer. Your suggestions, as well as other people's suggested variations on proceeding by using a backup can do the job, but isn't it adding one more layer of complication? Is there a problem on extracting directly from the production database, other than a slight delay on write operations whenever a new checkout takes place?
    – Saloom
    Dec 17, 2021 at 1:10
  • 1
    " Is there a problem on extracting directly from the production database," yes. It's deeply wrong to hit the production database for this purpose. Dec 17, 2021 at 1:18
  • @DavidBrowne-Microsoft What makes it deeply wrong is because (a) we "lock" the tables thus, disturbing the flow in production, (b) some other technical reason or (c) the intention is wrong?
    – Saloom
    Dec 17, 2021 at 1:56
  • 2
    The technical reason is (a) and the fact that requires production access for developers, which has many operational risks. Dec 17, 2021 at 5:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.