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)

191
votes
4answers
361k 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?
31
votes
6answers
4k 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
10k 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 ...
18
votes
2answers
22k 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....
18
votes
1answer
569 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 ...
17
votes
3answers
16k 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 ...
17
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 ...
17
votes
4answers
83k 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 ...
16
votes
5answers
19k 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 ...
16
votes
3answers
32k 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 ...
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 ...
15
votes
3answers
26k 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?
15
votes
3answers
892 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
668 views

Why can't a differential backup specify its base?

This is my first DBA.SE post, so please inform me of any mistakes, thanks! I am a new DBA (not an IT pro, just no one else in the company to do it), so the more basic the explanation the better. I ...
15
votes
4answers
6k 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 ...
15
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 ...
14
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 ...
14
votes
3answers
165 views

Transaction Log Backups Serial or Parallel?

We happen to be using SQL Server 2012 Standard Edition. I also happen to use Ola Hallengren's scripts to provide an easy, more flexible framework for doing backups and maintenance. This question isn'...
14
votes
2answers
8k 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 ...
14
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
5answers
38k 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....
12
votes
3answers
19k 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 ...
12
votes
3answers
165 views

Keeping (Partial) Backups Small when using SQL Server FILESTREAM

I have a database with nearly 1TB of FILESTREAM data that I don't need backed up (if the data was deleted it'd be recreated automatically in a couple of hours, so it just isn't important). Most of the ...
11
votes
5answers
576 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 streamlining/...
11
votes
5answers
2k views

Backing up & restoring 10-20 SQL Server databases to a ~synchronous state?

I need to backup up 10-20 SQL Server 2008 R2 databases with sizes between 10-50 GB, while they are online and used simultaneously by a single enterprise app. I also need to restore them to a state ...
11
votes
5answers
25k 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
58 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 day-...
10
votes
2answers
21k 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
3answers
785 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 ...
10
votes
5answers
12k 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
4answers
594 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 ...
10
votes
1answer
5k 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
9k 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 ...
10
votes
6answers
39k 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
6answers
471 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
5answers
5k views

Attach/detach vs. backup/restore

I need to transfer the database (as a whole) to another server, to make a duplicate database to setup another test environment. I have two choices: Make a full backup at source server/restore at ...
9
votes
2answers
13k views

Why do we need to backup Oracle archive logs along with datafiles

Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2) says that Archived redo logs are the key to successful media recovery. Back them up regularly. But, I wonder, why backups of ...
9
votes
2answers
1k 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
3answers
339 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
4answers
20k 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 ...
9
votes
1answer
6k 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
974 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
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
22k 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
992 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
5k 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
5k 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 ...
8
votes
2answers
1k 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 ...