MSSQL Server database Backup Restore Common SQL statements and note

1. Backing Up the database

Backup Database Db_nameto disk= ' D:\db_name.bak ' with format

--by using the with format, you can overwrite any existing backup and create a new media set.
--You can avoid some errors caused by backup settings.
--explanation on the Help document:
--FORMAT specifies that the media header should be written to all volumes used for this backup operation.
-Any existing media headers are rewritten. The format option invalidates the entire media content.
--and ignore any existing content.

2. Backing up logs

Backup log log_nameto disk= ' D:\log_name.log ' with NORECOVERY

--When backing up the log, use Norecovery to back up the tail log.
--When you back up the log, the database is in a recovery state,
--Other processes can no longer access the database, the log will no longer increase,
-This will restore the normal recovery time.
--When restoring a backup, if you want to restore the log at the same time,
--must be added norecovery to restore the log, if there are multiple log backups,
--In addition to the last log backup, restoring other logs must also be accompanied by norecovery to revert to the last log.
-In the final analysis, this is a database requirement.

3. Restore the Database
Take the database offline first
ALTER DATABASE db_name set offline with rollback immediate
To restore again

Restore Database Db_namefrom disk= ' D:\db_name.bak ' with replace

Attention:
1. Make sure the database is online before backing up
2. Make sure the database is offline before restoring

4. Offline and on-line database
Offline (disconnect from other users)

ALTER DATABASE db_name set offline with rollback immediate

On-Line (open connection to other users)

ALTER DATABASE db_name set online with  rollback immediate

　

MSSQL Server database Backup Restore Common SQL statements and note