[MSSQL] Full Backup and Restore

  • 480
  • 0
  • 2017-08-29

摘要:Full Backup and Restore

--Backup
BACKUP DATABASE [XXX] TO DISK=N'...\XXX.BAK'

--Verify
RESTORE VERIFYONLY FROM DISK=N'...\XXX.BAK'

--Restore
RESTORE DATABASE [XXX] FROM DISK = '...\XXX.BAK'
WITH MOVE 'XXX' TO '...\XXX.mdf',
MOVE 'XXX_1' TO '...\XXX_1.mdf',
MOVE 'XXX_2' TO '...\XXX_2.mdf',
MOVE 'XXX_3' TO '...\XXX_3.mdf',
MOVE 'XXX_4' TO '...\XXX_4.mdf',
MOVE 'XXX_5' TO '...\XXX_5.mdf',
MOVE 'XXX_log' TO '...\XXX_Log.ldf';

--Stop restoring
RESTORE DATABASE [TSL] WITH RECOVERY

--Change physical database filename
ALTER DATABASE SEL MODIFY FILE (Name='SEL_log', FILENAME='D:\SQLData\SEL_Log.ldf')

--Clear transaction log
ALTER DATABASE SEL SET RECOVERY SIMPLE
DBCC SHRINKFILE (2, EMPTYFILE)
ALTER DATABASE SEL SET RECOVERY FULL