SQL database back up everyday

SQL database back up everyday

To backup to a remote location, you can use the IP of the remote location. For example

\192.168.25.41publicbackup

Where the public is a shared folder name and backup is the name of the backup.

OR

You can use a Tape for your backup since you have files coming everyday

OR

You can use an External/ Internal  hard disk of about a terabytes connected to a central system.

To do this you need to create a network share in a local or remote machine

You will be creating a network share in your local desktop or remote machine. With the use of this remote share you can take backup directly from SQL Server

Create a network share in your local desktop machine or remote machine
Provide SQL Service account Full privilege (i.e modify privilege for SQL Server ) so that it can read & write data to the share. If you miss this step then you will be ended with OS error 5 and backup command will fail.
Let's say I need to backup image database to my PC then I'll create a network share called remote backup and use it to take img db backup

BACKUP DATABASE img TO DISK='sagarpcremotebackupimg.bak'

Advantages

SQL Server will take care of entire backup and copy operation.
No server network share is required.

Disadvantages

Prone to network fluctuations,  if you are in middle of the backup and the network distort then the backup gets cancelled and you will need to start all over.
SQL Backup time is directly proportional to network bandwidth
If your SQL Service runs in Local system service account then you can't use this method

Creating Network Share in SQL Server (server level)

You will take the backup in the server itself and then make the backup path as network share and using this network share you will copy the backup file to your desktop.

Create a network share on the server.

Make sure the login used to copy the backup has necessary permission (only read is enough) in the server network share
Once you are ok with this start taking the backup. We will consider the same scenario for copying the image DB, first I'll take the backup to the server local drive (the folder which is made as server network share, in this case I've shared D: Backups folder), then Ill manually map the share from my desktop to copy it.

BACKUP DATABASE Pubs TO DISK='D: BackupsPubs.bak'

Advantages

SQL Server doesn't need to wait for network delays.
SQL Backup time is not directly proportional to network bandwidth.
You can use this method even if SQL Service used Local system account.

Disadvantages

You need to manually copy the backup to the local machine.
Incase if you have a space constraint in the server and if the db is large then you can't take the backup in the server.