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.

Backups have two distinct purposes. The primary purpose is to recover data after its loss, be it by data deletion or corruption. The secondary purpose of backups is to recover data from an earlier time, according to a user-defined data retention policy.

There are two types of backup : Physical and Logical

Physical

Cold backups copy database files when the database is offline. This would be done by shutting down the database, copying the database files, and starting the database back up. Although this is a reliable backup method, many databases have availability requirements that preclude its use.

Hot backups use software that understands the state of the database to make a consistent copy of the database files without requiring the database to be taken offline.

Some systems can capture transaction logs which can be used in conjunction with an earlier backup to effectively have a backup for any point in time for which the logs cover.

Some OS and disk systems can take a snapshot of the database files providing a point in time which the files can be restored to. When this type of backup is done and the snapshot is restored, the system will behave similarly to a loss of power and likely will need recovery.

Logical

A logical backup makes a copy of the objects in the database independent of their physical location. This copy could have an export specific format or be in the form of SQL statements.

See the backup article on Wikipedia for further information.

2541 questions
517
votes
4 answers

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?
markdorison
  • 5,305
  • 3
  • 14
  • 6
202
votes
7 answers

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 backup the database nightly. Due to my comcast…
Patrick
  • 4,129
  • 7
  • 26
  • 26
160
votes
13 answers

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, what's the most efficient way to do this?
John
  • 1,761
  • 2
  • 13
  • 10
151
votes
12 answers

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
116
votes
5 answers

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. There is a table with around 300 million rows, it's…
SBhojani
  • 1,263
  • 2
  • 9
  • 4
104
votes
6 answers

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 database then the dump may contain errors. Are there…
user784637
  • 1,175
  • 2
  • 8
  • 7
59
votes
8 answers

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?
Praveen Prasannan
  • 1,456
  • 5
  • 24
  • 38
59
votes
7 answers

.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 can script the restore process, but I've seen this…
Sean Long
  • 2,206
  • 5
  • 21
  • 29
57
votes
1 answer

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 could be wrong, finding a good PostgreSQL blog has…
Ali Razeghi - AWS
  • 7,378
  • 1
  • 22
  • 36
56
votes
6 answers

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 applications on the target machine cannot access the…
xpda
  • 745
  • 1
  • 6
  • 8
56
votes
3 answers

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 a .bak file of the same database? Thanks for any…
Chris
  • 663
  • 1
  • 5
  • 5
47
votes
1 answer

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?
nakhli
  • 713
  • 2
  • 7
  • 10
45
votes
7 answers

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 Is there a way that I can incorporate this select…
David LeBauer
  • 3,062
  • 8
  • 29
  • 33
44
votes
2 answers

How to restore a "contained" database?

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 sp_configure value 'contained database…
David
  • 1,279
  • 1
  • 9
  • 12
39
votes
3 answers

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? Are there scenarios where one of them might work…
Ryan Foley
  • 531
  • 1
  • 5
  • 7
1
2 3
99 100