Tuesday, March 3, 2015

How to Manually Adhoc take full backup of a database in SQL Server - SQL Server DBA Tutorial

In this video you will learn how to take Full backup of a database in SQL Server 2008, 2012 and 2014 using SQL Server management studio (SSMS) as well as using T-SQL script. It will walk you through different database backup options. It also illustrates the best practices while taking adhoc (on demand) full backup of SQL Server databases.

Scripts used in this video:

BACKUP DATABASE [SalesOrders] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLPROD\MSSQL\Backup\SalesOrders.bak'WITH RETAINDAYS = 10, NOFORMAT, NOINIT, NAME = N'SalesOrders-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM

GO
/*declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'SalesOrders' 
and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'SalesOrders' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''SalesOrders'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLPROD\MSSQL\Backup\SalesOrders.bak' 
WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO */



How to Manually Take Full Backup of SQL Server Database