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

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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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.

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

  • 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.

  • 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]

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

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