Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [snapshot]

The processes of creating a point in time copy of data.

Filter by
Sorted by
Tagged with
64 votes
8 answers
45k views

Is it possible to quickly create/restore database snapshots with PostgreSQL?

First of all, I'm a developer, not a DBA or sysadmin; please be gentle :) I'm working on an application workflow where a single user action will trigger complex changes in the database - creating ...
Zilk's user avatar
  • 1,121
16 votes
1 answer
20k views

How can I query for existing database snapshots in SQL server?

I'm trying to create a t-sql query that can determine whether or not a given database has any database snapshots that were created from it. For example, if I were to create a snapshot like this: ...
Jesse Taber's user avatar
15 votes
5 answers
2k views

Backing up & restoring 10-20 SQL Server databases to a ~synchronous state?

I need to backup up 10-20 SQL Server 2008 R2 databases with sizes between 10-50 GB, while they are online and used simultaneously by a single enterprise app. I also need to restore them to a state ...
bogdan's user avatar
  • 171
14 votes
2 answers
4k views

SQL Server database snapshots for integration testing

I am trying to define a way of working with a test database (in SQL Server) for our integration testing. My idea was to do these steps at the launch of the integration test assembly: create a ...
marc_s's user avatar
  • 8,952
12 votes
1 answer
6k views

Remote queries at SNAPSHOT isolation level

I'm experimenting with snapshot isolation level. Whenever I try to access a linked server, I get an error: Remote access is not supported for transaction isolation level "SNAPSHOT" I wasn't able ...
VladV's user avatar
  • 223
12 votes
1 answer
5k views

Does SQL Server Snapshot replication completely copy the data every time or does it issue deltas?

I am looking at snapshot replication between two servers. Here is what I have: 500GB database ~500MB nightly bcp loads ~50MB daily transactions I was asking other DBAs in the company about which ...
grid's user avatar
  • 306
9 votes
1 answer
5k views

EC2 - How to correctly back up PostgreSQL data?

Here is the setup: 1 small Amazon Linux (EBS-backed) EC2 instance with 3 additional volumes. This is both a web server and database server. One volume for code, one for the PostgreSQL (8.4) data ...
user avatar
8 votes
2 answers
2k views

Are SQL Server Database writes slower with Snapshot Isolation?

I have a lot of deadlocks going on in my system. I would like to use Snapshot Isolation to fix them, but my DBA has reserves about it. One of his concerns is that Snapshot Isolation slows down ...
Vaccano's user avatar
  • 2,496
8 votes
2 answers
2k views

Advantage of using a database snapshot for reporting purposes

What is the performance advantage of using a snapshot of a database for reporting purposes? The way I see it, it would probably downgrade performance since for every write in the original database, ...
ivanmp's user avatar
  • 2,323
7 votes
1 answer
5k views

What is the proper way to back up MySQL database with rsnapshot?

I want to back up my website that contains both user-uploaded images and data stored in MySQL databases such that they are always consistent with each other at any particular point of time. While ...
Question Overflow's user avatar
7 votes
3 answers
3k views

What methods are available to create a snapshot of an oracle database for testing purposes?

I am new to databases and have just been assigned to work with Oracle. We want to create a snapshot of the database for testing purposes that will allow us to revert back once we're done. I'm a bit ...
Megan's user avatar
  • 95
7 votes
2 answers
2k views

Sql server snapshot isolation overhead when read_committed_snapshot is enabled

When SQL Server's read committed snapshot is on (and thus some amount of row versioning is necessary), is there any additional overhead caused merely by enabling snapshot isolation? Snapshot ...
Eamon Nerbonne's user avatar
6 votes
3 answers
1k views

SQL Server 2008 mirrored Database with manual failover, need reporting database

Our current setup is a principal database used in production with a manual failover mirrored database for HA. I need to setup a reporting database as well. What would be the best approach? Shall I ...
davey's user avatar
  • 689
6 votes
3 answers
3k views

Using SQL Snapshot Replication to refresh TEST databases from PRODUCTION. Why not?

What are the drawbacks and downsides? We want to schedule automatic nightly refreshes of 20 databases, approximately 1GB each, from the PRODUCTION server to the TEST server. We're setting up SQL ...
Lawrence P. Kelley's user avatar
6 votes
1 answer
3k views

Should snapshot agent continue to run in transactional replication?

We have transactional replication running for a large number of publications on sql server 2008 R2 2 node active/active cluster. I have noticed that the snapshot agent job runs hourly and it looks ...
Johnny Barnes's user avatar
6 votes
2 answers
45k views

Why is my database stuck in the Restoring state?

I have some unit tests that manipulates data in a database. In order to guarantee that the database state is always the same throughout all the tests, we're trying restore a database snapshot at the ...
andrerpena's user avatar
6 votes
2 answers
3k views

