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.

Filter by
Sorted by
Tagged with
8
votes
2answers
7k 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 ...
8
votes
1answer
491 views

Backup and restore SQL server database with FILESTREAM filegroup

I use SQL Server and have a huge database with two filegroups: Primary: Which contains all the data except the large files (1MB+) FILESTREAM (read/write): Which contains the large files Now, the ...
7
votes
3answers
15k 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 (local)\...
7
votes
3answers
812 views

Is my restore process being badly affected by this third-party non copy-only backup?

This question may read like a duplicate, but is situation-based, and is posted from confusion applying the knowledge from other answers. I've read dozens of articles (among 1, 2, 3, 4), but am ...
7
votes
2answers
7k views

Large `mongodump` followed by `mongorestore`

I have a fairly large (~240GB) Mongo database that I need to transfer across a sluggish network and onto a new server. Traditionally for these situations, I've found that a mongodump followed by a ...
7
votes
3answers
33k views

SQL Server 2008 Database Restore Error - Cannot open backup device 15105

I am trying to restore from a backup and getting the following error Msg 3201, Level 16, State 2, Line 1 Cannot open backup device 'Z:\FULL_BACKUP.bak'. Operating system error 3(failed to ...
7
votes
3answers
432 views

Can full drive backup be used instead of MSSQL database backup?

I have a MSSQL Express server running on a VM. Hosting provider I use has an option to create drive snapshots. The server is running all the time and is not stopped during the drive backup. Backup ...
7
votes
2answers
2k views

Why is my log file so massive? 22gb. I am running log backups

I can't seem to figure out the answer. I've seen multiple answers like this: Why Does the Transaction Log Keep Growing or Run Out of Space? and everyone talks about running back ups on your log file ...
7
votes
2answers
1k views

Two Different Sources Taking Transaction Log Backups

I have inherited a database server with SQL Server 2005. The databases have full recovery mode with hourly transaction log backups. There are two sources taking full backups overnight and hourly ...
7
votes
3answers
18k views

Transaction Log Maintanance While Using AlwaysOn Availability Group

We are using HADR ( AlwaysOn Availability Group) feature of SQL Server 2012. Server and AG Configuration as below: SQL12_SRV1 --> AG1(PRIMARY) - SYNC -->> DBTest SQL12_SRV2 --> AG1(SECONDARY) ...
7
votes
2answers
36k views

How to backup IBM DB2 Database online?

I tried to backup the IBM DB2 (LUW) Database using this command: ./db2 backup database DBEMP to /home/user1/db2bkup But I got this message: SQL1035N The database is currently in use. SQLSTATE=...
7
votes
3answers
278 views

Is there a limit to how many backups can be appended to a single file?

A recent question got me looking at MS Documents and wondering. Is there a limit to how many backups can be appended to a single file? By default SQL Server uses NOINIT to append new backups to the ...
7
votes
3answers
8k views

Find which account SQL Server service is running BACKUP under

Our DBA is out and I'm trying to back up our (remote) dev database to a UNC path. I'm running the back up operation while logged into Management Studio as a particular Windows domain account (e.g. ...
7
votes
3answers
5k views

How to tell if a backup log chain is broken?

I had a situation where the Native Backups were being made on a Server. I happened to see in msdb that there was a third party backup tool (AppAssure) that was also taking VSS (kind-of) backup to ...
7
votes
4answers
31k views

Restore backup fails - not enough disk space [duplicate]

I have a backup that's about 6Gb. It's a "light" backup of the original (with the log tables purged), which is about 14Gb. I try to restore the backup on my SQL Express local server. It fails with a ...
7
votes
1answer
837 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 ...
7
votes
3answers
3k views

(Ola Hallengren) Deleting log backups older than last FULL backup

When I deploy Ola Hallengren's backup script into my servers, I always set the @CleanupTime parameter for log backups to zero. This way, every time the log backup job runs, it checks for log backup ...
7
votes
4answers
18k views

Verify mysqldump backup is corruption free

We've got a cron task backing up our Database using mysqldump - my main concern is corruption, short of manually importing and checking the backups each time whats the best way to check / verify a ...
7
votes
1answer
25k views

Is there a backup operator role in Microsoft SQL Server 2008?

I have a Windows Server 2008 with Microsoft SQL Server 2008 and VERITAS NetBackup installed. Basically, this NetBackup requires a valid logon ID into the SQL Server that is powerful enough to do a ...
7
votes
3answers
3k views

What methods are available to create a snapshot of an oracle database for testing purposes?

I am new to databases and have just been assigned to work with Oracle. We want to create a snapshot of the database for testing purposes that will allow us to revert back once we're done. I'm a bit ...
7
votes
1answer
146 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 ...
7
votes
3answers
68 views

What is not persisted in a database backup / restore? (SQL Server 2005)

If I take a backup of a database on a SQL 2005 Server, and then restore that backup to a different (new) database on the same database server instance, what things will not be persisted (things like ...
7
votes
1answer
3k views

SQL Server database backup file is double final size until backup completes

I've just completed writing a backup statement for a reasonably large database (1.3 TB), and I've been testing it out using a much smaller database (10 GB + 10GB log file). The backup writes to three ...
7
votes
1answer
6k 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 ...
7
votes
1answer
23k views

mysql: How do you determine the ideal 'open_files_limit' setting value?

I am facing this issue: https://serverfault.com/questions/104618/out-of-resources-for-mysqldump Questions I know I need to increase open_files_limit, but to how much? Can I call a command in mysql ...
7
votes
1answer
11k views

MySQL - MySQLDump problems with large table

I have a MySQL database that is 10.6GB - that has both MyISAM & InnoDB tables. One particular table (called 'responses') is 8Gb - this table is InnoDB. The live database is replicated to a ...
7
votes
2answers
614 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 ...
7
votes
2answers
3k views

Copying datadir directory enough for backing up a database with InnoDB tables?

I have an innodb table with 80 million rows and it's extremely slow with mysqldump so I'm thinking of copying the datadir directory for a backup / transfer. I used to copy database with only MyISAM ...
7
votes
1answer
498 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
1answer
113 views

Why does SQL Server require a private key to encrypt a backup?

I am setting up an experiment where I only have the public key portion of a certificate on the server. The certificate was generated on another server and I did not restore the private key. When I ...
7
votes
2answers
2k views

How to back up an entire database to text files

If it’s stupid and it works, it’s still stupid. The hullabaloo about GDPR got me thinking - if you had to go into your backups and modify/delete data, how would you do it? Cracking open a .bak file ...
7
votes
1answer
3k views

Fastest way to restore a Sql Server database

Each of our 10000 integration tests need to restore the database to its original "clean" state before each of them are done, during a cleanup stage. Running all tests takes time, and 80% of the time ...
7
votes
1answer
1k views

How does SQL Server calculate the initial size of a (compressed) backup?

When a backup is created, SQL Server guesses(?) the a size of the intial backup file. Later on, maybe when it appends the log the size will be re-adjusted, sometimes multiple times until the final ...
7
votes
1answer
6k views

In Postgresql, after pg_dump the access privileges on the database itself are not restored

Suppose user foo owns a database gee, and user foo has granted user bar with all access privileges on database gee gee=> \l List of databases Name | Owner | ...
7
votes
1answer
10k 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
6k views

Appending backups while overwriting expired sets

I've been reading up on this, but the online documentation is a bit difficult to understand and seems awkwardly written. For example the INIT, NOINIT. One says it will try to overwrite everything. ...
7
votes
3answers
33k views

mysqldump: Error: 'Can't create/write to file

Can't create temporary files on /var/tmp. mysqldump: Error: 'Can't create/write to file '/var/tmp/mysql.k6NKGk/#sql_5dc_2.MAI' (Errcode: 2)' when trying to dump tablespaces On my OS. id mysql uid=...
7
votes
1answer
2k 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 do I need to keep in mind? Do I need to keep backups of both the publisher and the subscriber? Can I ...
7
votes
2answers
13k views

How can I backup & restore a single FILEGROUP in Sql Server 2008

Previously, on ServerFault I asked a question about backing up and restoring a Sql Server 2008 Filegroup. Today, when I tried to RESTORE one of these FILEGROUP backups, I got the following error:- ...
7
votes
1answer
13k views

SQL Server backup fails on disk space error

SQL Server 2008 R2 RTM running on Windows 2008 64bit. Having problems backing up a single database with the following command backup database [somedatabase] to disk = '\\somehost\d$\BACKUP\...
7
votes
6answers
6k views

Backup very large table

I have to update certain values of a large table (for the sake of a presumed example, it is called 'Resource' and it is over 5M rows) and thus I have to make a backup before performing the changes. We ...
7
votes
1answer
763 views

Can SQL Server be configured to backup to a network share through a separate NIC?

We're in the process having our SQL Server instances backup to a network share, but our network admins want to try and divert the bandwidth from the backups through a different network segment. I was ...
6
votes
2answers
3k views

Understanding CleanupTime in Ola Hallengren's SQL Server scripts in relation to FULL backups and LOG backups

I am having trouble understanding what exactly to expect from the CleanupTime option in the Ola Hallengren Server Maintenance Solution. I'm finding some related questions, and elaborate answers, but ...
6
votes
3answers
1k views

Is it possible to take a database offline when doing a backup using an SQL job?

I have the scenario where I have to generate a backup of the database (SQL Server 2008) and restore into the new server (SQL Server 2016). While taking backup data should not be changed in any case. ...
6
votes
1answer
28k views

How can I solve postgresql problem after deleting wal files?

I turned the archive_mode on on my postgresql configuration for testing a backup server . And since the wal files took lots of disk space, after my test I turned it off and also deleted the wal files. ...
6
votes
3answers
13k views

how to use a different tmp dir for INTO OUTFILE select with MySQL?

I am trying to dump a large table (40GB, 600,000 rows) using a SELECT ... INTO OUTFILE query. I want to use that because I want my results sorted in a particular way (so mysqldump is not good enough). ...
6
votes
4answers
4k views

Can I restore all tables but not the stored procedures?

I have a testing database that I restore regularly from a production backup file in SQL Server 2008 R2 to get new data. We often edit\change or update the stored procedures in the testing DB to test ...
6
votes
2answers
656 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 ...
6
votes
2answers
21k views

Restoring encrypted database on another server (using Backup Encryption)

I have two SQL Server instances on same machine. I want to create an encrypted backup on one of the databases and then restore it on the second instance. I am doing the following steps: Create and ...