Questions tagged [restore]

Reloading a database from a backup, typically for disaster recovery, or to make a copy of a database onto another server.

Filter by
Sorted by
Tagged with
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?
Kees C. Bakker's user avatar
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 ...
Zilk's user avatar
  • 1,111
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?
Praveen Prasannan's user avatar
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 "...
Rob Bednark's user avatar
  • 2,163
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 ...
KSletmoe's user avatar
  • 657
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 ...
David's user avatar
  • 1,339
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 ...
randodatauser's user avatar
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....
rem's user avatar
  • 1,555
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 ...
Funky's user avatar
  • 471
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 ...
Marcello Miorelli's user avatar
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 ...
Andy K's user avatar
  • 667
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 "...
Stephane Rolland's user avatar
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 ...
Adam Butler's user avatar
  • 1,531
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. ...
Val F.'s user avatar
  • 363
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?
Sunil Kumar Machineni's user avatar
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, ...
Julian's user avatar
  • 303
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: ...
Racer SQL's user avatar
  • 7,376
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 ...
Abe's user avatar
  • 397
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 ...
Sébastien Clément's user avatar
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 -...
user1434997's user avatar
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 ...
yrushka's user avatar
  • 1,954
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 ...
Danielle Paquette-Harvey's user avatar
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} #...
Nam G VU's user avatar
  • 289
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 ...
Avinash Sahu's user avatar
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 ...
ian's user avatar
  • 271
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 ...
user avatar
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 ...
CodeMed's user avatar
  • 487
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 ...
user avatar
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 ...
bogdan's user avatar
  • 171
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?
Philipp Hofmann's user avatar
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&...
Jerome's user avatar
  • 279
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 ...
HamTheAstroChimp's user avatar
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 -&...
Josh Bula's user avatar
  • 251
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 ...
evictednoise's user avatar
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 ...
M.Ali's user avatar
  • 1,960
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....
serge's user avatar
  • 421
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 ...
alun's user avatar
  • 233
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 ...
djohnson10's user avatar
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 ...
E.Rawrdríguez.Ophanim's user avatar
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 ...
Jcl's user avatar
  • 231
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'...
Stew's user avatar
  • 133
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 ...
iKnowNothing's user avatar
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 ...
James's user avatar
  • 131
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 ...
Lunatik's user avatar
  • 221
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 ...
immutabl's user avatar
  • 809
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 ...
immutabl's user avatar
  • 809
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 ...
sam's user avatar
  • 221
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. ...
Paul's user avatar
  • 1,453
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 ...
FilamentUnities's user avatar
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 ...
Brooks's user avatar
  • 521

1
2 3 4 5
29