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.

Pages: 1 2




Related Articles :

36 Responses to “Backing Up a SQL Server Database Directly Onto a Remote Server”

  1. which domain account do we need to give access to

  2. Nicely written code… Keep up with the good work

  3. add try catch to the script so that if one of the backups fails it continues with the rest.

  4. Very nice post. I just stumbled upon your blog and wished to say that I have really enjoyed surfing around your blog posts. In any case I will be subscribing to your rss feed and I hope you write again soon!

  5. It’s spokoy how clever some ppl are. Thanks!

  6. I’m impressed. You’ve really rasied the bar with that.

  7. Thanks for sharing, I was looking exactly for the same.

  8. Nice code. You can do the same with a maintenance plan and specify the databases to back up.

  9. There is one thing to worry about here.
    Should there be a network hiccup, the backup to a UNC path will fail. SQL’s backup doesn’t recover from Network timeouts at all.

    • This is especially true for large databases, like ours, which takes over an hour to backup. The backup will actually be corrupt sometimes. Sometimes it will report as a failure, but actually work. Our backups fail three or four times weekly over a UNC path. And the two servers involved are nodes on the same cluster.

  10. Looks good – thanks for sharing. It may be possible to skip the cursor and use some sort of WHILE looping instead.

  11. Actually interesting content articles. I enjoyed reading through it.

  12. Hi there, You have performed a great job. I will certainly digg it and personally suggest to my friends. I’m sure they’ll be benefited from this site.

  13. Really good Satnam!!!

  14. Many thanks for an incredible publish, would read your others content. many thanks for your thoughts on this, I felt a lttle bit struck by this article. Thanks again! You earn an excellent moment. Displays the beauty through wonderful info here. I believe if a greater number consideration for it that way, they’d have a better time period obtain the suspend ofing the matter.

  15. Hey There. I found your blog using msn. This is an extremely well written article. I’ll be sure to bookmark it and come back to read more of 301 Moved Permanently . Thanks for the post. I’ll certainly comeback.

  16. Wow, marvelous blog layout! How long have you been blogging for? you make blogging look easy. The overall look of your website is great, let alone the content!. Thanks For Your article about 301 Moved Permanently .

  17. Hey There. I found your blog using msn. This is a really well written article. I’ll be sure to bookmark it and come back to read more of 301 Moved Permanently . Thanks for the post. I will certainly comeback.

  18. Very nice post. I just stumbled upon your weblog and wished to say that I have really enjoyed surfing around your blog posts. After all I will be subscribing to your rss feed and I hope you write again very soon!

  19. Oh my goodness! an amazing article dude. Thank you However I am experiencing issue with ur rss . Don’t know why Unable to subscribe to it. Is there anyone getting identical rss problem? Anyone who knows kindly respond. Thanks

  20. Thank you an unbelievable put up, may read your particular others topics. thanks for your thinking for this, I experienced a trifle strike by this short article. Many thanks again! You wanna make an excellent moment. Displays the beauty through excellent info here. I feel that in case more people considered it like that, they’d have got a better moment in time receive the grasp ofing the difficulty.

  21. The best this about electric toothbrush is that it is rechargeable.

  22. Quite Nicely done, effectively written in simple steps.. really really appreciate the effort put in..Thanks Mr. Satnam Singh.

  23. Thanks for an amazing publish, can examine your particular others content. thank you your ideas for this, I felt a bit made an impact to by this short article. Merit again! You wanna make a good time. Has great report here. I believe that in case a greater number of people thought about it like that, they’d have a very better time frame receive the grasp ofing the issue.

  24. I’m grateful you made the post. It’s cleerad the air for me.

  25. Fell out of bed feeling down. This has brighetend my day!

Trackbacks/Pingbacks

  1. alice johnson - February 22, 2012

    bob haircuts

    Hi there, just became aware of your blog through Google, and found that it’s really informative. I am gonna watch out for brussels. I’ll appreciate if you continue this in future. Lots of people will be benefited from your writing. Cheers!…

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |