SQL Server Database Restore Notes

While installing SQL 2012 on Windows Server 2008 R2

• Enable windows 3.5 from Windows Add / Remove Features
• While trying to restore SQL 2012 backup on SQL 2008, Error 'media family on device is incorrectly formed' for version mismatch is misleading

SQL Version to check query

SELECT

SERVERPROPERTY('ProductVersion') AS ProductVersion,

SERVERPROPERTY('ProductLevel') AS ProductLevel,

SERVERPROPERTY('Edition') AS Edition,

SERVERPROPERTY('EngineEdition') AS EngineEdition;

GO

Useful Notes

• How to extract the contents from an .ISO file without burning the .ISO to disc.
• Copying a SQL Server Database to Another Environment - (RESTORE FILELISTONLY command was very useful)
• How I completely remove a SQL Server database – Guaranteed to work – At least on my current setup - (Stop Services -> Delete Db Files -> Start Services -> Delete DBs) - 2 mins deletion

Quick Script encompassing the steps from above notes

Step 1 - Stop SQL Server
NET stop MSSQLSERVER

Step 2 - Remove DB Folders (MDF, LDF Files)
rmdir /s /q "C:\Databases\VOL\Data\"
rmdir /s /q "C:\Databases\VOL\Log\"

Step 3 - Recreate Same file path for Restore
mkdir  "C:\Databases\VOL\Data\"
mkdir  "C:\Databases\VOL\Log\"

Step 4 - Start SQL Server
NET start MSSQLSERVER

Step 5 - Run DB Restore script
Db Restore Command

Restore Script

• Identify mdf, log files from bak files
• Set DB to single user to restrict access
• Restore DB
• Set DB to multi user mode

RESTORE FILELISTONLY

FROM DISK = N'E:\TestDB_backup_20150210_000143.bak'

use master

ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE TestDB

FROM DISK = N'E:\TestDB_backup_20150210_000143.bak'

WITH REPLACE,

FILE = 1

    , MOVE N'TestDB_DAT'

        TO N'C:\databases\vol\DATA\TestDB.MDF'

    , MOVE N'TestDB_LOG'

        TO N'C:\databases\vol\LOG\TestDB_log.LDF'

    , NOUNLOAD, STATS = 10

   

ALTER DATABASE TestDB

SET MULTI_USER

WITH ROLLBACK IMMEDIATE

GO

Tweak it as you need :)

Happy Learning!!!