Fast backup & restore of MySQL database to revert recent changes

I have a decently big MySQL DB (InnoDB) with size about 400 MB. Thus dumping the whole DB with mysqldump and restoring it back takes forever (more than 15 minutes, which is unacceptable). For ...
knaperek's user avatar
  • 165
6 votes
1 answer
383 views

Deploying and rolling back changes in an Availability Group database

I'm looking for a solution or workflow. Here's how I used to do database deploys when using a stand-alone server: Create a snapshot of the production database Deploy changes Verify that the deploy ...
Daniel Hutmacher's user avatar
5 votes
3 answers
2k views

Can PostgreSQL support integration test with some kind of throwaway overlay?

It's a common problem to write integration tests that include a database. If the test changes the database then it could effect other tests or the next run of itself. I know that I could wrap my test ...
Thomas Koch's user avatar
5 votes
1 answer
6k views

How can I determine the date of a snapshot in SQL Server 2008r2?

I am working on implementing an ETL process to load data into a warehouse. Part of this process is pulling data from our production server (Server P) to a staging server where the actual load into ...
JNK's user avatar
  • 18k
5 votes
1 answer
572 views

Database ID of a non-existent database - time-out waiting for buffer latch on page appearing in error log

Background: I've recently started at a new shop with lots of databases in various states. I arrived this morning (having just been added to the SQL DBA mailing group) to find an email about the ...
Ian_H's user avatar
  • 1,656
5 votes
1 answer
3k views

DB snapshot goes into SUSPECT mode (SQL 2014 on WIN2K8R2) [closed]

I was hoping someone here might have encountered this sort of issue and if so what did you do to fix it? Here's some details: DB server: SQL 2014 Enterprise (12.0.4100.1) OS: Windows 2008R2 ...
C-Four's user avatar
  • 59
4 votes
3 answers
869 views

SQL Server - Snapshot is useful in production environments?

I'm reading about SQL Server high availability solutions and disaster recovery and among the available resources the SQL Server have snapshot feature. In theory all seems like beautiful. I also read ...
Leonardo Lacerda's user avatar
4 votes
3 answers
6k views

How to undo every change made to a database in a controlled environment?

I'm doing system testing of a product, and several tests are severely changing the data of a demo database. The data is quite complex to create queries which will undo the changes done by the tests; ...
Arseni Mourzenko's user avatar
4 votes
2 answers
2k views

Why database snapshot creation is faster than normal database backups?

Could you tell me any reason why creation of database snapshot is faster than database normal database backups in MS SQL server
S. Fernando's user avatar
4 votes
1 answer
683 views

Oracle Materialized View

I need your help with a issue on Oracle Materialized View. I created the Materialized View using the following command: CREATE MATERIALIZED VIEW SNAP_TEST TABLESPACE tbs NOCACHE LOGGING NOPARALLEL ...
Gianluca Frezza's user avatar
4 votes
1 answer
260 views

MySQL replication setup based on an LVM snapshot – am I forgetting anything?

I'm tasked with starting a brand new replica of a client's MySQL 5.7.35 database server. While I've observed others doing this in the past in various circumstances, I haven't been in charge of this ...
JK Laiho's user avatar
  • 145
4 votes
1 answer
301 views

Advice on setting up a Reporting server

I'd like to ask advice on setting up a reporting server please. We have a SQL Server (on a VM) that handles operations. (SQLOps) This primary SQL server instance has a secondary Log Shipping ...
Peter PitLock's user avatar
4 votes
1 answer
2k views

Why are Stored Procedure schema changes not being replicated when using ”initialize with backup”?

In SQL Server I have set up transactional replication using the "initialize with backup" option. Data is being replicated fine, as are schema changes to tables. However, schema changes to ...
David A Gibson's user avatar
3 votes
2 answers
5k views

Excessive replication snapshot size causing failure

I am searching for an explanation of a replication snapshot being in excess of 5 times what we're predicting Following a replication failure we are attempting to reinitialise subscriptions with a new ...
reticentKoala's user avatar
3 votes
1 answer
1k views

What is the difference between database snapshot and VSS database snapshot backup?

I have database backups configured via SAN disk tool. In the backupset table I can see the VSS writer service is taking full backups of type snapshot. There are 85 databases on the server. I know ...
variable's user avatar
  • 3,636
3 votes
2 answers
8k views

What is the best way to automatically create a snapshot of the table at the certain time?

I have running and quite loaded PostgreSQL server. I need to take snapshots of the certain tables at the certain time (midnight). Those tables are pretty loaded (a lot of updates and inserts). What ...
Worker's user avatar
  • 519
3 votes
1 answer
500 views

Get a listing of snapshot data files

I need to programmatically drop a database with all of its snapshots and then recreate the database and create a new snapshot. I'm having trouble locating where the snapshot files are located from ...
Lumpy's user avatar
  • 2,109
