17

A snapshot database will have an entry in sys.databases.source_database_id, so you can use something like the following to check for a current snapshot. Similarly, you could use the same check to DROP an existing snapshot before creating new. IF NOT EXISTS( SELECT * FROM sys.databases WHERE name = 'DatabaseA_Snapshot'...


14

is because it has to write to cache and then to the TempDb (row version) and then it can return to the caller. No, this is incorrect. It somehow implies that writes in the presence of versioning have higher latency as each write has to touch the disk (for tempdb) which is not true. The write into the tempdb is also a write into 'cache'. The only 'wait' ...


13

See the fine manual. If my advice conflicts with its' in any way, it's right. A sync isn't a bad idea, unless your copy tool fsync()s each WAL file it writes and the directory it's in before copying the next one. An incomplete last WAL file doesn't matter much; at worst, you just delete it. Pg will generally choke on an incomplete WAL - though there's no ...


13

One way you could approach this, is to create a "base" test database (including all test data) once. Then before each test suite, create a new database using the "base" database as the template. create database test_db with template = base_test_db; Using this, everything that is in the template will be copied to the new database (including data, ...


12

There's a few questions you need to ask first. Are you using SQL Server Enterprise Edition? Mirroring can be done with either Standard or Enterprise, but snapshots can only be done with Enterprise. Are you already paying for licensing on the mirror? There's a lot of fine print, but you basically get the mirror for free - only as long as you're not ...


12

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 MySQL (partially) are supported.


12

I need to backup up 10-20 SQL Server dbs used simultaneously by a single enterprise app, while they are online, in such a way as to restore them to a state that is largely synchronized across all dbs What you are looking for is a consistent backup across all your customer databases, you should use FULL backups along with Marked Transactions (emphasis in ...


12

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: The database must currently have only one database snapshot, to which you plan to revert. Any read-only or compressed filegroups exist in the database. ...


8

From Books On Line: Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, the entire snapshot is generated and sent to Subscribers. Using snapshot replication by itself is most appropriate when one or more of the following is true: ...


7

The underlying mechanics is pretty much the same. However, in snapshot isolation the versions are potentially kept significantly longer causing a higher strain on the resources overall.


7

By default, your usage of mysqldump will lock each table as it dumps it and there will not be any consistency among tables. You will also not get any triggers, stored procedures, stored functions, or events backed up, and if any of your view definitions are invalid (referencing nonexistent tables or columns that you may have dropped or renamed since the ...


7

Using a database snapshot located on your production OLTP server will, in all likelihood, make performance worse. There are two main reasons: Extra write overhead. Snapshots are copy-on-write, so this will obviously impact performance. A snapshot will share pages from the old database on disk, but it uses its own memory in the buffer pool. If you have a ...


7

Note that Character representation of any data will almost always exceed the original size. Depending on type, it can exceed by a large margin. Eg. an int column takes 4 bytes, but the character representation of 1000000 takes ~16 bytes as Unicode, including delimiters. That is a x4 increase right there. Dates, floats, numerics all will usually increase, and ...


7

If you are running full backups as well as transaction log backups (and you should if you consider this data important) you could just copy over the backups and transaction log backups to the test system and perform a point in time restore to restore the databases to +- the same time. Depending on whether all databases reside on the same SQL Server machine ...


7

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, mf.type_desc, mf.name, mf.physical_name FROM sys.master_files AS mf INNER JOIN sys.databases AS dbs ON mf.database_id = dbs.database_id WHERE ...


6

If with "export/import" you mean the "traditional" exp and imp commands, than I'd say there is a better solution: expdp and impdp are much faster and much more flexible when copying a snapshot of a database (or single schema). If the database is not huge (i.e. Terabytes) then expdp and impdp is the easiest way to achieve this. They are easily scriptable ...


6

If you abort a RESTORE mid-way the database is in an unusable state. This makes sense: Some pages are old, some are new. Stopping the debugger kills the client process causing SQL Server to kill the connection and all associated sessions and requests. To get it working, restart the last restore step that was interrupted. In your case, restore from snapshot ...


6

Your scenario is interesting : Small databases approx 1GB in size. Number of databases = 20 Refresh of the data occurs on nightly basis, so the data can be stale by one day. Initial thought reveals that snapshot replication should be OK as you thought, but I would highly recommend to go for backup/restore method - safe, reliable and less overhead of ...


6

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. Scenario A) We used database snapshots to provide a static image of a database for reporting (ETL to a datawarehouse) purposes. Daily a script would run at the ...


5

If this is just for development/testing, you can get a license for Developer Edition (free for 2014+ and ~$50 for 2012 and lower). and use snapshots, since Developer Edition supports all of the features supported in Enterprise Edition. The only difference is licensing - you cannot use Developer Edition in production. You're not going to be able to cook ...


5

MSSnapshot_History has snapshot information for replication. You can join to MSSnapshot_Agents to narrow it down to your specific agent, publication, and job. These tables are found in the [distribution] database on your distribution instance. Here's a basic query to get the last start time of your snapshot agents. select a.name, max(start_time) ...


5

Database snapshots are used in SQL Server 2005 or later every time you do a DBCC CHECKDB - so they are widely used, even if it is behind the scenes. Perhaps the biggest hindrance to adoption is that Management Studio didn't offer support. It sounds like an ideal use of snapshots in a dev environment just for quick script deployments and quick roll backs. ...


5

I'd recommend looking into DB Snapshots. They are (in theory) the fastest way to undo changes unless you're wrapping each change in its own transaction with a rollback at the end (after you've written the results of the test to a database). If you're willing to work it into the framework of your testing, encapsulating each individual test in a rollback ...


5

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 if they were using the snapshot directly. Whenever a new snapshot is created, run a script to update the synonyms with the new snapshot database name. Below is ...


5

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 early on the first day of each month. The job would simply restore "last nights" full backup. You are taking nightly backups, right? In order to ensure the ...


5

[...] 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 rely on TSQL command to create database snapshot. This is also mentioned in Create Database Snapshot official document The only way to create a SQL Server ...


4

Yes, that is a good and popular approach to offloading reporting, especially since you already have database mirroring in place. Please see this BOL reference for more specifics: http://msdn.microsoft.com/en-us/library/ms175511.aspx As for when you should create the snapshots, that is completely up to you and your business requirements, depending on how ...


4

I would suggest turning on READ_COMMITTED_SNAPSHOT at the database level - that will get rid of a large amount of the concurrency issues you are seeing with the Access front end. Having TIMESTAMP fields in the tables that you are having issues with will help Access, but not with the ASYNC_NETWORK_IO waits. Those waits are showing up from the various ...


4

I have noticed that the snapshot agent job runs hourly and it looks like it does a refresh of the publications (literally, a new snapshot?) Should this be doing a refresh this often if no new articles are being added or changed? NO. Snapshot should not be run frequently, unless you are adding/dropping articles. Also, Transactional replication uses ...


4

There is no reliable way of interrupting FLUSH TABLES WITH READ LOCK. I disagree with the previous answer. You can try KILLing the FLUSH query all you want. Typically this will just hang till the original command completes. However, some good news for you. You should be aware that FLUSH TABLES WITH READ LOCK is not strictly necessary in order to take LVM ...


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