How to Create Full Database Backup Using TSQL Script

http://www.mytechmantra.com/LearnSQLServer/How-to-Backup-Database-SQL-Server-P3.html

How to Create Full Database Backup Using TSQL Script

Permissions Required to Take Database Backup in SQL Server

In order to take backups a user must be a member of DBCREATOR Server Role and DB_OWNER Database Role else you will receive the below mentioned error while performing backups.

Error Message

Msg 262, Level 14, State 1, Line 1
BACKUP DATABASE permission denied in database 'DatabaseName'.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Sample TSQL Script to Create Login with DBCREATOR Server Role and DB_OWNER Database Role

CREATE LOGIN BackupRestoreAdmin WITH PASSWORD='$tr0ngP@$$w0rd'
GO
CREATE USER BackupRestoreAdmin FOR LOGIN BackupRestoreAdmin
GO
EXEC sp_addsrvrolemember 'BackupRestoreAdmin', 'dbcreator'
GO
EXEC sp_addrolemember 'db_owner','BackupRestoreAdmin'
GO

Read more: http://www.mytechmantra.com/LearnSQLServer/How-to-Backup-Database-SQL-Server-P1.html#ixzz3CLb9L095
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook

The following example TSQL script can be used to take the full back up of AdventureWorks database.

DECLARE @BackupDatabaseName          SYSNAME
DECLARE @DatabaseBackupLocation      NVARCHAR(120) 

SET @BackupDatabaseName = 'AdventureWorks'
SET @DatabaseBackupLocation = 'C:\DBBackups\AdventureWorks.BAK'

/* Database Backup Script Using Compression Feature of SQL Server 2008 */
BACKUP DATABASE  @BackupDatabaseName
TO  DISK = @DatabaseBackupLocation
WITH NOFORMAT, NOINIT, 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 25 

/* Verify the Database Backup File Once the Backup has completed successfully */
RESTORE VERIFYONLY
FROM DISK = @DatabaseBackupLocation
GO

Read more: http://www.mytechmantra.com/LearnSQLServer/How-to-Backup-Database-SQL-Server-P3.html#ixzz3CLaJpHql
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook