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)

7
votes
3answers
4k 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 ...
15
votes
4answers
669 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 ...
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 ...
6
votes
8answers
16k 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 ...
6
votes
2answers
177 views

Understanding Backup Types

I have recently started a role as a junior DBA... Yesterday I created a new database for a co-worker and my boss asked me to make sure we get a backup plan on it asap. I didn't think much about it, ...
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 ...
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 ...
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 ...
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 ...
8
votes
5answers
14k views

User Permissions Messed Up following a Backup -> Restore Operation

I had to move several SQL Server 2008 databases to our new db server so I backed them all up (to .bak files), copied these files over to the new box and restored them (all done using SQL Management ...
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
196 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 ...
5
votes
1answer
977 views

Quick MySQL Backup (1 file per table)

I asked a relevant question few months back and got answer with wide variety of options for backup. But this time I have different scenario and I have got a database with 7 tables and 1 of them has ...
4
votes
3answers
5k views

How can I dynamically back up all SSAS databases on a given instance?

I want to dynamically back up all the databases on a given SSAS instance using a SQL Agent job (which would most likely involve executing an SSIS package). It is imperative that this is a dynamic ...
4
votes
2answers
231 views

BACKUP LOG cannot be performed because there is no current database backup

I have a SQL Server 2005 database using the full recovery model. If I execute the following commands: ALTER DATABASE test SET RECOVERY SIMPLE; ALTER DATABASE test SET RECOVERY FULL; or BACKUP LOG ...
4
votes
3answers
8k views

how to create backup for stored procedure,trigger and functions of a database in sql server 2008?

I'm having 2 databases in 2 different servers(.246 and .252). I need to create backup for all stored procedures, triggers and function from 252 server and restore it in 246 server.database name in ...
3
votes
2answers
445 views

When does creating a .sql backup with mysqldump become too large?

Is there a limit to how large a database can be when executing a .sql backup using mysqldump, or will you run into server problems before that - will the server not be able to take such a large backup ...
2
votes
2answers
7k views

Restore Database script

I have a live database which i would like to create a backup and restore script that will take the "live" database every night, create a back, then drop the existing "testing" database (if any) and ...
1
vote
1answer
2k views

mysqlbinlog failing with “ ERROR 1032 (HY000) at line 48: Can't find record in `` ” Error

I am facing some issue with PITR restore using mysqlbinlog . It is failing with the following error . # mysqlbinlog mysql-bin.000035 | mysql -u root -p db Enter password: ERROR 1032 (HY000) at ...
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'...
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
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
3answers
23k 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 ...
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 ...
7
votes
1answer
4k views

How can I fix “Cannot perform a differential backup … a current database backup doesn't exist”?

We recently switched to the FULL recovery model, with full backups being done every weekend, and differentials being taken every day. The problem is, the differential backups don't always seem to ...
7
votes
1answer
2k views

recover a single mysql database on a busy master-slave replicated system

Looking for a strategy or tool to deal with recovering a single database to a point-in-time in a busy replicated system. I have 12 databases running on 2 MySQL 5.0.77 servers in master-slave ...
6
votes
4answers
6k views

SQL Server 2012 always-on backup, full vs copy-only

I need a quick clarification on copy-only backup vs full backup as it pertains to transaction log numbering and restore if required. I have SQL Server 2012 setup with always on for multiple databases....
6
votes
5answers
638 views

SQL Server Backup Process Tuning

I am working for a small company that has 20 full time web sites. And another 20-30 micro sites. We are having issues with the sites freezing every day after the SQL Server Agent backup is running. ...
6
votes
4answers
18k views

How to increase fast backup and restore of 500GB database using mysqldump?

There is a database A size of 500GB. Tables in database A contains both MyISAM and INNODB tables. MyISAM tables are master tables and Innodb tables are main transaction tables. Backup and restore ...
5
votes
2answers
3k views

SQL Server 2008 R2 Restore COPY_ONLY full backup with transaction logs

After doing some research I cannot seem to find an answer to this question. Background I am attempting to setup a backup plan that fits the following three requirements: Reliability of backups, ...
5
votes
5answers
4k views

Can I restore a database to a point of time without backup?

I have an SQL 2005 database with logging set to FULL but without any backups. Is it possible to restore it to a point of time?
5
votes
3answers
3k views

SQL Server 2012 database backup successfully report but no backup file

I have created maintenance plan in SQL Server 2012. And every day, the maintenance plan should backup the database. There is no database backup file when I look in the folder where the backups must be ...
5
votes
2answers
7k views

Saving SQL Server backup file in multiple parts

I am taking daily backups of a SQL Server database. Right now the .bak file is about 2GB and it is growing day by day. There is a scheduled job is running which moves this .bak file from one location ...
4
votes
1answer
2k views

Which are the minimum privileges required to get a backup of a MySQL database schema?

Which are the minimum privileges required to get a backup of a MySQL database schema? I am interested only on the schema, not on the data. Are SELECT privileges required?
4
votes
1answer
7k views

I/O is frozen on Database & also I/O was resumed on Database on daily basis in log with complete Backup

I would like to say that as on daily basis i got the message in my log file. First The I/O is frozen on Database (DatabaseName) message appears in log. The message is like I/O is frozen on ...
4
votes
1answer
4k views

Is Percona xtrabackup the right choice for my 500GB MySQL backup?

I have a MySQL db with 500GB, consisting mainly of Innodb tables, and one big (200GB) MyISAM table. My current backup strategy is copying the db files to an external harddrive. This causes a downtime ...
3
votes
1answer
3k views

PostgreSQL Scheduled Database Backup

For scheduling a daily back up on a postgres database we execute the following command: 0 2 * * * /usr/local/pgsql/bin/pg_dump -Fp -b -U database_name \ > $HOME/db_backups/database_name-`...
3
votes
2answers
479 views

How to prevent SQL Log from being too full

I have a database that generate a huge amount of transaction per seconds, something like 50-100 transactions/second. Because of this, the log files grows very quickly. The thing is though, that even ...
3
votes
1answer
159 views

pg_dump does not honor -n

I'm using pg_dump (9.2 database), to extract one schema and load this into another database (9.4). well, here's the command: pg_dump -f file.sql -F plain --inserts -n schema database Ok. My ...
3
votes
1answer
1k views

What is the best practice for backing up a database in Simple mode?

I am trying to create a SQL Job for backing up my database. I want to have: A weekly full backup A daily differential backup Something that will clean up backups that are older that 14 days I ...
3
votes
3answers
169 views

How can I make a database backup go faster?

I recently attended an interview for my next job and was asked this: How can I speed up the backup of a database sized close to 1TB or 800 GB in SQL Server? My Answer: We can use Database ...
3
votes
2answers
19k views

Backup Access Database Daily/Weekly

We have a Microsoft Access database split into backend/frontend. What I am looking for is to be able to automatically backup these files on a daily or weekly basis - what is the best way to go about ...
2
votes
1answer
118 views

How is my database being backed up?

I'm using SQL Server 2008 R2. Someone else set everything up. I encountered an error today: Autogrow of file 'ASPState_log' in database 'ASPState' was cancelled by user or timed out after 1748 ...
2
votes
1answer
6k views

How to Create Snapshot Backups in MySQL

I've seen a lot of people talking about snapshots backup, even I'm reading a book about mysql but nobody explain the code how to make a snapshots backup. This is what the book says: flush tables ...
2
votes
3answers
691 views

xtrabackup prepare - after dump or before restore?

i have doubts about xtrabackup --apply-log. is there any difference when use it? should it be done right after dump or before restore (even few months later)? seems stupid, but maybe i missing ...
2
votes
1answer
977 views

How to Backup and Restore Filestream Data

I am trying to backup and restore my database which includes a filestream filegroup. I backed up the database without the filestream filegroup and was interested to know what happens when you restore ...
2
votes
1answer
226 views

Backup on an empty SSAS database hangs

I created an SSIS package to iterate through all databases on an SSAS instance. It grabs the DatabaseId using AMO in a vbscript, then generates the XMLA to backup each database. Most databases ...
2
votes
1answer
92 views

SQL Server Database Migration - Backup/Restore while Original DB still in Production

I have a very large DB in full recovery mode. My SQL Server is 2008 R2. The thing is that I need to migrate to a new server while having the original DB still in production. Both backup and restore ...
1
vote
1answer
3k views

Write lock during mysqldump even using --lock-tables=false

When I try to dump an online database using --lock-tables=false, others cannot update the table. Are there any methods to solve this? As I am using MyISAM, does --single-transaction for InnoDB help? ...
1
vote
1answer
1k views

MySQL Point in Time Recovery For Just a Single Schema?

Is it possible to simply restore a single schema and bring it to a point in time (Say three hours ago) without bringing the entire database back? I can think of a lengthy method of backing up each ...