Making copies of data which may be restored after a data loss event or to recover data from some earlier point in time.

learn more… | top users | synonyms (1)

164
votes
4answers
311k views

How do you mysqldump specific table(s)? [duplicate]

How can I dump a specific table or set of tables without including the rest of the db tables?
30
votes
6answers
3k 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 ...
28
votes
2answers
9k 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 ...
27
votes
8answers
2k 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 ...
17
votes
2answers
20k 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 ...
16
votes
3answers
14k 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 ...
16
votes
4answers
8k 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 ...
16
votes
3answers
4k 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 ...
16
votes
1answer
114 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 ...
15
votes
3answers
862 views

Database disaster prevention [closed]

My database is larger than 250GB. I make scheduled backups with a third party Tool. Are schedule database backups the best way to protect SQL Server database from corruption? Or can you recommend ...
15
votes
4answers
5k views

Is it possible to backup and restore part of a database in sql-server?

We have a sql-server 2005 database that we regularly transfer from our client site to ours. This takes a long time because we don't have a direct connection and have to transfer the file over their ...
14
votes
1answer
2k views

How does PostgreSQL handle Checkpoints in the middle of a WAL-enabled backup?

On a PostgreSQL v9.0 I've a WAL archiving system working. So WAL files a regulary archived (when 3 WAL are created or if a WAL is older than 15 minutes). Now I add a binary packup of the PG_DATA ...
13
votes
3answers
3k 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 ...
13
votes
3answers
25k 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 ...
13
votes
5answers
69k 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 ...
13
votes
2answers
6k 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 ...
13
votes
1answer
1k views

Sql Anywhere 11: Restoring incremental backup failure

We want to create remote incremental backups after a full backup. This will allow us to restore in the event of a failure and bring up another machine with as close to real time backups as possible ...
12
votes
3answers
17k 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?
12
votes
5answers
14k 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 ...
11
votes
5answers
565 views

SQL Server Backups - A couple of questions

We run our weekly backup job at 9pm on Fridays and we are experiencing a couple of issues with regard to disk space (which gets perilously low at times) and performance. We're looking at ...
11
votes
5answers
33k 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) ...
11
votes
3answers
16k 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 ...
11
votes
5answers
22k views

How to detect any changes to a database (DDL and DML)

There are a lot of databases on my client's sql server. In common, these databases is under development, so developers can design, refactor, do data modifications and so on. There are some databases ...
11
votes
2answers
55 views

Why do my Batch Requests increase when doing a Differential Backup

I have started doing some long-term tracking of various activities on our 2012 SQL Server and I have noticed an increase in Batch Requests during our incremental backups. To give an idea, normal ...
10
votes
2answers
19k views

Can I restore a SQL Server .bak files without SQL Server?

I have some large .bak files from a SQL Server 2005 dump. Can I restore these without using SQL Server, either to PostgreSQL, MySQL, or to flat text files? An open source solution would be most ...
10
votes
5answers
11k views

How do I copy my SQL Azure database to a local SQL Server instance?

I have an OLTP database hosted on a SQL Azure instance. I want to pull a copy of the database down from the cloud so I can run some heavy extracts and OLAP-style queries against it without impacting ...
10
votes
2answers
2k views

SQL Server transaction log backups: test whether tail log follows last known log backup

We are using SQL Server with full recovery mode. Given a full backup and a series of log backups, we would like to be able to check whether the log chain is complete from the last full backup to the ...
10
votes
1answer
4k 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 ...
10
votes
3answers
8k views

How best to maintain SQL log file sizes

I'm somewhat of a new DBA and I'm managing a SQL Server 2012 instance that has a fair amount of activity. I'm running in Full Recovery mode because we need point in time recovery. Right now, I'm ...
9
votes
6answers
459 views

Backing up SQL DB more frequently

I currently have a scheduled task that fires off each night at 2 AM that calls SQLCMD.exe and passes it a .sql script to run for the backup (shown below). We're a pretty small company with growing ...
9
votes
2answers
969 views

A Non-DBA Asks: How to Painlessly Copy/Move a SQL Server instance to another Server via Backup/Restore?

We've just got a new server so I need to copy the existing SQL Server 2008 instance over from the old db box. I normally do this by copying .mdf and log files over and attaching them but I'm not able ...
9
votes
4answers
273 views

Using third party tools to backup

Is there any advantages to use Third Party Tools to make Backups ? I'm not referring one in particular but I want to know if someone using them to DB greater than 200 GB. Servers are mostly MSSQL ...
9
votes
3answers
298 views

