For validating mdf files
So let's work through an example scenario to illustrate.While the code within this tip was developed and tested against a SQL Server 2005 instance, it should function on any version currently in use.USE [master]GORESTORE DATABASE [Adventure Works Copy] FROM DISK = 'c:\mssql\backup\yukon\AW2K5_Full.bak' WITH CHECKSUM, MOVE 'Adventure Works_Data' TO 'c:\mssql\data\yukon\Adventure Works Copy_Data.mdf', MOVE 'Adventure Works_Log' TO 'c:\mssql\log\yukon\Adventure Works Copy_Log.ldf', RECOVERY, REPLACE, STATS = 10;10 percent processed.20 percent processed.30 percent processed.40 percent processed.50 percent processed.60 percent processed.70 percent processed.80 percent processed.90 percent processed.100 percent processed.Processed 20576 pages for database 'Adventure Works Copy', file 'Adventure Works_Data' on file 1.Questions on Stack Overflow are expected to relate to programming within the scope defined by the community.
You could then rerun the above query to find information about the database.
USE [master]GOSELECT DB_NAME([database_id]) [database_name], [file_id], [type_desc] [file_type], [name] [logical_name], [physical_name]FROM sys.[master_files]WHERE [database_id] IN (DB_ID('Adventure Works'), DB_ID('Adventure Works Copy'))ORDER BY [type], DB_NAME([database_id]); As long as the database being restored already has the same number and type of files to match those contained in the backupset being restored, the MOVE clause is not required.
This functionality offers additional flexibility in automating regularly-scheduled restore operations.
The drive layouts between the servers are almost never the same.
I am constantly shuffling files on my development servers due to space constraints, which in turn breaks the automated restore scripts because the MOVE clauses are invalidated.
The paths used in the code may need to be updated to reflect the folder structure in use by your environment.