5
My advice would be to just restore the entire backup. In my current environment, someone accidentally screws up some table once every few months. It just happens. Of course, it always hits our biggest database. 200GB backup file, 1+TB when fully restored. It is just easier to restore a copy somewhere, find the specific table we need, and just update that ...
4
Take a look at the command that is generating the backups (it could be a job, a maintenance plan, 3rd party tool, etc.).
You can give the file any extension you like, including no extension. Windows Explorer might not know what on earth it is, but SQL Server will still be able to restore it just fine.
4
Your option 1, pg_dump | psql does have the limitations you describe.
Your option 2 doesn't make much sense. You need to start out with a clone in order to set up streaming, and by the time you have the clone then you don't need the streaming, as you already have the clone. Also, you don't have to "reconfigure both to disable replication again". When you ...
3
Without using third party software, your best bet is to restore the backups one by one and running your query on the restored databases.
If you're looking for the last backup that contains those records, you can be smart about which databases you recover, so you don't have to recover all of them.
Example, restore the oldest backup, if it contains the ...
3
The way to go is a plain format pg_basebackup.
Just make sure that --wal-method=stream, and the backup will contain all required WAL files.
Then start the server on the backup directory, and it will perform recovery and come up.
There is no need to mess with pg_receivewal.
3
I started taking tlog backups last night, on an existing database (231682) with no activity. I used a while loop and a counter so I could get a running total.
DECLARE @counter int
SET @counter = 1
While 1=1
Begin
BACKUP LOG [231682] TO
DISK = N'G:\SQLBackups\Test_Tlog.trn' WITH NOFORMAT, NOINIT,
NAME = N'231682-Log Database Backup', SKIP, NOREWIND, ...
2
but I can't find any examples online of how to use verifyonly with a
destination to test. How can I do this?
You can use this syntax:
restore verifyonly
from disk = 'V:\SQL_backup\myBak.bak'
with move 'myDb' to 'C:\myDb.mdf', -- put here your destination
move 'myDb_log' to 'C:\myDb_log.ldf' -- put here your destination
In case of insufficient ...
2
Also from the Database Backup and Recovery User's Guide.
The total number and size of backup sets depends mostly on an internal
RMAN algorithm. However, you can influence RMAN behavior with the
MAXSETSIZE parameter in the CONFIGURE or BACKUP command. By limiting
the size of the backup set, the parameter indirectly limits the number
of files in the ...
2
Per this Microsoft documentation:
For backwards compatibility with earlier versions of Microsoft SQL
Server, the recovery model of master can be set to FULL or
BULK_LOGGED. However, BACKUP LOG is not supported for master.
Therefore, even if the recovery model of master is changed to full or
bulk-logged, the database continues to operate as if it ...
1
You are correct.
The way to avoid this is to use pg_receivewal, which streams WAL information from the database server and persists it in WAL segment copies immediately.
By default this is asynchronous, so you could lose some split seconds in case of a crash. It you need to avoid that, you'd have to use synchronous replication with pg_receivewal, but be ...
1
I found last_wait_type is ASYNC_IO_COMPLETION for this backup job. We
have sufficient disk space available.
This blogpost from Paul Randal gives you most of the information regarding this wait stat
...The official definition of ASYNC_IO_COMPLETION is ‘Occurs when a task
is waiting for I/Os to finish....
...Long-duration ASYNC_IO_COMPLETION waits ...
1
I'd just like to know why it didn't save as .bak file. In other servers that I've worked on it's always .bak
This might have happened:
Declare @BackupPath varchar (256),
@DBName varchar (256),
@DateTime varchar (25)
--- With Extension (Other Server)
set @BackupPath = '\\SharedLocation\' + @DBName + '_Full_' + @Datetime + '.bak';
--- ...
1
There should be no other limit than maximum possible file size. The backup file is written in Microsoft Tape Format and new headers are simply appended to the file.
1
Is there a limit to how many backups can be appended to a single file?
3285.
Actually, that just as far as I got. The backup file got to 10GB, and each backup was taking 10 seconds, so I wasn't willing to wait longer.
Using:
use master
go
create database bt
go
backup database bt to disk='c:\temp\bt.bak' with noinit
go 10000
restore headeronly from disk=...
1
The underlying partition was initialized using MBR (master boot record) instead of GPT (GUID partition table), and you've broken the 2TB file size limit.
I'm willing to bet that if you ever replay that log backup, it will fail because the backup would have stopped writing when it reached the 2TB boundary.
You will need to reinitialize the partition using ...
1
As @a_horse_with_no_name wrote pg_dump and pg_restore cope with PostGIS things without problems. At least since PostGIS 2.0, previously (before PostGIS became an extension) you might had to change paths of the c-library used for geometry-functions.
In PostgreSQL since 9.x ist is also fairly easy:
backup at the original databse with pg_dump
at the new ...
1
Like any transaction log restore, sp_AllNightLog needs exclusive access to a database in order to do the restore.
This means you'll run into trouble when:
A user starts a very long-running query
sp_AllNightLog tries to start a log restore, and is blocked
Other users try to start queries, but are blocked by sp_AllNightLog, leading to timeouts and angry ...
1
It depends on your RTO (Recovery Time Objective) and RPO (Recovery Point Objective).
Recovery Time Objective: Time duration to restore the database in case of any disaster.
Recovery Time Objective: The time interval that may pass until the database recovered. (Acceptable time duration for data loss).
Before you decide your recovery model and backup ...
1
Using backup database plus archivelog rman will make several steps:
log switch (creates new archivelog)
backup of archivelogs
backup of datafiles
log switch (creates new archivelog)
backup of archivelogs for the time of db backup
backup of controlfile and spfile;
So here rman will create at least 4 backupsets:
two - with archivelogs, one - with datafiles, ...
Only top voted, non community-wiki answers of a minimum length are eligible
