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

  • Daniel Calbimonte

    SSCarpal Tunnel

    Points: 4742

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

  • Jeff Moden

    SSC Guru

    Points: 995161

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Aaron Nelson


    Points: 1837

    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


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

  • Daniel Calbimonte

    SSCarpal Tunnel

    Points: 4742

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

  • Michael Meierruth


    Points: 10051

    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


    Points: 10963

    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.


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

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

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