Questions tagged [backup]
Making copies of data which may be restored after a data loss event or to recover data from some earlier point in time.
How do you mysqldump specific table(s)?
How can I dump a specific table or set of tables without including the rest of the db tables?
How can I optimize a mysqldump of a large database?
I have a symfony application with an InnoDB database that is ~2GB with 57 tables. The majority of the size of the database resides in a single table (~1.2GB). I am currently using mysqldump to ...
How can I move a database from one server to another?
How can I move MySQL tables from one physical server to another? Such as this exact scenario: I have a MySQL server that uses innodb table and is about 20GB size. I want to move it to a new server, ...
How do I move SQL Server database files?
I have a database and want to move the .mdf and .ldf files to another location. But I do not want to stop the MSSQLSERVER service, and I do not want to export to another server. How can I do this?
MySQL any way to import a huge (32 GB) sql dump faster?
I have this huge 32 GB SQL dump that I need to import into MySQL. I haven't had to import such a huge SQL dump before. I did the usual: mysql -uroot dbname < dbname.sql It is taking too long. ...
Safest way to perform mysqldump on a live system with active reads and writes?
I'm not sure if this is true but I remember reading if you run the following command in linux mysqldump -u username -p database_name > backup_db.sql while reads and writes are being made to a ...
Restore mysql database with different name
How can we restore mysql database with different name from mysqldump file. I dont't want to open dump file and edit it. Any other better methods?
.bak file not visible in any directory in SSMS
I have a .bak file created today by someone else, manually created through SSMS 2008 R2. I'm trying to manually restore the database, unfortunately the file isn't appearing when I go to browse it. I ...
How to get a working and complete PostgreSQL DB backup and test
I was hoping I could get a clear answer on how to ensure taking a full Postgres backup just like you would get with MS SQL Server, and then take care of orphaned users. From what I've read, and it ...
After moving database (backup, restore), I have to re-add user
I occasionally move a database (SQL Express 2012) from a development machine to a server, or vice versa, using database backup and restore in SQL Server Management Studio. Whenever I do this, the ...
Why is a .bacpac file so small compared to a .bak file of the same database?
I've been doing backups of my SQL Server 2014 Express databases for import to other servers and noticed a difference in file size between .bacpac and .bak. Why is a .bacpac file so small compared to ...
Dump only the Stored Procedures in MySQL
I need to dump only the stored procedures : no data, no table creation. How can I do this using mysqldump?
Is it possible to mysqldump a subset of a database required to reproduce a query?
Background I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research). Question ...
How to restore a "contained" database? [closed]
I recently tried to restore to my local development SQL Server a backup from a network instance. To my surprised I received the following error message: Msg 12824, Level 16, State 1, Line 3 The ...
Backup only one schema and restore it on another server
I have a database named "A" which has two schemas "B" and "C". I want to backup and restore Schema "B" (including data) on a different server. Not sure how to ...
mysqldump vs mysqlpump
I just heard about mysqlpump - it looks like it was released with MySQL 5.7, however MySQL 5.7 still includes mysqldump. They're both backup programs, but could anyone summarize the main differences? ...
pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists
I am using pg_dump / pg_restore to backup and restore a PostgreSQL database, but am getting some error messages (and a non-zero exit status) from pg_restore. I tried a super simple base case (outlined ...
The smallest backup possible ... with SQL Server
Daily we ship our SQL Server backups across the WAN. We need to minimize the size of these backups so it does not take forever. We don't mind if our backup process takes a bit longer; as it stands ...
Setting BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE for BACKUP command
I am looking for practical guidance for setting the values for the BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE of the BACKUP command. I have done a bit of research (see below), I have done a bit of ...
media family on device is incorrectly formed. SQL Server cannot process this media family
I am trying to restore a .BAK in SQL server but get the following error: Msg 3241, Level 16, State 7, Line 1 The media family on device 'c:\glyn\JA.bak' is incorrectly formed. SQL Server cannot ...
DBA first day in a new job - check backups and security - How? what else should be checked?
Generally when I start in a new environment, I tend to check where are the backups, when the last full was taken, when was the last restore applied and I check the security too. The way I do this is ...
Restoring a backup to an older version of SQL Server
When trying to restore a backup to a SQL Server Express 2008 database I got the following error: Restore failed for Server '...\SQLEXPRESS'. (Microsoft.SqlServer.SmoExtended) System.Data.SqlClient....
What is the recommended way to backup a MySQL/Amazon RDS database to S3?
I have two purposes for this: To have an offsite backup in case of a region wide problem with Amazon Web Services. To copy production data from a production billing account to a beta billing account. ...
Mongodb incremental backups
I was given the task to set up incremental backups for MongoDB replicaset, as start point, of course, I googled it and could not find anything on MongoDB docs, I did find however this question on ...
Difference between Full backup and Copy-only full backup
I saw in the SQL Server Central thread Does a full backup truncate the log? that full backup does not truncate the log: No. Neither Full or Differential backups truncate the transaction log. - Lynn ...
Security implications of restoring a backup from an unknown source?
Scenario: You're handed a database backup and told to restore it to a server (that's already hosting other databases), but are given no useful information about what the backup contains or whether the ...
I/O requests taking longer than 15 seconds
Usually our weekly full backups finish in about 35 minutes, with daily diff backups finishing in ~5 minutes. Since tuesday the dailies have taken almost 4 hours to complete, way more than should be ...
How can I determine the size of a dump file prior to dumping?
How can I determine or estimate the size of the SQL dump file prior to using something like mysqldump?
pg_restore: [archiver] did not find magic string in file header
I'm using PostgreSQL 9.1 and want to restore backup files generated with pg_dump: sudo pg_dump -h 127.0.0.1 -U postgres --clean --inserts -E UTF8 -f out.sql database_name This command generates a ...
What is the use of "Backup set will Expire:" option while creating Backup Database task in Maintenance plan
While backing up the database, I would like to use "Backup set will Expire:" option to delete/overwrite old database backups. I am not able to use this option. Any advice on how to use "Backup set ...
Is it possible to restore sql-server bak and shrink the log at the same time?
We have a bak file from a client that we have transferred to our developer offices for problem investigation. The backup is currently 25GB and the restored database is about the same size however it ...
mysqldump with INSERT ... ON DUPLICATE
I want to merge data from one database to another. So I create dump with mysqldump and then import it to another database (with same tables structure). I don't have any problems (such as duplicate ...
How can I mysqldump all databases except the mysql schema? [duplicate]
I want to backup all 200+ databases on a MySQL server to all-dbs.sql. I want to exclude the mysql schema. How can I do that?
Any option for mysqldump to ignore databases for backup?
We have 40 databases in our server. We want to take 36 databases backup using mysqldump. How can i ignore remaining 4 databases in mysqldump command? Is there any option for mysqldump to ignore ...
How can I create readonly user for backups in PostgreSQL?
Is it true that it is IMPOSSIBLE to create a readonly backup user in PostgreSQL? I've been advised on an IRC channel that you simply can't have a backup only user with no ownership privileges. I ...
Running pg_dump on a hot standby server?
Disclaimer: I admittedly haven't tried this yet, but I'm not sure I would know if it wasn't working correctly, so I wanted to ask. I would like to run a nightly backup job (via pg_dumpall) from a hot ...
Should I stop transaction log backups during a full backup?
I have two SQL Agent jobs which are scheduled to run at different intervals. The first job runs a full backup once a day. The second job runs transaction log backups every fifteen minutes. As the ...
SQL Server 2017 crashes when backing up because filepath is wrong
I was trying to restore my database and SQL Server kept crashing. I would get a message in SSMS that said there was a network transport error (the connection dropped bc the crash). I checked the logs ...
MySQL database freezing after 'use'
I recently made a rather stupid mistake and corrupt my Ubuntu installation. To resolve it, I booted with a live cd and copied the database files over. However, now I have copied the folders from the ...
SQL-Server: Why do we use FILE = 1 in scripts for restoring a database
Recently when I came across the script for restoring a database, I got a doubt on why we have to use "FILE = 1"? Can't we restore a database without that statement!? Basically, what it is used for?
How often to backup the master database?
The suggestion in BOL is fairly vague: Back up master as often as necessary to protect the data sufficiently for your business needs. We recommend a regular backup schedule, which you can ...
Get postgres snapshot from Amazon RDS
I can not find a simple way to get a copy of the nightly backup I take of my Amazon RDS postgres instance so I can restore it on my local machine to play with. Right now I'm doing a pg_dump when I ...
Taking backup of all the databases in SQL Server
I have a Microsoft SQL Server 2005 DB server. In the DB server, I have around 250 user databases. I have to take a back up of all these databases. Since, manually taking backup consumes lot of time, I ...
What's a good SQL Server backup schedule?
There seems to be a plethora of information regarding the process of setting up backup tasks, but not much information about the big-picture view of database backups. At least, it's hard to formulate ...
mysqldump Error 2013
I have a database installed, that I would like to backup in mysql. The problem is mysqldump fails on exporting the 'maia_mail' table # mysqldump -u root -p maia > maia.sql mysqldump: Error 2013:...
How to backup large MongoDB database
What is the recommended way to back up large data sets in MongoDB? Let's say we have a data size in the order of 10TB - how would you back that up? We're considering a hidden, possibly delayed, ...
PostgreSQL 9.1 Hot Backup Error: the database system is starting up
I have been working on a hot backup for Postgres 9.1 for awhile and have run into a consistent issue. After restarting Postgres on the slave server the pgstartup log file and the daily log file under ...
How can I backup a large SQL Server database without impacting performance?
We've had reports of queries running slowly or timing out early in the morning, and the only job I see running that I think could affect this is our database backup job. The database itself is about ...
How to do incremental backup every hour in Postgres?
Trying to do an hourly incremental backup of a single Postgres server (Win7 64). I have the following setup in postgresql.conf: max_wal_senders = 2 wal_level = archive archive_mode = on ...