11 Tips to Backup databases with SMO, VB, C# Powershell, Command lines

Question

11 Tips to Backup databases with SMO, VB, C# Powershell, Command lines

Daniel Calbimonte

SSCertifiable

Points: 6063
More actions
August 14, 2012 at 12:38 am

#265829

Comments posted to this topic are about the item 11 Tips to Backup databases with SMO, VB, C# Powershell, Command lines

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 1

Nice summary of backup methods, Daniel. I also like that you took the time to demonstrate how you can schedule backups for SQL Express. Well done.

As an off subject sidebar, let's hope that SMO doesn't go the way of the Dodo bird as did DMO.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Aaron Nelson SSCommitted Points: 1898 More actions · Answer 2

Nice write-up Daniel. 🙂

For the PowerShell portion, I would highly recommend using the Backup-SqlDatabase cmdlet that came with SQL Server 2012. It's much simpler to just backup your databases with a simple:

dir SQLSERVER:\SQL\LocalHost\Default\DATABASES\ | Backup-SqlDatabase

Cheers!

@SQLvariantI have a PowerShell script[/url] for you.

Daniel Calbimonte SSCertifiable Points: 6063 More actions · Answer 3

Thanks a lot for the feedback is pretty cool to write in sqlservercentral ! 😎

Michael Meierruth SSChampion Points: 10051 More actions · Answer 4

I do backups frequently from my computer which is different from the server on which the database resides. But I want the backup to end up on my computer. This requires making use of a special kind of net share. The BAT file below shows what I mean.

set backupdir=C:\Backup

set database=MYDATABASE

set sqlserver=SQLSERVER

set sqluser=SQLUSER

set sqlpassword=SQLPASSWORD

echo backup database %database% to disk='\\%computername%\backup\%database%.bak' with stats=1 > backup.sql

erase %backupdir%\%database%.bak

net share backup /d

net share backup=%backupdir%

sqlcmd -S %sqlserver% -U %sqluser% -P %sqlpassword% -d %database% -i backup.sql

net share backup /d

erase backup.sql

On occasion, I have to use my computer's IP address. A non-server's IP address can change (for various reasons) and getting the IP addess is just a little tricky - but not impossible.

quackhandle1975 SSChampion Points: 11055 More actions · Answer 5

Many ways to skin a cat! Empowering to know as many ways as possible to perform a set task or tasks then see which is the best fit.

Good article.

qh

[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]