Microsoft SQL Server: Backing Up a SQL Server Database Directly Onto a Remote Server

Recently I encountered a situation where the backup drive was short of space on the production server. The policy on the production server was that as soon as soon as the Full Backup is complete, a copy of the production backup is transferred to the staging server using RoboCopy and then on the staging server there is a job scheduled which transfers the backup to tape.

I needed to increase the size of my backup drive but this could take several weeks to procure and order the drive. Thus, I needed a method to backup the database while the new drive was being procured. I then decided to try and verify whether my Production DB Backups could be directly taken to my staging servers using T-SQL.

I am taking backups of the database hosted on my Production server (which is named A.A.A.A) directly onto the staging server named P.Q.R.S. My Production and Staging servers are located in different locations but are both in the same domain.

Approach

I decided to write a Dynamic SQL Query to implement the backup. The table named sysdatabases in the master database contains all the information related to the databases hosted on the server.I decided to write a Cursor to implement the required looping. The full code is available for download here, the explanation of the code is as follows:

DECLARE DATABASE_BACKUP Cursor
FOR

The DECLARE CURSOR statement defines the SELECT statement that forms the basis of the cursor. Using this you can perform almost anything you would do in a SELECT statement.

select [name] from master.sysdatabases
where [name] not in(
'tempdb',
'distribution'
)

I decided to backup all the databases on the server excluding tempdb and distribution. As you most likely know, tempdb is never backed up and in our environment the distribution database is not required to be backed up since we were using Transactional Replication for Reporting, and if there are any issues we would have to directly set up the Replication again.

Once have the names of the databases which need to be backed up, the next step is to define a cursor to do the looping:

Open DATABASE_BACKUP

The OPEN statement statement executes the SELECT statement and populates the result set.

DECLARE @Name varchar(100)
Fetch NEXT FROM DATABASE_BACKUP INTO @Name

The FETCH statement returns a row from the result set into the variable. You can select multiple columns and return them into multiple variables.

While (@@FETCH_STATUS <> -1)

The variable @@FETCH_STATUS is used to determine if there are any more rows. It will be 0 as long as there are more rows. We use a WHILE loop to move through each row of the result set.

WHILE(@@FETCH_STATUS<>-1) will ensure that the conditional looping proceeds until we reach the last row of the SELECT statement.

BEGIN
DECLARE @month varchar(2)
DECLARE @date varchar(2)
DECLARE @year varchar(4)
DECLARE @SQL varchar(max)
SELECT @month = DATEPART(MM,GETDATE())
SELECT @date = DATEPART(DD,GETDATE())
SELECT @year = DATEPART(YYYY,GETDATE())
SELECT @SQL ='BACKUP DATABASE '+CONVERT(VARCHAR(100),@Name)+'
To DISK='+''''+'\\P.Q.R.S\DBBACKUPS\'+CONVERT(VARCHAR(100),@Name)+''+CONVERT(VARCHAR(1),'_')+CONVERT(VARCHAR(2),@month)+CONVERT(VARCHAR(1),'_')+
CONVERT(VARCHAR(2),@date)+CONVERT(VARCHAR(1),'_')+CONVERT(VARCHAR(4),@YEAR)+'.BAK'''
EXEC(@SQL)

The variable @SQL contains the Dynamic SQL Query which contains the backup statement. EXEC(@SQL) tells the SQL Server to execute the backup statement individually for each database. Please note that DBBackups is the name of the folder which is present on the staging server P.Q.R.S to hold the DB Backup.Please do not forget to share this folder and give full rights to the Domain Account on this folder.