Friday, July 08, 2011

Restore the Database from the Backup file

-- PART 1
-- Find the Backup file details like Machinename, softwareBuild, collation, backupsize, BackupStartDate

USE
MASTER
go
RESTORE HEADERONLY
FROM DISK = 'H:\MSSQL\Backup\AdventuresDb.BAK'

-- Find the Backup file orignal File path and Logical filename, Physical filename

RESTORE FILELISTONLY
FROM DISK = H:\MSSQL\Backup\AdventuresDb.BAK'

-- Find the Backup file orignal File path and Logical filename, Physical filename

RESTORE VERIFYONLY
FROM DISK = 'H:\MSSQL\Backup\AdventuresDb.BAK'

-- Restore the database from the backup file
-- eg. Development database refresh from production copy

RESTORE DATABASE AdventuresDb
FROM DISK = 'H:\MSSQL\Backup\AdventuresDb.BAK'
WITH RECOVERY, REPLACE, STATS = 10

------------------------------------------------------------------ PART 2
-- Find the Backup file orignal File path and Logical filename, Physical filename

USE MASTER
go
RESTORE FILELISTONLY
FROM DISK = 'F:\MSSQL\Backup\AdventuresDb.BAK'
-- Create/Restore the Database from the backup file with move to different locations
-- eg. Development database refresh from production copy


RESTORE DATABASE AdventuresDb
FROM DISK = 'F:\MSSQL\Backup\AdventuresDb.BAK'
WITH RECOVERY,
MOVE 'AdventuresDb_Data' TO 'D:\MSSQL\Data\AdventuresDb.MDF',  MOVE 'AdventuresDb_Log' TO 'E:\MSSQL\Log\AdventuresDb_log.LDF',
STATS = 10

-- sp_helpdb AdventuresDb


1 comment:

Manimaran said...

Can this be possible if I want to run from the command line on sql server 2008?

[SqlCmd -E -S MyServerMyInstance –Q “BACKUP DATABASE [MyDB] TO DISK=’D:BackupsMyDB.bak’”

Also similar for restore

Thanks
Mani