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

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

Use Stellar, it's like git for databases: Stellar allows you to quickly restore database when you are e.g. writing database migrations, switching branches or messing with SQL. PostgreSQL and ...
David Portabella's user avatar
12 votes
Accepted

SQL Server database snapshots for integration testing

Unfortunately, it's by design. Taken from BOL page "Revert a Database to a Database Snapshot": Limitations and Restrictions Reverting is unsupported under the following conditions: ...
spaghettidba's user avatar
  • 11.3k
9 votes
Accepted

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

Answering Your 1st question The database snapshot and VSS database snapshot backup (via VSS writer service) is the same concept? No, they are different concepts. Database Snapshots The Database ...
John K. N.'s user avatar
  • 17.9k
7 votes
Accepted

Get a listing of snapshot data files

You can identify database snapshots in sys.databases by the column source_database_id being not null. Join it to sys.master_files and you're done: SELECT mf.database_id, dbs.source_database_id, ...
spaghettidba's user avatar
  • 11.3k
6 votes

SQL Server - Snapshot is useful in production environments?

As an opinion based question and answer, I suspect that this will get closed, but here's my two cents. I have used and seen Database Snapshots in my production environments, although rarely. ...
Jonathan Fite's user avatar
5 votes
Accepted

SQL Snapshot Redirect

One approach is to create a database containing only synonyms referencing the objects in the snapshot you need for reporting. Clients can then connect to the synonym database and run queries just as ...
Dan Guzman's user avatar
  • 28.3k
5 votes
Accepted

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

Since you're dealing with a single database (or a known set of databases), simply create a SQL Server Agent Job that restores the month end backup to a "month-end" database. Schedule the job to occur ...
Hannah Vernon's user avatar
  • 70.1k
5 votes
Accepted

SQL Server - Snapshot is useful in production environments?

[...] Perhaps the biggest hindrance to adoption is that Management Studio didn't offer support[...] By this he meant that you do not have feature in SSMS to create snapshot of database, you have to ...
Shanky's user avatar
  • 19k
5 votes

Starting SQL server Database in forced recovery mode

Not possible. Recovery mode means that redo was performed but not undo i.e., you need a prior restore operation. A snapshot backup can be used as a base for restoring log backups (Veeam does it) but I ...
Tibor Karaszi's user avatar
5 votes
Accepted

Difference between Backup and Snapshot

You have to be very specific about the terminology used. SQL Server Database Backups Backing up your SQL Server database is essential for protecting your data. This discussion covers backup types, ...
John K. N.'s user avatar
  • 17.9k
4 votes

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

Following the TEMPLATE answer by Peter, these are the commands I use: List databases: psql -h localhost -l Copy db: createdb -T <db_to_be_cloned> <clone_name> Delete db: dropdb <...
Noam Nol's user avatar
  • 141
4 votes

how can database snapshot to be used to recover data?

But my problem is how can database snapshot to be used to recover data? You have two options here. You can revert your existing database back to the point of the snapshot using this command: ...
Kenneth Fisher's user avatar
4 votes
Accepted

how can database snapshot to be used to recover data?

how can database snapshot to be used to recover data? Yes, you have to use restore database ... FROM DATABASE_SNAPSHOT. It is called reverting the database to a database snapshot. You would use it to ...
Kin Shah's user avatar
  • 62.3k
4 votes
Accepted

Why database snapshot creation is faster than normal database backups?

Mostly because a snapshot isn't really a backup. As explained in How database snapshots work The snapshot uses one or more sparse files. Initially, a sparse file is an essentially empty file ...
Kenneth Fisher's user avatar
4 votes

SQL Server database snapshots for integration testing

Another perspective will be to take backups and restore them - since you are just creating empty database and schema with some lookup values. Also, just inserting 50K rows, the database wont be that ...
Kin Shah's user avatar
  • 62.3k
4 votes

Can Sql Server Backups be replaced by a netapp SnapManager?

