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.

Filter by
Sorted by
Tagged with
510 votes
4 answers
939k 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?
user avatar
  • 5,235
200 votes
7 answers
268k 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 ...
user avatar
  • 4,099
160 votes
13 answers
300k 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, ...
user avatar
  • 1,761
148 votes
12 answers
407k 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?
user avatar
114 votes
5 answers
169k 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. ...
user avatar
  • 1,243
103 votes
6 answers
164k 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 ...
user avatar
  • 1,165
59 votes
7 answers
91k 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 ...
user avatar
  • 2,206
58 votes
8 answers
118k 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?
user avatar
56 votes
1 answer
92k 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 ...
user avatar
  • 7,353
56 votes
3 answers
31k 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 ...
user avatar
  • 663
55 votes
6 answers
114k 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 ...
user avatar
  • 735
47 votes
1 answer
64k 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?
user avatar
  • 713
44 votes
7 answers
87k 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 ...
user avatar
44 votes
2 answers
24k 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 ...
user avatar
  • 1,279
37 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 ...
user avatar
  • 1,084
37 votes
5 answers
100k 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 ...
user avatar
  • 471
37 votes
3 answers
18k 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? ...
user avatar
37 votes
1 answer
22k 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 ...
user avatar
36 votes
6 answers
83k 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 ...
user avatar
  • 517
35 votes
2 answers
6k 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 ...
user avatar
34 votes
5 answers
150k 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....
user avatar
  • 1,505
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. ...
user avatar
34 votes
7 answers
228k 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 ...
user avatar
  • 441
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 ...
user avatar
32 votes
1 answer
24k 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 ...
user avatar
  • 421
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 ...
user avatar
30 votes
2 answers
19k 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?
user avatar
29 votes
5 answers
75k 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 ...
user avatar
28 votes
3 answers
35k 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 ...
user avatar
  • 383
28 votes
4 answers
71k 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 ...
user avatar
  • 437
27 votes
3 answers
37k 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 ...
user avatar
  • 1,511
26 votes
10 answers
71k 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 ...
user avatar
  • 697
26 votes
6 answers
29k 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 ...
user avatar
26 votes
1 answer
35k 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 ...
user avatar
  • 435
26 votes
1 answer
43k 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?
user avatar
  • 363
25 votes
3 answers
16k 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 ...
user avatar
  • 461
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 ...
user avatar
  • 415
24 votes
3 answers
7k 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 ...
user avatar
  • 2,549
23 votes
3 answers
31k 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 ...
user avatar
22 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 ...
user avatar
  • 2,549
21 votes
1 answer
21k 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?
user avatar
21 votes
1 answer
12k 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 ...
user avatar
  • 313
21 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 ...
user avatar
  • 341
20 votes
5 answers
52k 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:...
user avatar
  • 301
20 votes
4 answers
93k 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 ...
user avatar
20 votes
4 answers
42k 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 ...
user avatar
  • 8,269
19 votes
8 answers
53k 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 ...
user avatar
19 votes
4 answers
12k views

Task scheduler for SQL Server Express

I have an ASP.NET MVC app which works with database under SQL Server 2008 R2 Express edition. There is a need to perform a regular task on updating some records in the database. Unfortunately the ...
user avatar
  • 1,505
19 votes
2 answers
23k 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, ...
user avatar
  • 293

1
2 3 4 5
53