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, ...
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.
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 ...
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.
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 ...
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 ...
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:
FROM sys.master_files AS mf
INNER JOIN sys.databases AS dbs
ON mf.database_id = dbs.database_id
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
[...] 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 ...
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 don't think it is possible outside the VDI API.
There is no T-SQL API to attach a database with NORECOVERY. Vote for the feature here.
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 ...
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 ...
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 ...
Yes, it is safe. There is a tool http://www.lenzg.net/mylvmbackup/ that uses LVM snapshots to take backups of MySQL.
From InnoDB's standpoint a backup copy taken with LVM snapshots looks like as InnoDB has suddenly crashed. When you start MySQL from the backup copy it will start crash recovery. To save some time it's better to do the crash recovery ...
For InnoDB and other transactional data stores it should be OK purely from a referential integrity point of view as the normal crash recovery procedure will roll out any incomplete transactions that were in progress when the backup was taken, but there are potential problems that mean it is not recommended that you backup databases this way:
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
that contains no user data and has not yet been allocated disk space
for user data. As more and more pages are updated in the source
database, the size of ...
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 reverse a serious error e.g. drop table, deleting data, etc.
This is important :
all changes made after the snapshot was created are lost.
can we take a ...
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 big. If you use compression the backup size will be less as well.
You can have a TSQL Agent jobs or just scripts (may be you can create stored procedure and ...
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 cases, this will present such RPOs and RTOs that the solution is not going to be feasible. What's more, restoring a SAN snapshot to a stable state is ...
Mysql 5.6 introduced FLUSH TABLES FOR EXPORT which might be a better option for your use case if you use InnoDB exclusively (apart from the internal mysql database which is always MyISAM):
Unfortunately I cannot comment here (yet), but I did quite some research on the answer of Shlomi Noach above. I hope he ...
@RemusRusanu has best explained the native and character mode for bcp. +1 for that from me.
To deal with your situation, you can take either of the 2 different approaches.
The first one is to Initialize the subscription from a backup. Refer to How to: Initialize a Transactional Subscriber from a Backup (Replication Transact-SQL Programming). This is self ...
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.
Incorrect. An unfortunate overload of the term 'snapshot'... A snapshot read of the index is used, which means row-versioning see How Online Index Operations Work:
A snapshot of the table is defined. That is, ...
From the Microsoft SQL Server 2012 Internals book:
The snapshot files contain only the data that has changed from the source. For every file, SQL Server creates a bitmap that is kept in cache, with a bit for each page of the file, indicating whether that page has been copied to the snapshot. Every time a page in the source is updated, SQL Server checks ...
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 are read-only, inherited permissions cannot be changed and permission changes made to the source will not be reflected in existing snapshots.
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:
RESTORE DATABASE DatabaseName FROM DATABASE_SNAPSHOT = SnapshotName
However that will overwrite your existing database. If you think you might need to keep the ...