• Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
  • Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
home/Knowledge Base/MS SQL Server/Backup using T-SQL
Popular Search:Oracle, SQL Server, MongoDB

Backup using T-SQL

125 views 0 September 7, 2019 April 2, 2021 admin

Backup using T-SQL in SQL Server

1. FULL DATABASE BACKUP:
BACKUP DATABASE [Banking_DB] TO DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\commandline\FULL\BANKINGDB_BACKUP.bak’ WITH FORMAT, stats

2. DIFFERENTIAL DATABASE BACKUP:
BACKUP DATABASE [Banking_DB] TO DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\commandline\DIFF\DIFF_BANKINGDB_BACKUP.bak’ WITH FORMAT, DIFFERENTIAL, stats

3. FULL FILEGROUP BACKUP:
BACKUP DATABASE [Banking_DB] FILEGROUP = ‘PRIMARY’ TO DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\commandline\FILEGRP\FileGroup_BANKINGDB_BKP.bak’ WITH FORMAT

4. DIFFERENTIAL FILEGROUP BACKUP:
BACKUP DATABASE [Banking_DB] FILEGROUP = ‘PRIMARY’ TO DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\commandline\FILEGRP\Diff_FileGroup_BANKINGDB_BKP.bak’ WITH FORMAT, DIFFERENTIAL, stats

5. FULL FILE BACKUP:
BACKUP DATABASE [Banking_DB] FILE = ‘Banking_DB’ TO DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\commandline\FILES\File_BANKINGDB_BKP.bak’ WITH FORMAT

6. DIFFERENTIAL FILE BACKUP:
BACKUP DATABASE [Banking_DB] FILE = ‘Banking_DB’ TO DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\commandline\FILES\Diff_File_BANKINGDB_BKP.bak’ WITH FORMAT, DIFFERENTIAL

7. REGULAR LOG BACKUP:
BACKUP LOG [Banking_DB] TO DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\commandline\LOGS\LOG_BANKINGDB_BKP.trn’ WITH FORMAT, stats

8. COPY ONLY BACKUP:
BACKUP DATABASE [Banking_DB] TO DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\commandline\BANKINGDB_BACKUP_COPY.bak’ WITH NOFORMAT, COPY_ONLY, stats

9. MIRRORED BACKUP:
BACKUP DATABASE [Banking_DB] TO
DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\commandline\M1\Mirror1_BANKINGDB_BKP.bak’
MIRROR TO
DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\commandline\M2\Mirror2_BANKINGDB_BKP.bak’
WITH FORMAT

10. SPLIT BACKUP — BELOW TWO BACKUP FILES FORM ONE BACKUP MEDIA FAMILY
BACKUP DATABASE [Banking_DB] TO
DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\commandline\S1\SPLIT_BANKINGDB_BKP_PART1.bak’
,
DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\commandline\S2\SPLIT_BANKINGDB_BKP_PART2.bak’
WITH FORMAT

11. PARTIAL BACKUP:
BACKUP DATABASE [Banking_DB]
READ_WRITE_FILEGROUPS
TO DISK = ‘D:\MSSQL\SQL_HOME\SQL_2017\db_home1\bkp_dir\commandline\BANKINGDB_BACKUP_PARTIAL.bak’ WITH FORMAT, COMPRESSION, CHECKSUM

12. HOT BACKUPS:
—-BACKUP DATABASE [Banking_DB] TO DISK = N’E:\BANKINGDB_BACKUPS.bak’ WITH RETAINDAYS = 14, NOFORMAT, NOINIT, NAME = N’BankingDB-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM, CONTINUE_AFTER_ERROR
GO
—-declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N’BankingDB’ and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N’BankingDB’ )
if @backupSetId is null begin raiserror(N’Verify failed. Backup information for database ”BankingDB” not found.’, 16, 1) end
RESTORE VERIFYONLY FROM DISK = N’E:\BANKINGDB_BACKUPS.bak’ WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

13. COLD BACKUPS (DURING MAINTENANCE HOURS ONLY)
STEP 1: FROM THE SOURCE SERVER > RIGHT CLICK DATABASE > DETACH
STEP 2: FROM THE DESTINATION SERVER > RIGHT CLICK DATABASES FOLDER > ATTACH

Tags:backup

Was this helpful?

Yes  No
Related Articles
  • MS SQL Server Notes
  • Important Counters (perfmon.exe)
  • Restoring Backup via Scripts
  • Restore Backup in SQL Server
  • SQL Server JOBS
  • Steps to Take Backup from GUI in SQL Server
Leave A Comment Cancel reply

MS SQL Server
  • Backup using T-SQL
  • MS SQL Server Notes
  • Important Counters (perfmon.exe)
  • Restoring Backup via Scripts
  • Restore Backup in SQL Server
  • SQL Server JOBS
View All 20  
Popular Articles
  • Upgrade Oracle Database from 11.2.0.3 to 12.1.0.2
  • Upgrading MariaDB on Windows
  • Oracle DBA Basic Interview Questions Part A
  • Purging Oracle Sysaux Tablespace
  • Client – Server Architecture
KB Categories
  • Oracle Database
    • Oracle RAC
    • Oracle ASM
    • Oracle GoldenGate
    • Oracle Tuning
    • Oracle 11g Database
    • Oracle Database Upgrade
    • Oracle 12c Database
    • ALL KB Oracle
    • Oracle 18c Database
    • Oracle Standby Database
  • MongoDB
  • MS SQL Server
  • MySQL
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • MariaDB
Database Organization

Database Organization (DB ORG) is knowledge base for DBA to learn and execute the fundamental of different databases under one website. DB ORG is a non-profit initiative. ORACLE, MS SQL Server, MongoDB, MariaDB, Couchbase

Join Our Community
  • KnowledgeBase
  • Documentation
  • Troubleshoot
  • FAQ
Information Links
  • About DBOrg
  • Licenses
  • Terms
  • Privacy Policy
Contact Us
    DB ORG - Database Administration,
    Knowledge Base for DBA
    Mail: support@databaseorg.com
    WhatsApp: (+91) 9306440957
    Monday to Friday: EST - 11:30 AM to 06:30 PM (IST - 9:00 PM to 4:00 AM)
  • © 2023 Database Organization - DB ORG. All Rights Reserved.

Popular Search:Oracle, SQL Server, MongoDB

WhatsApp DB Org