Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


Powershell script to backup all DBs on MS-SQL 2019 Std.
New on LowEndTalk? Please Register and read our Community Rules.

All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.

Powershell script to backup all DBs on MS-SQL 2019 Std.

Does anyone have a script handy to dump all user DBs on an MS-SQL 2019 Std. instance locally on the server ?

I'd prefer it to be Powershell if possible but sqlcmd raw commands would work too... just need to make sure dumps are integral and 100% restorable.

Many thanks

Thanked by 2vpsGOD Edmond

Comments

  • umeume Member

    My recommendation for MS-SQL is: https://ola.hallengren.com/sql-server-backup.html

    Thanked by 2mehargags raindog308
  • Why not use the internal backup feature in MSSQL and schedule it.

  • @simonindia said:
    Why not use the internal backup feature in MSSQL and schedule it.

    I've heard... can it internally rotate backups too or do I rotate manually ?
    Any good guides you can point me to ?

  • mehargagsmehargags Member
    edited July 2020

    @ume said:
    My recommendation for MS-SQL is: https://ola.hallengren.com/sql-server-backup.html

    I checked and downloaded "DatabaseBackup.sql" but I just could not understand how to "run" this .SQL script from PS/Command. Any pointers ?

    EDIT: Or does it just setup the built in stored procedures inside MS-SQL so the backups are created by sql agent (like @simonindia pointed) ?

  • jsgjsg Member, Resident Benchmarker

    @mehargags

    Script? You need no script. just use mysqldump

  • @mehargags said:

    @simonindia said:
    Why not use the internal backup feature in MSSQL and schedule it.

    I've heard... can it internally rotate backups too or do I rotate manually ?
    Any good guides you can point me to ?

    https://solutioncenter.apexsql.com/how-to-backup-multiple-sql-server-databases-automatically/

    follow the second method start at topic "Create a maintenance plan to back up selected databases"

  • BharatBBharatB Member, Patron Provider
    edited August 2020
  • Since you're looking for a PowerShell solution, this should be a good starting point for you:

    https://www.sqlservercentral.com/articles/backing-up-sql-server-databases-is-easier-in-powershell-than-t-sql

    Of particular interest, is this one liner:

    Get-SqlDatabase -ServerInstance localhost | Out-GridView -PassThru | Backup-SqlDatabase -CompressionOption On

    Now, since that's an interactive command, you can change the "Out-GridView -PassThru" part to a where block instead.
    Lastly, what I suggest is rather than just piping directly to Backup-SqlDatabase, do a foreach statement, so you can specify the backup name.

  • raindog308raindog308 Administrator, Veteran

    @jsg said: Script? You need no script. just use mysqldump

    MS-SQL is Microsoft SQL Server not MySQL.

    I'm not a SQL Server DBA myself, but the SQL Server DBA pros I know speak very highly of Ola's scripts and they're widely used in the industry.

    @simonindia said: follow the second method start at topic "Create a maintenance plan to back up selected databases"

    That's the way I was taught a long time ago when I took a SQL Server 2008 class.

    Thanked by 1simonindia
  • jsgjsg Member, Resident Benchmarker

    @raindog308 said:

    @jsg said: Script? You need no script. just use mysqldump

    MS-SQL is Microsoft SQL Server not MySQL.

    Oops, you are right, I misread.
    But I'm quite confident that something similar exists for MS-SQL too.

  • raindog308raindog308 Administrator, Veteran

    @jsg said: But I'm quite confident that something similar exists for MS-SQL too.

    Oddly, no. The "big boy" databases do not (to my knowledge) have simple dumps. I'm only familiar with Oracle, really, but the backup technology there is very different than just "write a script that can recreate the DB through SQL statements".

    e.g., in Oracle you're writing a binary file that contains blocks (which makes changed-block tracking for incrementals, etc. easy), or you are exporting objects in Oracle's proprietary format (which I believe is also binary).

    It's obviously easy to write a script that writes out SQL statements (there are database procedures inside the DB you can call to do that if you want) but it's not the norm.

    I believe SQL Server is similar. All of these systems use more complex transaction logging systems and are engineered around backing up to enable precise point-in-time recoveries, backing up while the DB is live, full/incremental schemes, or at least in Oracle's case enabling flashing back the DB instantaneously to earlier points in time (sometimes virtually so you can "see" the data as it was while the main data is still changing).

    In my experience, 90%+ of DB environments don't need these features but those that do must have them. This applies to a wide range of DB-related technologies (replication, disaster recovery, etc.)...all much more advanced in the closed-source databases, but 90% of enterprises won't need these features.

    Well, there's my the-former-DBA-in-me gushing for the day.

    Thanked by 1jsg
Sign In or Register to comment.