Backing Up a SQL Server Database Directly Onto a Remote Server
The below statement tells the SQL Server that once the backup of first database is complete proceed with the second and so on. This procedure continues until we have reached the last database present in the sysdatabases table in the master database.
FETCH NEXT FROM DATABASE_BACKUP INTO @Name END
The next statement tells the SQL Server to release the row set.
CLOSE DATABASE_BACKUP
The below statement releases the resources asociated with the cursor.
DEALLOCATE DATABASE_BACKUP
If there are any suggestions for improvement please leave a comment or contact me directly at singhsatnam84@yahoo.com.
Pages: 1 2
which domain account do we need to give access to
the account running the sql server job agent. if you use network service add the computer account since it uses the computer account. If you use local service you can not backup to shares.
Hi Suresh,
that´s quite easy.
You should run SQL Server with a dedicated service account (e.g. user account from Active Directory with no additional rights).
Afterwards you grant this user the needed NTFS permissions on the backup loication (e.g. modify rights on the backup folder)
Regards
Dirk
That’s way more celver than I was expecting. Thanks!
Your story was ralely informative, thanks!
Nicely written code… Keep up with the good work
Please keep trhowing these posts up they help tons.
add try catch to the script so that if one of the backups fails it continues with the rest.
Shoot, who would have tohguht that it was that easy?
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!
It’s spokoy how clever some ppl are. Thanks!
I’m impressed. You’ve really rasied the bar with that.
Thanks for sharing, I was looking exactly for the same.
Nice code. You can do the same with a maintenance plan and specify the databases to back up.
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.
Looks good – thanks for sharing. It may be possible to skip the cursor and use some sort of WHILE looping instead.
Actually interesting content articles. I enjoyed reading through it.
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.
Really good Satnam!!!
You demonstration is simple, clear easy to follow and understand and the query execution is fast great Satnam Singh
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.
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.
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 .
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.
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!
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
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.
Hope to hear moer from you!
The best this about electric toothbrush is that it is rechargeable.
Quite Nicely done, effectively written in simple steps.. really really appreciate the effort put in..Thanks Mr. Satnam Singh.
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.
Mhm, nice layout
.
I’m grateful you made the post. It’s cleerad the air for me.
Fell out of bed feeling down. This has brighetend my day!