Red Gate tools include both schema and data compare utilities that can be run against a "source" and "target" database. These tools provide detail comparisons and can generate scripts to make A = B or B = A.
Any Server Specific items (e.g. SIDs in sys.Database_Principals) will be different.
Once the backup is restored to target DB, it will be reflecting the state of source DB at the time of backup. From my understanding, restore is almost a drop and recreate of DB in target server. Normally, if Prod DB is restored to a non-prod - logins/Users accounts may be different, or if any alerts are configured to make any data changes thru triggers/SP's ...
It looks like pg_ctl wants a real terminal for output, which is not allocated by ssh when you simply ask it to run a command. According to the Postgres manual
On Unix-like systems, by default, the server's standard output and standard error are sent to pg_ctl's standard output (not standard error). The standard output of pg_ctl should then be redirected ...
It was recommended that I review the job name for any characters that would resolve to a bad windows file path since I am using a variablized path. This ended up being due to a '/' in the SQL Server Agent Job name.
Use a custom format pg_dump with the -Fc option.
Don't restore permissions and ownership by using the -x and -O options of pg_restore. You will then have to write and apply an SQL script that adjusts the ownership and permissions.
There is no need to change the sequences.
If database is restored on dev server and database backup was not scheduled to run on the dev server meaning no backup file is available for previously running database then, there is no way you can recover the previous database.
You may possibly ask your development team to re-deploy the things again for SQL code. Possibly, this would be learning to ...
In case anyone is still having the same issue
Nothing described here help me to fix my problem, however I was able to workaround the problem performing a backup of my empty database and then magically the add button worked and let me do a restore from my bak file
My environment is:
Windows 10 enterprise, clean installation
SQL Server 2017 express ...
mysql -u root -p
set global net_buffer_length=1000000; --Set network buffer length to a large byte number
set global max_allowed_packet=1000000000; --Set maximum allowed packet size to a large byte number
SET foreign_key_checks = 0; --Disable foreign key checking to avoid delays,errors and unwanted behaviour
Mine went from 2.5 mins to open, to 1-2 seconds.
MSDB.dbo.backupset keeps a log of backups (separate to what's inside backupfiles themselves).
where database_name = 'Your-DB-Name-Here'
To clean up:
If you run pg_dump with the --clean option then the generated SQL dump would contain drop statements for all dumped objects (note that when using the custom format and pg_restore you don't need to decide on this when dumping, but when restoring).
However if the target database contains tables (or other objects) that were not contained in the source, those ...
my plan was to remove all the secondary replica from the availability group and then set the primary database offline.
Restore the production database into a staging server, then complete the migration/conversion
This seems like a really reasonable plan. You'd likely have lots of issues trying to load 100 GB+ of data to the AG quickly, especially if they ...