I've been reading about Snapshots, and it says you can send small catalogs lets say, in a monthly routine.

I liked the Idea and i will start to study about this, but can I use snapshot for large databases?

Every week I need to backup development database (2tb) and migrate it to test environment. It will be a good idea to make this via snapshot? Performance will not be testes, since it's development, we can wait a lot.


It seems like you've been reading about Snapshot Publications and replication. There are three items in SQL Server with the name snapshot. One is the database snapshot. When a database snapshot is created, one new file per existing data file is created and the database engine will save the original page for any changed page in the database into those files. Database snapshot files are just a collection of pages since the snapshot creation and won't be useful on any other server. Another type of snapshot is a Snapshot Publication, which is part of the replication system. When you create a Snapshot Publication, you specify which articles, which can be tables, views procedures,ect, to include in the publication, specify a subscriber and schedule it for delivery. You can configure it to drop the existing articles at the subscriber and recreate them so they will be replaced every time the snapshot is pushed. It will synchronize all articles between the publisher and subscriber at the time it is run. Which brings be to the third instance of an object named snapshot. Within the replication system there is an agent or executable named the snapshot agent, whose job is to take a snapshot of the articles in the publication at the time it is executed.

If you really need to transfer the entire database from one server to another on a regular basis, you can design a backup system that takes full backups once per week, for example, with differential backups daily and transaction log backups hourly for production. Then transfer the full backup once per week to the test environment and leave it there. After that you would only need to transfer the differential backup daily to restore using the full followed by the differential. I would also advise turning on backup compression to minimize the sizes of the files involved.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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