What is the use of restore headeronly when performing a backup?

When I monitor my backups with the following query: SELECT command, percent_complete, 'elapsed' = total_elapsed_time / 60000.0, 'remaining' = estimated_completion_time / ...
9
votes
3answers
528 views

Backup internals - What happens when a backup job is running - in terms of locking and performance overhead in SQL Server?

For MySQL I know the database is backed up table by table in SQL Statements, this results in locking and if you update columns while backing up you might end up in integrity problems. To my ...
9
votes
1answer
5k views

SQL Server Transaction Backups vs Logs

I've inherited a moderately-sized business-critical SQL Server 2008 database and am trying to wrap my head around backup planning. (I'm a developer, not a DBA.) The way our system is set up right now ...
9
votes
7answers
957 views

Scheduled backup task doesn't always backup all databases despite always saying job successful

I have a job in SQL 2008 that runs a stored proc to backup all databases. This runs daily via sql server agent job. It quits with success every day but some days it quits with success only after ...
9
votes
6answers
33k views

Error 3154 while restoring a backup using WITH REPLACE

I have SQL 2012 with SP1 installed on my computer. I made a backup of a database test.bak. I have a database with the name test2 which is the same database, but the data changed. I want to restore ...
9
votes
4answers
4k views

Determine when a PostgreSQL database was last changed

I'm looking at altering how backups are done and am wondering if there is a way to determine which databases in a postgreql cluster have not been recently changed? Instead of using pg_dumpall, I'd ...
9
votes
2answers
19k views

SQL Server backup failing. Error: 3041, Severity: 16, State: 1

First of all, I'm very new at stackexchange, so please bear with me. I'm running a SQL Server 9.0.4060. My problem is: my SQL Server backup keeps failing on a lot of the databases. The Maintenance ...
9
votes
1answer
876 views

Restoring differential backup creates DEFUNCT log file?

Here is my problem. I'm trying to move a database to a new server via a full restore, then cutover with a quick differential backup/restore. I can do a full restore without a problem, but when ...
8
votes
6answers
4k views

Pros and Cons of SQL Server back up strategies and their appropriate usage scenarios

From what I can tell, there are three possible ways of backing up your SQL Server database Full backup Differential backup Log Shipping What are the pros and cons of each strategy and in what ...
8
votes
2answers
684 views

Can't find process that is creating backups

We have an instance of SQL Server 2008 installed on a customer server. The customer's IT department is in charge of the various backups of the machine. The SQL Server log shows there is a full backup ...
8
votes
2answers
694 views

PostgreSQL Complete Database Backup

I've been trying to figure out how I can completely backup my PostgreSQL database. I have not seen any resource that explains how to do a complete backup. I am aware of the pg_dump and pg_dumpall ...
8
votes
3answers
2k views

Database backups in Oracle - Export the database or use other tools?

One of the "practices" that I've seen about being performed by DBA's in my organization is to treat a full database export using tools like exp / expdp as backup. Would this be a good practice ? What ...
8
votes
2answers
7k views

How to delete a Backup from a backup set in SQL Server 2008 R2

The title pretty much sums it up. We are running out of space on our backup disks, and need to remove old backups out of the set. I can't seem to find any information on this.
8
votes
3answers
761 views

Restricting users to COPY ONLY backups

I am looking to deploy log shipping in my production environment. This will mean I will have to manage backup chains which could be disrupted by people taking backups to refresh development ...
8
votes
2answers
181 views

How many backup devices is recommended to improve backup performance?

In order to improve performance of our SQL Server backups we backup to multiple backup files. On most blog posts about this topic they say to use multiple backup files but, is there a point where ...
8
votes
2answers
781 views

PostgreSQL PITR restore

I have a PostgreSQL 9.1 server running on a CentOS 5.8 32bit OS and I have enabled WAL archiving. On this server, there are three databases: databaseA, databaseB and databaseC. Someone deletes a ...
8
votes
0answers
1k views

Mongodb incremental backups

I was given the task to setup incremental backups for mongodb replicaset, as start point of course I googled about it and could not find anything on mongodb docs, I did find however this question ...
7
votes
3answers
3k views

Will the transaction log shrink automagically in SQL Server?

When SQL Server database in a SIMPLE mode, you don't have to care about the transaction log bakcups. But in a SIMPLE mode, the transaction log seems to grow as it does in FULL mode. Does is truncate ...