backuppg-dumppostgresqlpostgresql-9.4
I have 100 databases in my cluster. I need to backup 30 databases (as single file or multiple file, anything is fine). I could do it for tables using pg_dump with -t.
Is there any way to do something like this (pseudocode):
pg_dump -d db1 -d db2 -d db3 -Fc > dbs_backup.backup
No you don't need to install the Ola's solution again since the initially installed commandexecute.sql and databasebackup.sql would have been saved on master DB as SP.
You just need to create a new job , as one created early but this time for 5 left over DB;s and schedule it weekly per you're timings.
EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',-- Name of those 5 DB's would come here
@Directory = 'C:\Backup',-- You're backup path
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y'
You can use above script while creating the T-SQL job and schedule it accordingly.
More details on how to do above refer to schedule job
Also, just to make sure both, initial backup and this newly created backup job not to run on same time you can put a condition or per the analysis when you're daily backup job completes, you can schedule this new weekly/nightly backup job for remaining 5 DB's
I would highly recommend you to ditch tsql and adapt PowerShell. It specifically adds more value to what you are doing .. Make sure you use Backup compression and instant file initialization on source and destination server instances.
Copy-SqlDatabase from dbatools.io
e.g. Usage :
Copy-SqlDatabase -Source sqlserver2014a -Destination sqlcluster -Exclude Northwind, pubs, AdventureWorks -BackupRestore -NetworkShare \fileshare\sql\migration
Best Answer
pg_dumpcan only dump a single DB. The manual:pg_dumpall, on the other hand can only dump all DBs in the cluster, even if you provide a specific DB name in the connection string. The manual:To dump some DBs but not all, use a shell script, like has been advised. You may want to include commands to create each DB in the dump: