New answers tagged

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....


0

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 ...


1

Is something like this anything like what you want? This is how to run through all the databases in a server and backs them up. #!/bin/bash USER="your_user" PASSWORD="your_password" OUTPUT="/Users/rabino/DBs" rm "$OUTPUT/*gz" > /dev/null 2>&1 databases=`mysql --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v ...


-1

Jehad Keriaki nailed it. For InnoDB Engine Hot Backup, Percona is having a backup solution called "XtraBackup" which I believe is based on


0

First of all, categorizing the backup into the four mentioned categories is not accurate. You can categorize into (hot, warm, cold), and into (physical, logical). In other words, the backup can be cold and physical, or, hot and physical, etc. It cannot be (physical and logical). Notice please that physical backup is better called "Raw backup", which is a ...


0

Yes it is a hot backup. It is run with the database online, queries operating etc The dump will be consistent and atomic - a snapshot of the database as it looked when you initiated the dump


0

Documents that cannot be dumped are broken in some way or the other. Either they reached the 16MB size limit, contain Null-Strings or other problematic stuff. You have to break down your dump into steps. If I were in your shoes, my preferred strategy would be to make an Update on the document set, assuming that you cannot update what you cannot dump. db....


1

This is what I do to start the mysql daemon manually. I've also included my own my.cnf. By specifying the correct corresponding paths in your system, you should be able to do the same thing. I would also point out that this is a source install, but again, it should apply generally. Start the daemon like this: ./bin/mysqld --defaults-file=./my.cnf ...


1

SQLmojoe has good points in the answer. 2 things that you need to consider : The cert that you used when taking backup should be preserved as is. The cert should not be changed or renewed (even if it is expired). Renewing the cert will change the cert thumbprint and you wont be able to restore the database backup. The user account should have VIEW ...


1

Assuming you have the process down tight (as in scripted, tested, re-tested, and tested with the newest and most junior persons on the team) the only other area that I seen people slip on is accessibility of the key. Most people get that they should not store it in the same place as the backup files or even transport them together. What is sometimes missed ...


2

No, it's not possible to specify a default location. The best way to do this would be to write a shell script (which specifies your location of choice) to handle your backups. You can have DB2 remove old backups (using the AUTO_DEL_REC_OBJ database configuration parameter with proper values for NUM_DB_BACKUPS and REC_HIS_RETENTN), but that will only occur ...


0

The script generated will have the data folder for your 2014 instance. Since the database already exists on the 2014 instance, and the files are already there, the CREATE DATABASE statement will fail. Change the path in the CREATE DATABASE statement to the 2012 data folder.


-1

Yes, Ian is correct here. I am sure your issue is resolve now. Also, archive logs can still exist in Active log path if we set LOGARCHMETH1 to LOGRETAIN correctly . Details at Information Center can give better overview .Hope it helps !


2

I want to know if there is a problem to occasionally start a new incremental backup when the previous one is still running For SQL Server 2000 You would not be allowed to start new differential backup unless the previous one finishes. For SQL Server 2005 and above You can start new differential backup while old one is still running but new one would be ...


0

to decide whether a full backup needs to be taken or a differential backup can be initiated, I would like to know exactly if the db uid and the backup's uid match - only then I want to create a differential backup, otherwise first create a full backup. The closest you can get is to use sp_SQLskillsDIFForFULL - from SQLSkills.com which will tell how much of ...


1

As per the message you got you were trying to restore backup taken from SQL Server 2014 database on SQL Server 2012 which of course is not allowed. So I need to downgrade my backup file, but how can I do it? You cannot downgrade a backup file, neither can you downgrade, inplace, the database from SQL Server 2014 to SQL Server 2012. You only have option ...


0

One can even search and replace each occurrence of Insert into with Insert Ignore into in the mysqldump file.


0

USE MASTER GO GRANT VIEW SERVER STATE TO User ; Here User is , which you are runnig the query in SSMS. For further ref from pinal Blog "SQL SERVER – Error Fix: Msg 300, VIEW SERVER STATE"


1

You should make sure that for the database you're trying to back up, you have at least the db_backupoperator database role assigned to the user that's trying to run the backup. That should be sufficient.


-1

What is happening is simple - read the error message. The user you are connected to the database server with has not enough permissions. Database backup in SQL Server requires quite some permission - you likely only got permissions assigned to the database "as needed" and that is not enough.


3

When I see messages of that nature, I usually blame the redo logs. This is where is LSN is written. The problem may stem from not preparing the incremental backup. What seems to be missing is the use of the --prepare option. What this essentially does is create the backup rolled forward to a specific point in time. This is particularly true if there were ...


2

The scripting answers above will obviously work. But, assuming you have to, have to, have it executed from within the DB (let us know why :) ), the code below (minus the loop, etc.) should get you on your way. You were very close btw. CREATE or REPLACE FUNCTION function_1(rows text) RETURNS void AS $$ begin execute 'COPY ( SELECT * FROM ...


1

Giving an analogy with Excel is comparing apples and oranges. Why ? Excel is not a database as it lacks data integrity. Excel is a pretty nice spreadsheet application and might be a complement to database. SQL Server is a relational database system which allows you to store all your data and provides a mechanism to query it. The important part is "...


5

Under full recovery mode, will a differential backup "break" the previous log backups? No. A differential backup will not break the log chain. Log backups rely on LSNs (Log Sequence Numbers). A log chain is broken when : You take adhoc log backup without using COPY_ONLY option. When you change the recovery mode e.g. from full to simple. When you use ...


4

The feature you want could exist in principle. It would not be efficient with the current database structures (see Max Vernon's answer). SQL Server would have to either maintain a set of diff maps or compare the current DB contents against the full backup that you specify as a base. There are applications that deduplicate large files. You can make two full ...


4

Don't confuse transaction log backups with differential backups, they have different purposes! What you're calling a "differential backup", whereby you "note all the changes to the cells", is in fact a transaction log. A differential backup's purpose is to keep the size of the resulting backup file small by only recording the information that has changed ...


13

A differential backup uses what is called the differential change map to build a list of pages that have been modified since the last full backup. This list is a "differential" list, hence the name of the backup type, and the reason the backup can only ever be restored over top of the associated full backup. Performing a full backup resets the differential ...


1

We have the same issue with Simpana postgres backup. The documentation states: What are all the files that get backed up as part of Log Only backups? Only the transaction log files that are relevant to the current backup cycle are backed up. The entire Log directory content will not be backed up. For example, if you run a log backup at time T1 and ...


5

I would answer your specific question I thought of using backup compression but later came to know it can be used only between same Sql server Editions. Is it TRUE ? NO this is not true. From BOL document about Compression Every edition of SQL Server 2008 and later can restore a compressed backup. You can take backup with compression on any ...


2

You can try something like below in a bash script: #!/bin/bash su postgres for id in `psql -d database -c"select DISTINCT(account_id) from backup_table WHERE migrated = 1 AND account_id IN ";` do psql -d database -c"COPY (SELECT * FROM backup_table WHERE id = "$id" AND status = 1) TO '/var/lib/pgsql/"$id".sql'"; done Then run this in script through ...


3

You will need at least the same amount of space on your new server. If you are restoring it from disk you will also need the space for the backup files in addition to the restored files. The other thing you can check is to see how much free space is available in the physical files. If there is significant free space and you don't anticipate needing that ...


0

Your backup plan seems to be good. Of course, everything depends on: Recovery Point Objective (RPO) in other words – how much data can you afford to lose? And Recovery Time Objective (RTO) if database disaster occurs, how much time are you able to spend restoring a database to its working condition? Note, if you will run your database under the simple ...


1

The easiest to make SQL Server database backup and send it to Dropbox is to use third-party tools, but they are not free. If you are ready to pay I definitely recommend you to use them, something like a_vlad said. Otherwise, use T-SQL Commands to make database backups and then move your backups to dropbox manually.


1

You can try MySql Backup FTP, anyway, we have a good experience of using this tool.


1

Look into having a Master server https://www.mssqltips.com/sqlservertip/2445/sql-server-multi-server-administration/


1

You can use SqlBak to manage multiple SQL backups from the one place. Also, it allows sending backups to different destinations.


5

Only overnight SSIS is making writes, daytime is all reads - I only need daily recovery. You should choose your recovery model based on your business needs : How much data business can loose and at the same time survive ? Based on the above answer, you should carefully choose your database recovery model. In simple terms (not discussing bulk logged ...


2

Ok, my bad. :-/ SQL Server is correct on both accounts. The weekly full backup starts at 6 PM on Sundays, and the daily incremental backup starts at 8 PM. The weekly backup is still running when the daily incremental starts. The daily incremental records a .bak file that is the same size as the full, because ?? all indexes, statistics and so on have ...


-1

yes, you could extract database objects using mysql queries , here is a php script you can : https://davidwalsh.name/backup-mysql-database-php I hope this helps


1

I need to setup full-backup and diff backup. Can I do it on Server C? You can do a copy only full, but unless C is the primary you cannot do a differential database backup. Running below query on server C but it throwing 1 which means I can't take backup on that server. A return value of 1 means it is "preferred" but doesn't necessarily mean you can ...



Top 50 recent answers are included