3 votes
3 answers
10k views

Replication Access denied in Replication

I have a database on-prem, and I have an Azure account. I need to establish transactional replication from the on-prem to Azure. First of all I created a publication and after that I made a ...
Loai's user avatar
  • 131
3 votes
1 answer
114 views

SQL Server 2016 SP1 + strategy for long term version retention (snapshots, temporal tables, other?)

We have a data mart fed from an upstream data warehouse that we generate reports out of for external parties. I have a business requirement to preserve the state of the database for each month end to ...
cmcapellan's user avatar
3 votes
1 answer
481 views

SQL Snapshot Redirect

Company is implementing Database Mirroring Asynchronous with Snapshot. This provides some Disaster Recovery with Reporting area. Server A Principal contains database which mirrors to Server B, ...
user avatar
3 votes
1 answer
183 views

How widespread is the adoption and usage of database snapshots?

After reading up on database snapshots, it seems that they are useful as a backup and quick restore method for small DDL and DML changes. Additionally they are useful for large scale dev/test ...
Alan Whited's user avatar
3 votes
3 answers
3k views

Unable to fully drop a database snapshot

I have a job that drops and re-creates a snapshot, it's currently failing when trying to create the database as it says the database already exists. The database however does not have an entry in ...
Sam Peacey's user avatar
3 votes
2 answers
504 views

Online index rebuild snapshot location and impact of filling index drive

My understanding of a rebuild index online operation is that the index will have a snapshot taken and the rebuild is started on the snapshot index. My question is where does the snapshot index ...
hpk89's user avatar
  • 344
3 votes
1 answer
106 views

Questions Concerning Database Snapshots

One of our divisions is running SQL Server 2012 SP1 Enterprise edition for no particular reason (I know, I know... the decision was made before my time and they are resistant to change). What I'm ...
SQL_Deadwood's user avatar
3 votes
1 answer
10k views

How to generate replication snapshot without locking tables

When I reinitialize subscription and generate new snapshot it locks all tables and makes the database unusable for a very long time (never waited it out). Is there anyway to do this without locking?
Kenny Johansen's user avatar
3 votes
1 answer
840 views

Microsoft SQL Server - Permission to create a Snapshot

I've developed a set of SSIS packages to load a data warehouse. In order to manage any issue that may arise during package execution, each package starts by creating a snapshot of the data warehouse. ...
s_allegrini's user avatar
3 votes
1 answer
655 views

SQL Server secondary replica snapshot with Basic Availability Group

I have the following scenario: I created an HA group of two nodes in SQL Server 2016 Standard (which is limited to two nodes, one database per group and a non readable secondary replica). I tried to ...
olimpiu rapan's user avatar
3 votes
1 answer
546 views

SQL Server Snapshot Replication and SRDF compatability

Is there anything that breaks down if you use snapshot replication and then use Symmetrix Remote Data Facility (SRDF) from EMC Corporation for disaster recovery (DR) on the snapshot publisher. I'm ...
grid's user avatar
  • 306
3 votes
3 answers
4k views

Snapshot not picking up new articles in SQL Server 2012 transaction replication when added through script

I am trying to add new articles to an existing publication in SQL Server 2012. I am adding the article to the publication with the syntax --add the table into the correct publication sp_addarticle @...
DamagedGoods's user avatar
  • 2,591
2 votes
3 answers
2k views

Where do Database Snapshots store the information what pages have been copied to the snapshot files?

Database snapshot files are sparse files. Only when a page is modified the sparse file is being written to. Reads of the snapshot must be able to know whether the sparse file contains a copied page or ...
boot4life's user avatar
  • 1,279
2 votes
3 answers
3k views

In what situation can snapshot restore be blocked after SET SINGLE_USER?

In my tests, I have the following script: USE master; ALTER DATABASE [TestDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE RESTORE DATABASE [TestDB] FROM DATABASE_SNAPSHOT = 'TestDBSnapshot' In some case ...
Andrey Shchekin's user avatar
2 votes
2 answers
192 views

SQL Server replication methodology

I have a relatively large production environment (150GB) in SQL Server, and I need to duplicate it somewhere else for querying (specifically: I need to create an environment identical to the ...
LearnByReading's user avatar
2 votes
1 answer
627 views

Would using timestamps solve consistency issues with snapshot isolation?

I'm considering using Snapshot Isolation for some large queries being send from an Access 2007 frontend. These queries are occasionally causing ASYNC_NETWORK_IO waits, and some blocking as well. I'm ...
Jeff Rosenberg's user avatar
2 votes
1 answer
333 views

Can we access snapshot database of a log shipping database while we are restoring log files

I have a question regarding snapshot database of a log shipping database. I have a database name like Northwind on server A and I am shipping it to server B. On server B, the log shipped copy of the ...
info.sqldba's user avatar