New answers tagged

1

After you change the recovery model to SIMPLE, perform a full backup, that should truncate your tlog. From MSDN: After Switching to the Simple Recovery Model If you switch from the full or bulk-logged recovery model to the simple recovery model, you break the backup log chain. Therefore, we strongly recommend that you back up the log immediately before ...


2

The best way is not to shrink it at all. You are not losing any data when you shrink the log or when switching recovery models. You are losing recovery points though. But there's a reason the file was sized that way. Backup your transaction logs frequently, such as every minute, to minimize its size. If the size is still bigger than what you'd think, ...


0

Other than the reasons @Tara mentioned, you also need to ensure that your backup succeeds. Usually you need to ensure two things There is no open transaction hungup there in the database using dbcc opentran DBCC OPENTRAN helps to identify active transactions that may be preventing log truncation. DBCC OPENTRAN displays information about the oldest ...


0

Because the size of it is not big enough to handle the size of the transactions every hour. or Something is preventing the log from getting cleared after the log backup completes. Check the log_reuse_wait_desc column in master.sys.databases to see why it isn't clearing out. Consider backing up the transaction logs more frequently than hourly. I ...


1

No, you don't need to prep every backup. You only need to create the full backup on Sunday and then create the incrementals from Monday to Saturday. Full: innobackupex --user=USER --password=PASSWORD /path/to/backup/dir/ Incr: innobackupex --incremental /path/to/inc/mon --incremental-basedir=$FULLBACKUP --user=USER --password=PASSWORD innobackupex --...


1

Since I happened to be researching this myself, here's a summary of what I found. According to a 2015 blog post from the MySQL dev team, the main advantages of mysqlpump are that it can use multiple threads in parallel to speed up the dumping and that it doesn't share mysqldump's backwards compatibility requirements, which should open the door for further ...


1

pg_dumpall --clean will not issue DROP DATABASE statements for databases like template1 or postgres that have been created by initdb at cluster initialization time. However it will dump the contents of these databases if you created any objects in it (by default they're empty). So when restoring the result of pg_dumpall --clean into the same instance, it's ...


0

Working out which Log Backup to restore is all based on LSN's. You have to apply all log backups since the full (or differential, but you didn't mention them) backup, including log backups taken while the full backup was in progress. There is no danger in trying to restore a log backup out of sequence, it just won't work. Kin answered above how to find the ...


0

how do I know which transaction log backups to select? You can find that out using msdb..backupset table. To automate that, you can use RestoreGene - TSQL or PowerShell version This procedure queries msdb database backup history and database file details in master. It builds and returns RESTORE DATABASE commands as it's result set, it does not ...


0

Yes, you need to catalog the backups at their new location. You should run crosscheck also on the backups, so the entries pointing to the old location become EXPIRED.


1

You have not decompressed the Xtrabackup files if they still have the .qp extension. Try using this inside the current folder: sudo innobackeupex --decompress --parallel=4 where 4 stands for the number of threads you would like to use. Also, for clean-up afterwards: sudo find ${DEST_DIR}/ -name "*.qp" -delete


2

Besides being unaware that your backup is being performed as a COPY_ONLY backup instead of not being backed up at all, are there any caveats that anyone can think of if this switch is set to default Y or even if it's used at all? A COPY_ONLY full backup is still a full backup. The only difference between the two is the COPY_ONLY will not reset certain ...


1

You need to check the actual size of the database files which you can check by running RESTORE FILELIST ONLY FROM <backup_device> This will give you information about the files contained in the backup and their sizes in bytes You need the same space available on the server as the database file size, the backups can be compressed. Now you need ...


0

The PITR process is as follows: Set up WAL archiving (via archive_command in postgres.conf) Create file-level backup of your database using some tool (I assume pg_basebackup does this?..) Save file-level backup with WAL archive somewhere Then when you need to restore you: Restore file-level backup into some location Copy WAL archive files into some ...


0

PITR restores forward, not backward. You must use a base backup which was taken before the point in time to which you want to recover.


0

Yes, it is right that the incremental backups are not possible in postgresql. I don't know if there is an alternative for pg_rman in windows but you can achieve the incremental backup in postgres with the continuous archiving. Which can be later restored with PITR (point in time recovery) Details on: https://www.postgresql.org/docs/current/static/...


0

I got 2 comments that hinted me that: This problem might not be reproducible again if I do a second mysqldump. The copy of the file from one place to another might have corrupted the file. So then to ensure (1) was not the case, I tried to mysqldump again, and to make sure (2) didn't happen, I compiled the file before transferring it to another computer (...


0

I am not sure that this is the best way (I would welcome any comments to tell me how it is not), but it's very simple (and quick if you have a small database): First, backup the database to a .bak file. Then, restore the database from the same .bak file, choosing the new .mdf and .ldf file locations under file options for the restore task. I wouldn't do it ...


2

The million dollar question is - is it worth adding an extra server to the windows failover cluster in your current scenario ? IMHO, NO - since you are on sql server 2012. Why ? SQL server 2012 and 2014 will allow read-only routing directed traffic to the FIRST available replica defined in the routing list - provided the first read-only replica is ...


0

We had some problem with tape backups in one of the environments and started Archive bits to make sure that the backup files are not deleted before getting archived. Once the database is backed up in tape Archive bit will be updated and this bit is used to avoid deleting file before tape backups.


2

In a MS SQL Server 2014 AlwaysOn AG setup, I would like to schedule backup jobs for a given availability group. The ultimate goal is to have the regular backups run on the synchronized secondary and, most importantly, not bound to the availability of a particular secondary node. This can be accomplished without issue. Just keep in mind that your ...


1

Ola Hallengren automated backup solution will solve your problem for newly added databases. Check for the MirrorDirectory option: MirrorDirectory Specify one or multiple directories to perform a mirrored backup. The MirrorDirectory option in DatabaseBackup uses the MIRROR TO option in the SQL Server BACKUP command.


1

From here, the complete command for a hot backup of a MySQL database using only InnoDB tables is: mysqldump -u user -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql Note the gap between -u and user and the lack of a gap between -p and pass. This is correct - a quirk of MySQL which has caught me on more than one occasion....


-1

Alright, let's assume you have full backup without archivelog. And your backup is output from rman backup such as rman> BACKUP DATABASE;. So, I'll explain how to restore it. 1. Make sure your DBID is same as your database. (Note : this process is used if you have full backup datafiles,controlfiles,spfile, and copy archivelog if you have.) SQL> SELECT ...



Top 50 recent answers are included