Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
28
votes
1 answer

How can I mysqldump all databases except the mysql schema?

I want to backup all 200+ databases on a MySQL server to all-dbs.sql. I want to exclude the mysql schema. How can I do that?
haulpd
  • 383
  • 2
  • 4
  • 7
28
votes
4 answers

Compare two similar Postgres databases for differences

I occasionally download publicly available data sets in the form of Postgres dBs. These datasets are updated/modified/expanded over time by the repository host. Is there a Postgres command or tool (ideally FOSS) that can show the differences…
CuriousGorge
  • 291
  • 1
  • 3
  • 4
28
votes
1 answer

Is there a psql equivalent of bash's reverse-search-history?

I am very fond of bash's reverse-search-history (C-r) (command-line feature): Search backward starting at the current line and moving ‘up’ through the history as necessary. This is an incremental search. When I hit control-r in bash, I get the…
user664833
  • 1,869
  • 2
  • 20
  • 19
28
votes
3 answers

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 needs 100GB to be restored. I believe this is…
Adam Butler
  • 1,531
  • 4
  • 16
  • 16
28
votes
3 answers

upgrading from postgres 9.1 to 9.3 on ubuntu server

I have my production server(ubuntu 13.10) running with postgresql 9.1. I want to use few features of 9.3, hence want to upgrade. Could someone help me with upgrading from 9.1 to 9.3 so that there is a downtime of not more than 30 mins. or so? Prime…
Ram Kumar
  • 383
  • 1
  • 3
  • 4
28
votes
6 answers

How to view an encrypted view or stored procedure

I am working on a third party database. When I try to view the definition of a view by right clicking, CREATE TO and then to NEW QUERY EDIT WINDOW, I am getting an error: This property may not exist for this object or may not be retrievable due…
goofyui
  • 653
  • 2
  • 12
  • 20
28
votes
3 answers

VACUUM FREEZE vs. VACUUM FULL

Can someone explain the difference between these types of VACUUM in PostgreSQL? I read the doc but it just says that FULL locks the tables and FREEZE "freezes" the tuples. I think that's the same. Am I wrong?
Christian Maíz
  • 383
  • 1
  • 3
  • 5
28
votes
2 answers

SQL Server's READ COMMITTED SNAPSHOT vs SNAPSHOT

I was researching the differences between SQL Server's READ COMMITTED SNAPSHOT and SNAPSHOT isolation levels and came across the following resource: Choosing Row Versioning-based Isolation Levels For most applications, read committed isolation…
John Russell
  • 717
  • 1
  • 8
  • 17
28
votes
6 answers

How can I create readonly user for backups in PostgreSQL?

Is it true that it is IMPOSSIBLE to create a readonly backup user in PostgreSQL? I've been advised on an IRC channel that you simply can't have a backup only user with no ownership privileges. I find it very strange so I want to make sure I'm not…
milosgajdos
  • 429
  • 1
  • 5
  • 8
28
votes
3 answers

Do natural keys provide higher or lower performance in SQL Server than surrogate integer keys?

I'm a fan of surrogate keys. There is a risk my findings are confirmation biased. Many questions I've seen both here and at http://stackoverflow.com use natural keys instead of surrogate keys based on IDENTITY() values. My background in computer…
Hannah Vernon
  • 70,041
  • 22
  • 171
  • 315
28
votes
8 answers

How to query a database for empty tables

Due to some 'developers' we had working on our system we have had issues with empty tables. We have found that during the transfer to the cloud several tables were copied, but the data in them wasn't. I would like to run a query the system tables…
codehammer
  • 473
  • 2
  • 5
  • 5
28
votes
2 answers

How to write a query which finds all circular references when a table references itself?

I have the following schema (names changed), which I cannot change: CREATE TABLE MyTable ( Id INT NOT NULL PRIMARY KEY, ParentId INT NOT NULL ); ALTER TABLE MyTable ADD FOREIGN KEY (ParentId) REFERENCES MyTable(Id); That is, each record is…
cubetwo1729
  • 881
  • 4
  • 10
  • 15
28
votes
2 answers

`ERROR 1114 (HY000) the table ... is full` with innodb_file_per_table set to autoextend

I have a MySQL database that holds a large amount of data (100-200GB - a bunch of scientific measurements). The vast majority of the data is stored in one table Sample. Now I'm creating a slave replica of the database and I wanted to take the…
Petr
  • 537
  • 2
  • 5
  • 11
28
votes
1 answer

Primary key with "NOT FOR REPLICATION" option

I have recently taken over a project, and I have discovered that in most tables the primary key has the property "NOT FOR REPLICATION". I'm no DBA, but surely in most databases, a record without a primary key would be considered corrupted. The…
Steve Rukuts
  • 383
  • 1
  • 3
  • 6
28
votes
2 answers

Difference between Owned Schemas and Role Membership for Microsoft SQL Server

Are there differences between Owned Schemas and Role Members? From my understanding, the Role Members are compulsory - user need to have at least one of the check box under Role Members checked. However, the Owned Schemas is not compulsory. It…
Jack
  • 2,509
  • 14
  • 36
  • 42
1 2 3
…
99 100