New answers tagged

0

The backup is safe to create, but not to use to restore and start the database, the gist being the buffers and cache and in flight transactions being processed. I won't spend time on more details as others have provided adequate links. However while not your exact request understanding rsync backup logic could still be helpful to you. For instance this on-...


0

Since barman 2.4, there are two option for after recovery behavior. They are --target-action option, accepting the following values: shutdown: once recovery target is reached, PostgreSQL is shut down pause: once recovery target is reached, PostgreSQL is started in pause state, allowing users to inspect the instance promote: once recovery target is ...


0

Well. try following barman command instead of fixed timeout: barman cron barman switch-wal <server-name> I face the same issue when add a new server in to barman backup config. It alwsy happened. After check stackoverlog and barman docker, I noticed that if the datbase base is not very busy, there will no wal(xlog)generated during the new server ...


0

I've done this before using ASCII art. I don't have the code to hand but this is the gist of it. We have a table (or view) of job names with start and end times. The interval of interest is defined by two variables - @IntervalStart and @IntervalEnd. The output will be an A4 sheet. A Word document using Courier New 10 pt and default margins has a width of ...


2

The Agent Log is limited by the number of entries, not by a date. The default is 1000 rows, with 100 rows per job. You can see this via Right-click SQL Server Agent in SSMS -> Properties -> History. So, if you've had fewer than that number of events since January, the history will go back further. You can change that limit in the dialog noted above. Many ...


9

I just validated it. Yes. Not only is it possible, other than using the WITH MOVE to change where the files are located, there's literally nothing special involved. I tested it using a shared volume to get the backup out of my docker instance. I also tested it using the docker file copy command. Both worked just fine. It's possible that the backup is corrupt ...


1

You might use below script to get history of backups in last 1 month: SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.expiration_date, CASE msdb..backupset.type WHEN 'D' THEN 'Database' WHEN 'L' THEN '...


2

It's kind of expected assuming how incremental backups work. Xtrabackup knows the last LSN (a point when a full copy was taken). Xtrabackup reads all pages. Every page has an LSN in its header of the latest modification: if a page is newer than the last LSN - save it. if a page is older than the last LSN - skip it. So, in the end of the day xtrabackup ...


0

DECLARE @BackupCount BIGINT, @GetSPid BIGINT = NULL, @KillCommand NVARCHAR(1000)=N'', @mailfrom NVARCHAR(100)=N'', @subject NVARCHAR(100)=N'', @mailbody NVARCHAR(MAX)=N'', @JobName NVARCHAR(100)=N'', @tableHTML NVARCHAR(MAX)=N'', @tableHTML1 NVARCHAR(MAX)=N'', @Table1 NVARCHAR(MAX)=N'' --SELECT @jobName = name FROM msdb..sysjobs WHERE job_id = $(ESCAPE_NONE(...


2

As long as the backups are not actively being written to, you can delete, copy, or move them without impacting SQL. You can do this to .bak or .trn or .dif Beware that during the backup process you can use any extension you want like .WhatEver It doesn't really mater what extension they have, same rules apply. If the files have extensions .mdf or .ndf ...


6

In general, yes, you can move the backup files. Backup files are not in use by SQL Server once the associated backup is complete. .BAK files are typically full or differential backups, and .TRN are typically transaction log backups, and they can all be moved. It is certainly possible to use different file extensions for the backups, but it's not common. ...


0

You must add unicity variable in the backup format name: backup incremental level 0 format 'F:\DATABASE_BACKUP\%d\%Y-%M-%D\FULL_%d_%T_%t_%U' database ; As your backupset is composed of multiple backup pieces, they must have different names.


0

AH User error. Apparently the certificate was on the server! Dropping the cert caused the backup to fail! My bad!


2

Then if we have an outage, can I do a faster restore by restoring the VM image? No. See my answer here. Is it possible to restore a database with database backups starting on top of a restored server point in time snapshot that is out of date? You can't attach a database without crash recovery, and you can't perform a crash recovery without the ...


3

can I use the database backups that have occurred (on the original server) after that full server snapshot was generated, to bring the newly restored server up to date? No. You cannot manually switch an ONLINE database to RESTORING and apply log backups, and you cannot attach a database and leave it in the RESTORING state. This would be a useful feature,...


0

Snapshots are not backups. For protecting against crashes and for restoring purposes in case of a failure you need backups. The frequency of the backups depends on RPO,RTO,SLA-s. Database snapshot is not a full copy of the database and it resides on the same server instance as the source database. You cannot restore from a snapshot if the database is ...


1

The differential_base_time and lsn columns in sys.database_files indicate the point in time and in the log stream when the last non-copyonly backup was taken. So: select min(differential_base_time) last_full_backup_utc, datediff(second,min(differential_base_time),SYSUTCDATETIME())/( 24*60*60.) days_since_last_full_backup from sys.database_files ...


Top 50 recent answers are included