Not at all. A SAN snapshot is a bitwise copy of raw disk state. If and only if all the responsibility for business continuity is on SAN storage team, this might be acceptable. For most of the business ...
vonPryz's user avatar
  • 3,081
3 votes
Accepted

Questions Concerning Database Snapshots

When you create a database snapshot, it logs a message in the Error Log that says 'starting up database...' and the database name. You could filter the error log on those messages. CREATE DATABASE ...
Erik Darling's user avatar
  • 41.2k
3 votes
Accepted

EXPDP snapshot too old live monitoring

You need to fully understand how undo works to troubleshoot this error. I recommend this blog post: http://blog.oracle48.nl/wordpress/oracle-database-undo-space-explained/ You can use this query to ...
Andrew Brennan's user avatar
3 votes

Backup standby database using LVM snapshot

To do that on the standby server you need to configure it like a master server to enable the cascading replication[1]. To enable it, you need to change the follow parameters: wal_level to ...
Sebastian Webber's user avatar
3 votes

how can database snapshot to be used to recover data?

There is another way in which snapshots can be used to recover lost data, specifically when used with AlwaysOn Availability Groups. If there would be a need to have a forced failover, a snapshot can ...
Sean Gallardy's user avatar
3 votes

Can you provide rights to a snapshot without providing rights to the database?

From Limitations and Requirements of Database Snapshots on TechNet: A database snapshot inherits the security constraints of its source database at the time of snapshot creation. Because snapshots ...
TheGameiswar's user avatar
  • 2,989
3 votes

Replication Access denied in Replication

I had the same problem and solved with the following steps: Right click shared folder and click properties Go to Advanced Sharing > Permissions In dialog "permissions for " Add snapshot user (\...
Ibrahim ben Salah's user avatar
3 votes

Replication Access denied in Replication

Nice question, I had the same scenario: Transactional Replication from on-prem to Azure SQL Database. My solution was: Replication > Local Publication > Right click on your publication > ...
Francesco Mantovani's user avatar
3 votes
Accepted

Are there open source RDBMS that support non-backup snapshots?

Arkhipov's extension for PostgreSQL enables Temporal Tables. https://pgxn.org/dist/temporal_tables/ Here's a decent guide for using it: http://clarkdave.net/2015/02/historical-records-with-...
Andrew Brennan's user avatar
3 votes
Accepted

Snapshot Backups for SQL Server Databases

Cons: necessitates installing the add-on for Commvault prior to running a restore. In an emergency, this can add a brutal layer of indirection. prevents piece-meal-restores of individual file groups,...
Hannah Vernon's user avatar
  • 70.1k
3 votes

Create DB snapshot without specifying the FILENAME attribute

Dynamic SQL is probably your best bet. See 3rd example here: https://www.sqlservercentral.com/scripts/create-database-snapshot-dynamically
Chuma's user avatar
  • 206
3 votes

How to run postgres in-memory on windows and sync to FS when a temer runs out?

That's how PostgreSQL operates by default – just set shared_buffers so that the whole database fits in it. Inefficient queries can be slow even on a small database that is cached in memory. You will ...
Laurenz Albe's user avatar
3 votes

Understanding the floor performance of a SQL Server snapshot restore

I'm attempting to repro your scenario, and am seeing the snapshot restore time between 600 to 800 milliseconds. This is the repro: USE master; IF DB_ID('test_snapshot') IS NOT NULL BEGIN ALTER ...
Hannah Vernon's user avatar
  • 70.1k
3 votes

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

No, you cannot do this. You can create a snapshot database of a log shipped database that is in STANDBY mode. However, in order to restore additional logs, you will need to drop the snapshot. Here's a ...
AMtwo's user avatar
  • 16.2k
3 votes

Log Replay on top of SQL database files captured during VSS snapshot failed

Attach MDF, LDF files in my SQL Server. When you attach the database it goes through recovery. There is no option to attach without recovery. Once it's recovered, you can take a tail log backup to ...
David Browne - Microsoft's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible