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