SQL Server Backup and Restore
Related to:: SQL Server
Backup
BACKUP DATABASE DatabaseName
TO DISK = '/opt/mssql/backup/DatabaseName.bak'
WITH NAME = 'DatabaseName',
COMPRESSION,
FORMAT,
INIT,
SKIP,
NOUNLOAD,
STATS = 10
Query 1 OK: Msg: 3211, Line 1, State: 1, Level: 0
10 percent processed.
...
Query 1 OK: Msg: 3211, Line 1, State: 1, Level: 0
100 percent processed.
Query 1 OK: Msg: 4035, Line 1, State: 1, Level: 0
Processed 246192 pages for database 'DatabaseName', file 'DatabaseName' on file 1.
Query 1 OK: Msg: 4035, Line 1, State: 1, Level: 0
Processed 1 pages for database 'DatabaseName', file 'DatabaseName_log' on file 1.
Query 1 OK: Msg: 3014, Line 1, State: 1, Level: 0
BACKUP DATABASE successfully processed 246193 pages in 44.468 seconds (43.253 MB/sec).Check Backup File
Check Health
This outputs the health status of the backup file.
RESTORE VERIFYONLY FROM DISK = '/opt/mssql/backup/DatabaseName.bak';
The backup set on file 1 is valid.Get Information about the Backup
RESTORE HEADERONLY FROM DISK = '/opt/mssql/backup/DatabaseName.bak'Get the File List from the Backup
This is used to get a backup file’s logical filenames to use on the MOVE part of the restore.
RESTORE FILELISTONLY FROM DISK = '/opt/mssql/backup/DatabaseName.bak'Restore
On New Database
This can be used to copy a database to a new one.
-- Perform the restore
RESTORE DATABASE NewDatabase
FROM
DISK = '/opt/mssql/backup/DatabaseName.bak' WITH
MOVE 'DatabaseName'
TO '/var/opt/mssql/data/NewDatabase.mdf',
MOVE 'DatabaseName_log'
TO '/var/opt/mssql/data/NewDatabase_log.ldf';
-- Switches to the newly created DB
USE NewDatabase;
-- Alters data file logical name
ALTER DATABASE NewDatabase
MODIFY FILE (NAME = 'DatabaseName', NEWNAME = 'NewDatabase');
-- Alters log file logical name
ALTER DATABASE NewDatabase
MODIFY FILE (NAME = 'DatabaseName_log', NEWNAME = 'NewDatabase_log');On The Same Database
Danger
The WITH REPLACE will replace all database contents. Use with care.
-- Force database to be in Offline state
ALTER DATABASE DatabaseName
SET OFFLINE WITH ROLLBACK IMMEDIATE;
-- Perform the restore
RESTORE DATABASE DatabaseName
FROM
DISK = '/opt/mssql/backup/DatabaseName.bak' WITH REPLACEOn Another Database
Danger
The WITH REPLACE will replace all database contents. Use with care.
-- Force database to be in Offline state
ALTER DATABASE NewDatabaseName
SET OFFLINE WITH ROLLBACK IMMEDIATE;
-- Perform the restore
RESTORE DATABASE DatabaseName
FROM
DISK = '/opt/mssql/backup/DatabaseName.bak' WITH REPLACE,
MOVE 'DatabaseName'
TO '/var/opt/mssql/data/NewDatabase.mdf',
MOVE 'DatabaseName_log'
TO '/var/opt/mssql/data/NewDatabase_log.ldf';