![]() This example assumes that two snapshots currently exist on the Sales database: sales_snapshot0600 and sales_snapshot1200. Reverting AdventureWorks to AdventureWorks_dbss1800ĭATABASE_SNAPSHOT = 'AdventureWorks_dbss1800' ![]() For the example that creates the snapshot to which the database is reverted here, see Create a Database Snapshot (Transact-SQL). This example assumes that only one snapshot currently exists on the AdventureWorks2012 database. Reverting a snapshot on the Sales database Reverting a snapshot on the AdventureWorks databaseī. This section contains the following examples of reverting a database to a database snapshot:Ī. To back up a database, see Create a Full Database Backup (SQL Server). Optionally, back up the reverted database, especially if it uses the full (or bulk-logged) recovery model. The reverted database retains the permissions and configuration (such as database owner and recovery model) of the database snapshot. Notice that in this statement, you must specify a snapshot name rather than a backup device.įor more information, see RESTORE (Transact-SQL). Where database_name is the source database and database_snapshot_name is the name of the snapshot to which you want to revert the database. RESTORE DATABASE database_name FROM DATABASE_SNAPSHOT = database_snapshot_name To revert the database, use the following Transact-SQL statement: For more information, see Back Up a Transaction Log (SQL Server) or Back Up the Transaction Log When the Database Is Damaged (SQL Server).Ī revert operation requires RESTORE DATABASE permissions on the source database. If the database uses the full recovery model, before reverting, you should back up the log. ![]() Also, you can identify the source database of a view from the source_database_id column of the sys.databases (Transact-SQL) catalog view.įor information on dropping snapshots, see Drop a Database Snapshot (Transact-SQL). You can view the snapshots on a database in SQL Server Management Studio (see View a Database Snapshot (SQL Server)). Identify the database snapshot to which you want to revert the database. For more information, see Back Up and Restore of SQL Server Databases.įor an example of this procedure, see Examples (Transact-SQL), later in this section. Therefore, taking regular backups and testing your restore plan are essential to protect a database. Furthermore, even when it is possible, reverting in the event of corruption is unlikely to correct the problem. A database snapshot is an incomplete copy of the database files, so if either the database or the database snapshot is corrupted, reverting from a snapshot is likely to be impossible. Reverting is not intended for media recovery. For reverting, there must only be one snapshot for the database, to which you plan to revert.Īny read-only or compressed filegroups exist in the database.Īny files are now offline but were online when the snapshot was created.īefore reverting a database, consider the following limitations: There are multiple snapshots for the database. Reverting is unsupported under the following conditions: To Revert a Database to a Database Snapshot, using: Transact-SQLīefore You Begin Limitations and Restrictions However, all changes made after the snapshot was created are lost. For example, reverting a database might be useful for reverse a recent serious user error, such as a dropped table. If data in an online database becomes damaged, in some cases, reverting the database to a database snapshot that predates the damage might be an appropriate alternative to restoring the database from a backup. Applies to: SQL Server (all supported versions)
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |