MSSQL|Database Backup/Restore and Schema comparison
This video from Soft Skills Cluster demonstrates how to back up and restore a Microsoft SQL Server database and then compare the schemas of the original and restored databases [0:00-0:07].
The steps are:
1. Backup: The video uses SQL Server Management Studio to create a full database backup using a specific T-SQL command [1:00-1:09].
2. Identify Files: Before restoring, it's important to identify the master data file (MDF) and log file (LDF) using another T-SQL query [1:11-1:46].
3. Restore: A third T-SQL command is used to restore the database to a new database, specifying the new file names [1:54-2:43].
4. Schema Comparison: Visual Studio compares the schemas of the original and restored databases. This involves using the "Schema Compare" tool, selecting the source and target databases, and clicking "Compare" [3:01-4:44]. Any differences will be highlighted, which can be subsequently applied to update the target database [5:10-6:25].
The video concludes by demonstrating that after addressing any inconsistencies and aligning the databases, the schema comparison tool will show that "no variations were detected" [7:26-8:00]. The benefits of this process consist of minimizing downtime and maintaining data consistency. The tools employed are SQL Server Management Studio and Visual Studio. [8:16-8:50]