Making copies of data which may be restored after a data loss event or to recover data from some earlier point in time.
25
votes
6answers
1k 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 ...
20
votes
5answers
32k 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?
16
votes
2answers
4k 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 ...
15
votes
4answers
7k 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 ...
13
votes
1answer
535 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 ...
11
votes
3answers
808 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 ...
10
votes
1answer
741 views
How does PostgreSQL handles 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 ...
9
votes
5answers
409 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 ...
9
votes
3answers
2k 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 ...
9
votes
2answers
447 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 ...
8
votes
2answers
1k 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 ...
7
votes
6answers
330 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 ...
7
votes
6answers
416 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 ...
7
votes
3answers
997 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 ...
7
votes
1answer
145 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 ...
7
votes
1answer
4k views
How can I recover data from the LDF file?
We are using SQL 2005 Express Edition. We want to read the data from the LDF file in order to restore some deleted records.
We tried using a trial version of ApexSQL which helped us a lot. Instead of ...
7
votes
1answer
110 views
Error in SSMS when attempting to restore a database
We have over a dozen SQL servers, one of which hosts a couple of hundred sage databases, when i right-click on the Databases node in SSMS there is a long delay then i get the following error (and the ...
7
votes
3answers
5k 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 ...
7
votes
1answer
123 views
SQL Server considerations when backing up / restoring a database that uses replication
In the event that I need to restore a database involved in replication, what considerations to I need to keep in mind?
Do I need to keep backups of both the publisher and the subscriber?
Can I ...
7
votes
1answer
325 views
SQL Server distribution database log file grows uncontrollably after full database backup
We have a merge replication environment that is pushing to 8 subscribers. This is working fine. Our distribution database is setup in Simple recovery mode. We have a maintenance plan that will backup ...
6
votes
6answers
2k 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 ...
6
votes
3answers
2k views
Why is a .bak so much smaller than the database it's a backup of?
I just took a backup of a SQL Server database. The MDF and LDF files together total around 29 GB, but the .bak file was only 23 GB, about 20% smaller.
My first guess when one version of a set of ...
6
votes
1answer
281 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 ...
6
votes
1answer
101 views
When you back up a database and scripts are running at the same time, at what point in time does the backup reflect?
If I run a SQL Server database backup via Management Studio and there are scripts running at the same time, does the backup reflect the point in time when the backup was started or when it ended? My ...
6
votes
2answers
290 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 ...
6
votes
1answer
536 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
1answer
286 views
Restore a Database to a New database (T-SQL)
I have a database backup from a database, say SourceData which needs to be restored on another database, same schema, but different name (SourceData_1) using T-SQL. I don't know why I gets the ...
6
votes
1answer
224 views
Backup to three drives suddenly slow
The situation: I have a 1.3 TB Database (SQL 2005) that I am currently backing up to 2 network shares on separate machines and on one local USB. (Don't ask why - long story involving a DBA that quit ...
5
votes
3answers
2k 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 ...
5
votes
5answers
3k 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 ...
5
votes
3answers
7k views
How to restore multiple backups
I'm using ExpressMaint and Windows Scheduled Tasks to create weekly full and daily differential backups of some MS SQL Server 2008 R2 Express databases
Weekly fulls
expressmaint -S ...
5
votes
4answers
10k 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)
...
5
votes
1answer
392 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 ...
5
votes
2answers
3k 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 ...
5
votes
2answers
512 views
Save postgres database from broken ubuntu 10.04 server
Recently a database server with an important db broke (some grub linux issue that I'd prefer not to solve).
I can still access the filesystem. Is there a chance to transfer the database by only ...
5
votes
2answers
139 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 ...
5
votes
2answers
263 views
Can I Run Two Full Backups on SQL Server 2005 at the Same Time?
Running SQL Server Express 2005 here. We are creating several programs which run a backup on the database using Server Management Objects (SMO) mostly through C# (.NET). We have separate processes ...
5
votes
2answers
2k 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.
5
votes
1answer
188 views
SQL Recover from .bak file with NOINIT
This will, hopefully, be a nice and quick question for someone out there. It continues from my previous question.
I have a .BAK file that I'm trying to restore. The .BAK was created with the NOINIT ...
5
votes
1answer
469 views
How to back up a small database in SQL Server 2008 R2 Express Edition
I will shortly be migrating a database from SQL Server 2000 (part of SBS 2003) to SQL Server 2008 R2 Express Edition
The database is small, and there are only a few hundred short transactions per day ...
5
votes
2answers
169 views
Performing data update operations while backing up a large SQL Server database
I have a large (in the tens of millions of records) database that I am going to perform a full database backup on.
However, the database is large enough that transactions can start before and during, ...
5
votes
1answer
251 views
Are there performance benefits to turning off archivelogging? Is it wise?
The first part of the question is pretty simple; has anyone run a DB in both archivelog and noarchivelog mode? Were there any measurable performance benefits to turning off logging that you could ...
5
votes
2answers
5k 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 ...
5
votes
4answers
842 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 ...
5
votes
2answers
965 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 ...
5
votes
2answers
113 views
Thought about this SQL Server backup plan?
I just started a new job, and I'm reviewing the database maintenance plan. I've got quite a bit of experience writing SQL, but not much experience with DB administration. My last job was at a large ...
5
votes
2answers
2k 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 ...
5
votes
2answers
430 views
How to automatically stagger transaction log shipping to minimize bandwidth usage peaks?
The following question pertains to Microsoft SQL Server transactional log shipping (TLS).
We're using SQL Server 2008 R2 SP1, although the question is probably relevant for all recent editions.
...
5
votes
2answers
776 views
SQL Server backup, attach to BAK file?
I have a SQL Server database with a full text index. When I created the app that uses it, I made a backup of the database and I ship the .BAK file with the app. As part of the app install it does a ...
5
votes
1answer
115 views
How do I shrink the physical Transaction Log file when it's the principal in a mirror?
We setup database mirroring over the weekend, and forgot to re-enable the job that backs up the transaction logs. When I came in this morning, the transaction log had ballooned to 58GB, and was taking ...
