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.
2,662
questions
525
votes
4
answers
979k
views
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?
- 5,385
203
votes
7
answers
282k
views
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 ...
- 4,179
163
votes
13
answers
318k
views
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, ...
- 1,791
154
votes
12
answers
424k
views
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?
user2645263
120
votes
5
answers
190k
views
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. ...
- 1,303
106
votes
6
answers
172k
views
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 ...
- 1,195
60
votes
8
answers
125k
views
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?
- 1,466
60
votes
7
answers
97k
views
.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 ...
- 2,216
60
votes
1
answer
99k
views
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 ...
- 7,448
57
votes
6
answers
121k
views
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 ...
- 755
56
votes
3
answers
34k
views
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 ...
- 663
48
votes
1
answer
70k
views
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?
- 723
46
votes
7
answers
91k
views
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
...
- 3,092
45
votes
2
answers
27k
views
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 ...
- 1,299
41
votes
5
answers
112k
views
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 ...
- 511
40
votes
3
answers
20k
views
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? ...
- 541
40
votes
6
answers
96k
views
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 ...
- 557
38
votes
6
answers
6k
views
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 ...
- 1,094
37
votes
1
answer
23k
views
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 ...
- 67.3k
36
votes
8
answers
237k
views
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 ...
- 461
35
votes
2
answers
7k
views
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 ...
- 15.4k
34
votes
5
answers
161k
views
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....
- 1,525
34
votes
3
answers
48k
views
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.
...
- 443
34
votes
1
answer
26k
views
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 ...
- 441
33
votes
5
answers
81k
views
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 ...
- 3,113
32
votes
8
answers
3k
views
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 ...
- 4,703
31
votes
2
answers
13k
views
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 ...
- 1,465
30
votes
2
answers
21k
views
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?
- 425
29
votes
4
answers
43k
views
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 ...
- 393
29
votes
4
answers
74k
views
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 ...
- 447
28
votes
3
answers
38k
views
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 ...
- 1,531
28
votes
1
answer
37k
views
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 ...
- 455
27
votes
1
answer
46k
views
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?
- 373
26
votes
10
answers
77k
views
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 ...
- 697
26
votes
6
answers
33k
views
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 ...
- 409
25
votes
3
answers
17k
views
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 ...
- 461
25
votes
3
answers
8k
views
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 ...
- 2,609
25
votes
1
answer
2k
views
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 ...
- 415
23
votes
3
answers
33k
views
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 ...
- 395
23
votes
1
answer
23k
views
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?
23
votes
3
answers
6k
views
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 ...
- 2,609
22
votes
1
answer
10k
views
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 ...
- 351
21
votes
8
answers
56k
views
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 ...
user1493004
21
votes
1
answer
13k
views
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 ...
- 313
20
votes
5
answers
54k
views
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:...
- 301
20
votes
2
answers
24k
views
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, ...
- 303
20
votes
4
answers
101k
views
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 ...
Jen
20
votes
4
answers
43k
views
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 ...
- 8,377
20
votes
2
answers
22k
views
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
...
- 8,071