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.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
Comments
My recommendation for MS-SQL is: https://ola.hallengren.com/sql-server-backup.html
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 ?
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) ?
@mehargags
Script? You need no script. just use mysqldump
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"
https://gist.github.com/AdamLJohnson/c336bb6d28131f8fc2aa
This should be helpfull
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:
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.
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.
That's the way I was taught a long time ago when I took a SQL Server 2008 class.
Oops, you are right, I misread.
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.