MS SQL – Database Backup
MS SQL – Database Backup
sqlserver backupintermediate
by MSSQL
17 views
MS SQL – Database Backup
12345
This backs up the whole database.
In order to have further differential or transaction log backups you have to create the full database backup first.
Syntax:
BACKUP DATABASE [DBNAME] TO DISK = N'H:\DB_BACKUP\FULL_DB_BACKUP_TEST.bak' WITH NAME = N'FULL DATABASE BACKUP - DBNAME',STATS
GO1234
Differential database backups are cumulative. This means that each differential database backup backs up the all the changes from the last Full database backup and NOT last Differential backup.
Syntax:
BACKUP DATABASE [DBNAME] TO DISK = N'H:\DB_BACKUP\FULL_DB_BACKUP_TEST.bak' WITH DIFFERENTIAL,NOINIT, NAME = N'DIFFERENTIAL BACKUP DBNAME DATABASE',STATS
GO1234
It takes complete transaction log file
Syntax:
BACKUP LOG [DBNAME] TO DISK = N'H:\DB_BACKUP\FULL_DB_BACKUP_TLOG.bak' WITH NAME= N'T-LOG BACKUP FOR DATABASE DBNAME',STATS
GO
12
SELECT name AS [Database Name],recovery_model_desc AS [Recovery Model] FROM sys.databases
GO
12
BACKUP DATABASE [TEST] TO DISK = N'H:\DB_BACKUP\TEST_BKP\FULL_DATABASE_BACKUP_TEST.bak' WITH NAME = N'FULL DATABASE BACKUP - TEST',STATS
GO
12
BACKUP DATABASE [TEST] TO DISK = N'H:\DB_BACKUP\TEST_BKP\FULL_DATABASE_BACKUP_TEST.bak' WITH DIFFERENTIAL,NOINIT, NAME = N'DIFFERENTIAL BACKUP TEST DATABASE',STATS
GO
12
BACKUP LOG [TEST] TO DISK = N'H:\DB_BACKUP\TEST_BKP\TEST_DB_BACKUP_TLOG.bak' WITH NAME= N'T-LOG BACKUP FOR DATABASE TEST',STATS
GO
12345678910111213141516171819202122
-- List all database backups from last week.
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
12345
RESTORE VERIFYONLY FROM DISK = 'H:\DB_BACKUP\TEST_BKP\FULL_DATABASE_BACKUP_TEST.bak';
RESTORE VERIFYONLY FROM DISK = 'H:\DB_BACKUP\TEST_BKP\TEST_DB_BACKUP_TLOG.bak';
RESTORE HEADERONLY FROM DISK = 'H:\DB_BACKUP\TEST_BKP\FULL_DATABASE_BACKUP_TEST.bak';
RESTORE HEADERONLY FROM DISK = 'H:\DB_BACKUP\TEST_BKP\TEST_DB_BACKUP_TLOG.bak';
Please to add comments
No comments yet. Be the first to comment!