New answers tagged

0

On a terminal, type systemctl cat 'postgresql*' That will show you where the PGDATA environment variable is pointing to when you start the service normally via systemd, and all the parameters used for starting and stopping the service.


2

A solution for a database set to recovery mode SIMPLE is having the FILESTREAM data in a read-only file group (which isn't your ideal option), and then backing up only the read/write file groups with DIFFERENTIAL like this: BACKUP DATABASE [name] READ_WRITE_FILEGROUPS TO DISK = '' WITH DIFFERENTIAL, COMPRESSION; It will get any data that has changed in ...


1

I feel dirty providing this as an option, but if you choose to segregate the FILESTREAM data into its own database, you could maintain RI between the tables in the separate dbs by way of triggers: Triggers -> Remarks -> Limitations: A trigger is created only in the current database; however, a trigger can reference objects outside the current ...


3

Its easy using T-SQL. I would suggest not to use Maiantenance plan. Instead go with a much flexible and widely adapted TSQL based solution - SQL Server Backup by Ola Hallengren EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES, -DB1,DB2', -- All user databases, except Db1, DB2 @Directory = 'C:\Backup', @BackupType = 'FULL', @Verify = 'Y', @Compress ...


1

This seems to me like the opposite of a problem... Regardless of requirements, I just feel better knowing every database is being backed up. Unless these two databases are quite large and can fill up a significant amount of disk space with backups, what negatives are there to backing up those files?


1

I would check the Management > Maintenance Plans folder, in the Object Explorer pane of SSMS. The other place would be under SQL Server Agent > Jobs, so I'm curious what you meant by, "I was able to find the full backup option on the SQL Agent." If there is a job titled "Full Backup" under the Jobs folder, then that's probably what you're looking for.


2

The problem this caused was that the log file (which also gets backed up) is now unnecessarily large because it includes the FILESTREAM data. I'm not sure if you mean the log file itself is too large or that the log file backups become too large. If it's the former then how often were you backing the log up? Depending on the application design you may ...


4

I've set up option 3, Master Server, a number of times, but never for scheduling backups. I'm able to schedule jobs for performance data collection, pushing out administrative changes/script updates via SQLCMD, and run tests against multiple versions of SQL Server on multiple instances to test for any unexpected changes made after SP/CU application (among ...


0

This was a very helpful link: https://mysqlintheenterprise.com/tag/mysql-enterprise-backup/


2

Their cannot be a comparison drawn as Snapshot is "photo copy" of data file. The difference lies in amount of activity both does and consistency of product arising out of both the operations. Full backup "Is Always" more reliable than snapshot backup Full backup includes all committed and uncommitted transaction when full backup has finished. Read more ...


4

Mostly because a snapshot isn't really a backup. As explained in How database snapshots work The snapshot uses one or more sparse files. Initially, a sparse file is an essentially empty file that contains no user data and has not yet been allocated disk space for user data. As more and more pages are updated in the source database, the size of ...


2

Here's a shell script that can do what you want: SCHEMA="myschema" DB="mydb" psql -Atc "select tablename from pg_tables where schemaname='$SCHEMA'" $DB |\ while read TBL; do psql -c "COPY $SCHEMA.$TBL TO STDOUT WITH CSV" $DB > $TBL.csv done Make sure you set the DB and SCHEMA variables to your particular database and schema. The wrapping psql ...


7

It can be specified in TSQL but the documentation states COPY_ONLY has no effect when specified with the DIFFERENTIAL option. There's no need for copy only differential backups because you can just take a normal differential backup. Taking a differential backup doesn't break anything as each such backup is cumulative and contains all changes since the ...


3

It's not really a requirement. The database itself will work perfectly without backup after switching between ARCHIVE / NOARCHIVE mode. But if something ever goes wrong (or database needs to be copied/cloned to other machine), you'll need a backup. You may or may not use incremental backups feature ; if you do want to use them you must take 0-level ...


3

You cannot use backups from before the switch to ARCHIVELOG mode to restore and recover the database to a point in time after the switch. Looking at this quote it makes sense. If you enable archivelog on a database where it was not enabled, then any backup that you have before archive log was enabled can't be used to do a point in time recover, since ...


0

Turned out to be a minor corruption of the SQL backup file. After re-running the entire process again, the restore worked fine with no errors. You would think that Postgres would have in-built checking to ensure integrity of exported data!


0

Speaking as someone who has taken over from multiple third parties who have done NetBackup database backups you can safely assume three things. Many of your servers and individual databases have not been backed up. You would do best to confirm yourself through msdb checks. The "tapes" being backed up to are probably not accessible, do not work, or ...


0

Assume the case where you make full backups once a week (on Sunday) and transaction log backups every 15 minutes. Then the database failure occurred on Saturday night and now you need to restore your database. In such case, you need to to restore the last full backup that was made on Sunday plus all transaction log backups that you were made from Sunday to ...


0

It depends on you restore time Differential backup helps you reduce the restore time Imagine that each of your logs takes half an hour to restore and imagine that your database fails on Saturday So you have to Restore the full backup of last Sunday + many log backups which will take so much time but If you have differential backup every 6 hours you will ...


3

My problem is what is the use of differential backup every 6 hours if we are taking a transaction log backup every 15 minutes? Differential backup is used to decrease the Recovery time object(RTO), it gives you facility of restoring backups quickly. Differential backup includes all the changes made since last full backup, or full backup which started ...


2

The use of the differential Backup is simple. Instead of restoring a huge amount of log backups in a row, you restore a full, a diff and a small amount of transaction log backups instead. Yes you are correct, you don't need the differential Backup, logically. But your restore will be faster using full + diff + log backup compared to a full restore + ...


0

Instead of using this script I would attempt to use this method. the script below assumes the following: The restored database will have the same name as the backed up database The restored database will be restored in the same location as the backed up database The files have the following naming format dbName_YYYYMMDDHHMM.xxx XP_CMDSHELL is enabled ...


-1

Please check the disk space or you may need to check the running user account on the present SQL Server instance. See my blog post: Ways to Fix SQL Server Error 3201 - Cannot Open Backup Device


0

I have gone through MSDN BOL blog How to backup SQL Server databases to a mapped drive and run the below query. Run the following command from a query window EXEC xp_cmdshell ‘net use <drivename> <share name>’ After that i am able to take backup in Map Drive. where drive name : Letter used to map the drive share name : UNC path to the ...


0

As of >= 1.3.1 Barman supports backup from a standby replica (concurrent_backup). Barman config, e.g. /etc/barman.d/standby.conf looks like this: [standby] description = "Replica of main PostgreSQL DB" ssh_command = ssh postgres@db02 conninfo = host=db02 user=postgres backup_options = concurrent_backup streaming_conninfo = host=db02 user=postgres ...


4

SQL Server can only see network drives that are mapped in the Windows Profile of the SQL Server service account. So you'd have to log on as that account (not your own) and map the drive, for SQL Server to be able to use it for backups. Backups run using the credentials of the SQL Server service account, not yours. As suggested in the comment above, try using ...


0

I think the answer depends on the SQL skill level of the people who will ultimately be responsible (lose their job) if the data can't be recovered in a DR scenario. Most of the time when DBAs are chit chatting you'll hear horror stories of vendor solutions that didn't work, or stopped working, or weren't monitored. And when the data is lost it's lost. I've ...


4

A simple way to identify the backup is by running restore headeronly command. Like RESTORE HEADERONLY FROM DISK = 'D:\Backup\backupfil.bak' ; Look at the output and look at the column backup type. Below are types and there corresponding backups Backup type: 1 = Database 2 = Transaction log 4 = File 5 = Differential database 6 = Differential file 7 = ...


4

Restore Headeronly From Disk = '...' There's only one SQL Server backup format. If this doesn't return anything useful then it's not a SQL Server backup file and may be something else (I can only imagine what; perhaps you'd have to open it in a text editor and see what the first few characters are). It will return a BackupType column: 1 = Database 2 = ...


2

Backups from a normal user are a nightmare. If you're using PostgreSQL on Linux, I'd recommend relying on sudo (and the /etc/sudoers file) to accomplish this, so that a given specific user can sudo -u postgres pg_dumpall <params> with specific parameters, so that's all they can do as user postgres (and therefore have no write access to the database).


0

Found the answer to the original question - the new volume became browse-able after restarting the agent. (But Max's workaround is still great)


3

Download the Google Drive desktop application. When backing up SQL Server, set the destination to the "My Drive" folder created by Google Drive (something like C:\Users\<user>\Google Drive by default) Once a backup is placed in that folder, it will automatically start to sync to Google Drive. Also worth nothing, as per Shawn Melton's comment: You ...


2

No - the MyISAM architecture prohibits hot backups - it uses a locking mechanism. You might be interested to my response in a related thread. Take a mysqldump of your database, change the bit where it says ENGINE=MyISAM to ENGINE=InnoDB (InnoDB uses a MVCC architecture) and then restore - XtraBackup will then perform hot backups. It's relatively easy to use ...


2

+1 for sensibly wanting to get the backups on a separate drive. However, if the new cluster volume is on the same SAN, you're not really accomplishing much. It would likely be much better to run backups to a file share on a totally different system. As a workaround, you can easily add an "Execute T-SQL Statement" task to your Maintenance Plan to manually ...


3

First of all, you can use a pipe mongodump -h sourceHost -d yourDatabase … | mongorestore -h targetHost -d yourDatabase … This reduces the time, as each document read will more or less instantly be restored on targetHost. However, this has the disadvantage that you might run into problems if the procedure is aborted for some reason, for example of a ...


-1

Just a guess that you are looking at the folder on a different computer. Your backup location represents folder where SQL Server instance is running.


0

I have successfully tested using a copy-only backup and the log backups that span the copy-only up to the point in time desired. You do need to have all the log backups. So if you have multiple replicas that you are using to do the backups(a failover happened for instance), you need to make sure and keep track of them. In my testing I simply set things up so ...


1

The final reason for this effect was that the Backupexec process responsible to take regular log backups was still present and running (so IT monitoring did not alert anything), bunt it was kind of internally crashed, so it did not do it's job properly. In order to address this kind of event I decided to add the log file events and it's metadata details to ...


1

When we put the tablespace in backup mode Oracle copies whole changed data blocks into the redo stream in order to make the backup safe from fractured block. Copying whole data block instead of only the change may degrade the performance. Oracle strongly recommends to put the tablespace in backup mode only when required. What Happens When A ...


3

You can use replace into flag (--replace) and where condition (--where) while also omitting the create statement (-t): mysqldump -t --replace --where="id between 901 and 1000" my_db1 table my_table


3

I recommend using Ola Hallengren's backup solution. The 6th question down on Ola's FAQ page walks you through automating the stored procedures with batch files. The steps given in the FAQ can be used for any stored procedure not just his. Below is the example provided from the FAQ site. You can modify that to run your custom stored procedure. sqlcmd -E -S ...


2

It sounds like you've done your homework to a large degree. Clearly you're aware of the log chain implications for COPY_ONLY backups. My advice to you would be to ensure you have a clearly though-out and tested restore plan. Without having tested your disaster recovery plan, you can never be certain it will work, and therefore it's not a plan. If you ...



Top 50 recent answers are included