Questions tagged [restore]
Reloading a database from a backup, typically for disaster recovery, or to make a copy of a database onto another server.
1,436
questions
96
votes
8
answers
301k
views
How to query last restore date in SQL Server?
Is there a T-SQL query that shows the last restore datetime for a certain database?
64
votes
8
answers
44k
views
Is it possible to quickly create/restore database snapshots with PostgreSQL?
First of all, I'm a developer, not a DBA or sysadmin; please be gentle :)
I'm working on an application workflow where a single user action will trigger complex changes in the database - creating ...
62
votes
8
answers
135k
views
Restore mysql database with different name
How can we restore mysql database with different name from mysqldump file.
I dont't want to open dump file and edit it. Any other better methods?
54
votes
1
answer
79k
views
postgresql: how do I dump and restore roles for a cluster?
Where are roles stored in a cluster, and how do I dump them?
I did a pg_dump of a db and then loaded it into a different cluster, but I get a lot of these errors:
psql:mydump.sql:3621: ERROR: role "...
50
votes
6
answers
112k
views
pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists
I am using pg_dump / pg_restore to backup and restore a PostgreSQL database, but am getting some error messages (and a non-zero exit status) from pg_restore. I tried a super simple base case (outlined ...
48
votes
2
answers
31k
views
How to restore a "contained" database? [closed]
I recently tried to restore to my local development SQL Server a backup from a network instance. To my surprised I received the following error message:
Msg 12824, Level 16, State 1, Line 3 The ...
38
votes
3
answers
292k
views
Binary path in the pgAdmin preferences
I installed PostgreSQL because I need to restore a large .backup file I received for upload to another platform (it's 38 million rows which was apparently too large for the person to export any other ...
37
votes
5
answers
178k
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....
37
votes
8
answers
249k
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 ...
35
votes
2
answers
7k
views
DBA first day in a new job - check backups and security - How? what else should be checked?
Generally when I start in a new environment, I tend to check where are the backups, when the last full was taken, when was the last restore applied and I check the security too.
The way I do this is ...
34
votes
4
answers
71k
views
postgres - pg_dump and pg_restore without roles
I'm trying to restore a dump without having the appropriate roles on the receiving database.
As mentioned here but also here, you need to have the --no-owner as an option, either in pg_dump or ...
29
votes
1
answer
35k
views
disable constraints before using pg_restore.exe
When I try to execute pg_restore.exe of a dump file from a database, it throws dozens of errors, all the same:
ERROR: insert or update on table "someTable" violates foreign key constraint "...
28
votes
3
answers
39k
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 ...
26
votes
2
answers
20k
views
Best way to migrate a huge SQL Server database with low downtime over network
Problem definition
Our database server needs to be transferred to an other datacenter. It runs on Microsoft SQL Server 2012 Enterprise (64-bit) and contains two databases of about 2TB and 1TB.
...
25
votes
1
answer
26k
views
SQL-Server: Why do we use FILE = 1 in scripts for restoring a database
Recently when I came across the script for restoring a database, I got a doubt on why we have to use "FILE = 1"? Can't we restore a database without that statement!? Basically, what it is used for?
20
votes
2
answers
15k
views
Restore database excluding FILESTREAM data
Context
We are developing a system with a large-ish database in the bottom. It is an MS SQL database running on SQL Server 2008 R2. The total size of the database is about 12 GB.
Out of these, ...
20
votes
1
answer
2k
views
Restore Database using GUI - Wrong file to restore
I'm just messing around with the SSMS graphic interface and studying the options of the "restore" task.
One thing that I noticed is when I click on "generate script", the first line of the query is:
...
19
votes
2
answers
38k
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 ...
19
votes
1
answer
39k
views
Checkpoints are occurring too frequently during pg_restore
Under PostgreSQL 9.2.2 (Windows 32 bits), I have a pg_restore command that systematically results in log warnings about the checkpoint frequency, for example:
LOG: checkpoints are occurring too ...
18
votes
7
answers
69k
views
Error while restoring a Database from an SQL dump
I am extremely new to MySQL and am running it on Windows. I am trying to restore a Database from a dumpfile in MySQL, but I get the following error:
$ >mysql -u root -p -h localhost -D database -...
17
votes
2
answers
8k
views
10 TB database backup / restore Question
We have a very big production database which is 10 TB. We are considering migrating it to a cloud service, but I have to understand the risks and estimate the duration of the whole process. I backup ...
17
votes
7
answers
77k
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 ...
16
votes
6
answers
71k
views
How restore from .gz backup with new database name using mongorestore r3.2.9?
I'm trying to backup/restore mongodb database to/from .gz files as sample script here
#01 create .gz backup - ok for r3.2.9 and r3.4.10
mongodump --db ${DB_NAME} --gzip --archive=${BACKUP_FILE_GZ}
#...
16
votes
2
answers
32k
views
Mongo DB Replica set Stuck at RECOVERING state
We have created a replica set and now the problem is 2 members of replica set [3 members set] are in recovering mode from 48 hours. Initially the size of recovering nodes was increasing and now even ...
16
votes
3
answers
41k
views
pg_restore into a new database
I'm using Heroku's Postgres option and have a backup I downloaded from it. I'd like to restore it into a fresh database so I can look at it. I've been trying different commands to little avail. My ...
16
votes
1
answer
2k
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 ...
15
votes
4
answers
35k
views
mysqldump doesn't restore database. why not?
Why doesn't my restore operation work?
I created a dumpfile for a database using:
mysqldump -u root -p databasename > /home/databasename_bkup.sql
I then opened the dumfile and confirmed ...
15
votes
5
answers
13k
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 ...
15
votes
5
answers
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 ...
15
votes
2
answers
36k
views
What is the difference between NORECOVERY and RECOVERY when restoring database?
What is the difference between NORECOVERY and RECOVERY when restoring a database?
15
votes
4
answers
40k
views
Restoring from pg_dump with foreign key constraints
In restoring a database from a pg_dump, a number of errors are being generated and the whole table is subsequently being ignored. An example:
ERROR: insert or update on table "channelproducts&...
15
votes
2
answers
42k
views
Table is marked as crashed and should be repaired
I have MySQL and Nagios installed Linux PC (Centos5.5)
I have wrote some script to fetch the data from Nagios to Mysql DB (Exactly fetch data in Table)
The data fetching working properly (confirmed ...
15
votes
4
answers
18k
views
Restore Database wizard won't open in SSMS 2014
On a dev machine running Windows Server 2012 R2, I just upgraded SQL Server from 2012 to 2014.
Using SQL Server Management Studio 2014, when I right-click a database and go to Tasks -> Restore -&...
15
votes
2
answers
3k
views
Can't restore SQL Server db from full backup, log processing fails, database in 'restoring' state
I am trying to set up a database for development purposes on my PC's local SQL Server Developer Edition 12.0.2000.8. I've got a full database backup and separate transaction-log-only backup files ...
14
votes
3
answers
78k
views
"Database is in transition" Error
Today I was trying to restore a database over an already existing database, I simply right clicked the database in SSMS --> Tasks --> Take Offline so I could restore the database.
A small pop up ...
14
votes
1
answer
21k
views
Clone a database from SQL Server 2019 to 2017 [duplicate]
I try to clone a database from an Sql Server on my local machine v15 (2019) to a server machine v14(2017). I did a .bak file and when I tried to import on the server side it says:
System.Data....
13
votes
6
answers
21k
views
Extracting a field from RESTORE HEADERONLY
I'm trying to use 'RESTORE HEADERONLY' to get the date when the backup I'm about to restore was made.
The command:
RESTORE HEADERONLY FROM DISK = '<path to .bak file>'
works fine in Query ...
13
votes
1
answer
2k
views
Temporarily moving a SQL Server 2016 database to SQL Server 2017 and then moving back. Is it possible?
If I take a backup of a database from a SQL Server 2016 instance and then restore it to a 2017 instance to do some work on it.
Can I then turn around and back up that database from the 2017 instance ...
13
votes
1
answer
62k
views
SQL Server error "Missing family sequence number 2"
I'm trying to restore a local database on my server, but I'm having problems with the backup. I did a file .bak but every time I try to load it on the server it doesn't let me.
Here's a screenshot of ...
13
votes
1
answer
1k
views
Online page restore hitting 1000 limit
I've been tasked to try to recover a database which suffered from corruption (due to I/O failure, which has been fixed since). I'm not familiar with the database or what it contains.
I've been given ...
13
votes
1
answer
23k
views
Restoring a SQL Server 2012 database in high availability
I have a database which is in always-on high availability mode synchronized with another database on a different instance. How can I restore from a .bak file into the primary database using T-SQL?
I'...
13
votes
1
answer
2k
views
Differential Backup Issue - Why? Is this Possible?
I am using SQL Server 2014 and this is the situation:
I have server A and server B.
Overnight ETL is processed on server A.
After the loading process completes, database X gets backed-up (with ...
13
votes
3
answers
62k
views
How to Backup/Restore in PostgreSQL pgAdmin 4?
So I feel like this is something that should be very basic and easy to do, maybe I'm just not doing things correctly.
I want to do the equivalent of SQL Server backups. I want to create the ...
11
votes
6
answers
29k
views
Migrate from SQL Server 2000 to 2012 without a 2005 or 2008 instance
I have come across three old databases sitting on SQL Server 2000 which I need to move to 2012. I believe the standard approach is to restore into a 2005 or 2008 instance, update, re-export and ...
11
votes
2
answers
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 ...
11
votes
5
answers
31k
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 ...
11
votes
4
answers
27k
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 ...
11
votes
1
answer
13k
views
Tail Log backup when doing a restore?
Typically when doing a restore of a DB from a production server to a non-production server I will use the WITH REPLACE option as when I forget I get an error about the tail log not being backed up.
...
11
votes
1
answer
2k
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 ...
10
votes
1
answer
15k
views
Do indexes get transferred with pg_restore
I see other questions that have been answered in the negative (i.e. indexes do not get transferred over with the standard pg_restore). However, it looks like it did in my recent dump/restore and I am ...