In order to recover data, ApexSQL Data Diff needs to be used in the similar way to compare and synchronize data and it can be read from article Recover deleted SQL data from a backup or from online database files.Įven if there’s no SQL database backup containing the dropped table, recover of lost tables due to Drop table operation can be done.ĪpexSQL Log is a SQL server transaction log viewer that audits, reverts or replays data and object changes. Note: ApexSQL Diff will only recover structure of the dropped table (and the dependent objects). Also, only the table structure can be recovered. If a SQL database backup doesn’t exist, this method is not applicable. The main grid shows the script and differences for every object, which provides granularity and helps in deciding which objects to select for synchronization. The objects depending on the dropped table are seamlessly recovered. The table and the dependent objects selected will be re-created.Īs ApexSQL Diff can read SQL Server database backups, the table recovery is faster as the backups are not being restored first. Review it, modify if needed, and execute: The generated script will be shown in the ApexSQL Diff’s integrated editor. If everything is in order, click the Create script button In the Output options step, select the Create a synchronization script as the output action:Ĭlick the Next button and review the Summary and any potential Warnings in the last step of the Synchronization wizard Go through the first two steps of the Synchronization wizard To script the objects the dropped table depends on, select them from the dependent objects list Check the table that should be recovered:Ĭlick the Synchronize button from the Home tab: The dropped table and any other objects that differ, if any, will be shown in the Results grid. ApexSQL Diff will show the comparison results in the Results grid.In the Tables object list in the right panel, check only the dropped table:Ĭlick the Compare button in the bottom-right corner of the New project window To have all objects that depend on the dropped table correctly scripted – triggers, tables, etc., leave all object types selected Select the Object filter tab and then select the Tables node in the left tree-view panel: Specify the production database name in the Database drop-down list:Įxpand the Advanced options and four additional tabs will be shown Specify the authentication method for that SQL instance (and a valid set of credentials if SQL Server authentication is chosen) Specify the SQL instance with the production database from the Server drop-down list Select Database from the Destination drop-down list On the Destination side of the Data source tab: Select Backup from the Source drop-down listĬlick the Add button, add a full database backup, or a full database backup and a chain of differential database backups, and check them On the Source side of the Data source tab: In the Project Management window, click the New button to create a new project: In order to recover lost table with ApexSQL Diff, follow these steps: It detects differences between database objects, generates comprehensive reports and synchronizes databases, database backups, snapshots, script folders and source control projects. This is another method where a database backup is necessary, but it doesn’t have to be restored to recover lost tables due to Drop table operation.ĪpexSQL Diff is a SQL server comparison and synchronization tool for SQL Server databases. Also, if the database is large, backup restoration can be time-consuming. This method is simple and straightforward, the only setback being that it is not applicable if there’s no database backup. Run the script against the original database The script creates the dropped table first, and then inserts the dropped records: Once the script has been generated, it can be opened, reviewed and modified. Types of data to script – select Schema and data, if there is a need to recover the lost records, too:.Note that a table cannot be dropped without removing the referencing constraint or the table first Generate script for dependent objects – to script the objects that depend on the dropped table.In the Set scripting options tab, choose between the Single file or Single file per object files to generate options, and specify the file path and name: In the Choose objects tab, switch to the Select specific database objects option and select the dropped table: In SQL Server Management Studio, right-click the database, select the Tasks sub-menu and then the Generate scripts command: If there is a full database backup or a chain of differential backups containing the dropped table:
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |