How to backup SQL Server database on local drive and network drive

Backing up SQL databases regularly is a must-do activity. We can take a backup of SQL database on the local drive and network drive.

We will discuss both

Step 1

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO


EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO


Step 2

Map network drive on your system

Step 3

EXEC XP_CMDSHELL 'net use H: \\BackupServer\LiveDBBackup'

EXEC XP_CMDSHELL 'Dir H:'

Step 4
Execute below query

This backup data and append backup to existing backup file if exists

BACKUP DATABASE MyDB
   TO DISK = '\\BackupServer\LiveDBBackup\MyDB_Backup.bak'

OR
This backup data and overwrite the existing backup file

BACKUP DATABASE MyDB
   TO DISK = '\\BackupServer\LiveDBBackup\MyDB_Backup.bak'
with init;


OR

To make a backup with compressed data, execute below query once on your SQL server and then make backup with backup option 1 or 2 as per your requirement

EXEC sys.sp_configure 'backup compression default', '1'
GO
RECONFIGURE WITH OVERRIDE
GO