Backup Database Wizard or backup sql database dialog screen will start.
BACKUP DATABASE [BackupDatabase]
TO DISK = N'c:\fullbackups\BackupDatabase.bak'
WITH NOINIT,
NAME = N'BackupDatabase-Full Database Backup',
COMPRESSION,
STATS = 10
GO
Verify that the database backup is valid (not that the data within is valid)
declare @backupSetId as int
select @backupSetId = position
from msdb..backupset
where database_name=N'BackupDatabase'
and backup_set_id=(select max(backup_set_id)
from msdb..backupset where database_name=N'BackupDatabase' )
if @backupSetId is null
begin
raiserror(N'Verify failed. Backup information for database ''BackupDatabase'' not found.', 16, 1)
end
RESTORE VERIFYONLY
FROM
DISK = N'c:\fullbackups\BackupDatabase.bak'
WITH FILE = @backupSetId
GO
Use msdb
go
SELECT logical_name,physical_name,file_number,backup_size,file_type, *
FROM dbo.backupfile
ORDER BY 1 -- Contains one row for each data or log file that is backed up
SELECT * FROM dbo.backupmediafamily -- Contains one row for each media family
SELECT * FROM dbo.backupmediaset -- Contains one row for each backup media set
SELECT * FROM dbo.backupset -- Contains a row for each backup set
SELECT * FROM dbo.backupfilegroup -- Contains one row for each filegroup in a database at the time of backup
Extended backup script with options
BACKUP DATABASE [Production]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Production.bak'
WITH
--NOFORMAT,
INIT, --<< Specifies that all backup sets should be overwritten
NAME = N'Production-Full Database Backup',
--SKIP,
--NOREWIND,
--NOUNLOAD,
COMPRESSION, --<< Compresses the database
STATS = 10, CHECKSUM --<< Specifies that the backup operation will verify each page for checksum and torn page
GO
Verifies that the database backup is valid
declare @backupSetId as int
select @backupSetId = position from msdb..backupset
where database_name=N'Production' and backup_set_id=(select max(backup_set_id)
from msdb..backupset
where database_name=N'Production' )
if @backupSetId is null
begin
raiserror(N'Verify failed. Backup information for database ''Production'' not found.', 16, 1)
end
RESTORE VERIFYONLY
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Production.bak'
WITH FILE = @backupSetId,
NOUNLOAD,
NOREWIND
GO
Validating a backup
Create a database
Create Database Items
Go
Use Items
Go
Create a table
Create table Products
(ProductID int IDENTITY (1,1) Primary Key,
ProductName varchar (100),
Brand varchar (100))
go
Insert data into table
insert into Products values ('Bike','Genesis')
insert into Products values ('Hat','Nike')
insert into Products values ('Shoe','Payless')
insert into Products values ('Phone','Apple')
insert into Products values ('Book','Green')
insert into Products values ('Cup','Large')
View data
select * from Products
Full backup with 6 products
BACKUP DATABASE items
TO DISK = N'c:\fullbackups\items.bak'
WITH NOINIT,
NAME = N'items-Full Database Backup',
COMPRESSION,
STATS = 10
GO
--Verify that the database backup is valid (not that the data within the database is valid!!!)
declare @backupSetId as int
select @backupSetId = position
from msdb..backupset
where database_name=N'items'
and backup_set_id=(select max(backup_set_id)
from msdb..backupset where database_name=N'items' )
if @backupSetId is null
begin
raiserror(N'Verify failed. Backup information for database ''items'' not found.', 16, 1)
end
RESTORE VERIFYONLY
FROM
DISK = N'c:\fullbackups\items.bak'
WITH FILE = @backupSetId
GO
Insert 2 more rows of data into table
insert into Products values ('Helmit','Shock')
insert into Products values ('Mitts','UnderAmour')
View data
select * from Products
Differential backup
The database must have a full back up in order to take a differential backup; it only backups the changes since last full backup.
BACKUP DATABASE items
TO DISK = N'c:\fullbackups\items.bak'
WITH DIFFERENTIAL ,
NOINIT,
NAME = N'items-Differential Database Backup',
COMPRESSION,
STATS = 10
GO
The differential backup has captured those TWO additional inserts to the database (the last two changes since that last full backup)
Verify that the differential backup is valid (not that the data within the database is valid!!!)
declare @backupSetId as int
select @backupSetId = position
from msdb..backupset
where database_name=N'items'
and backup_set_id=(select max(backup_set_id)
from msdb..backupset where database_name=N'items' )
if @backupSetId is null
begin
raiserror(N'Verify failed. Backup information for database ''items'' not found.', 16, 1)
end
RESTORE VERIFYONLY
FROM DISK = N'c:\fullbackups\items.bak'
WITH FILE = @backupSetId
GO
Insert 2 more rows of data into table
insert into Products values ('Screen','HP')
insert into Products values ('Ipad','Apple')
View data
select * from Products
Transaction Log backup
You must backup the transaction log, if SQL Server database uses either FULL or BULK-LOGGED recovery model otherwise transaction log is going to full.
Backing up the transaction log truncates the log and user should be able to restore the database to a specific point in time.
BACKUP LOG items
TO DISK = N'c:\fullbackups\items.bak'
WITH
NAME = N'items-Transaction Log Backup',
COMPRESSION,
STATS = 10
GO
The transaction log backup has captured those TWO additional inserts to the database (all modification to the database)
declare @backupSetId as int
select @backupSetId = position
from msdb..backupset
where database_name=N'items' and backup_set_id=(select max(backup_set_id)
from msdb..backupset where database_name=N'items' )
if @backupSetId is null
begin
raiserror(N'Verify failed. Backup information for database ''items'' not found.', 16, 1)
end
RESTORE VERIFYONLY
FROM DISK = N'c:\fullbackups\items.bak'
WITH FILE = @backupSetId
GO
RESTORE HEADERONLY FROM DISK = N'c:\fullbackups\items.bak'
RESTORE LABELONLY FROM DISK = N'c:\fullbackups\items.bak'
RESTORE FILELISTONLY FROM DISK = N'c:\fullbackups\items.bak'
Backup History
The following commands provide information as to the history of the backups in the MSDB database.
Use msdb
go
SELECT logical_name, * FROM dbo.backupfile order by 1 -- Contains one row for each data or log file that is backed up
SELECT * FROM dbo.backupset -- Contains a row for each backup set
SELECT * FROM dbo.backupmediafamily -- Contains one row for each media family
SELECT * FROM dbo.backupmediaset -- Contains one row for each backup media set
SELECT * FROM dbo.backupfilegroup -- Contains one row for each filegroup in a database at the time of backup
Also See: