Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [schema-copy]

A schema copy is a backup of the database schema, sans the data.

A schema copy is a backup of the database schema, sans the data.

Most relational DBs have an export tool that can export the schema without the data in the tables:

  • PostgreSQL features pg_dump --schema-only
  • MySQL features mysqldump -p --no-data
14 questions
4
votes
1 answer

Getting nice PostgreSQL pgAdmin-style DDL

In pgAdmin 3 (and pgAdmin 4 if you're prepared to wait longer) the DDL for the currently selected database object is shown in the main panel, along with corresponding triggers, constraints, etc, all nicely formatted. It looks quite different to the…
beldaz
  • 1,740
  • 3
  • 16
  • 26
3
votes
2 answers

Moving database objects to another database without wizard

I have several databases on production and i have also test environment. I don't have permission to do backup and restore operations. Actually i am using wizard to generate scripts (schema + data) and run sql on another database. Is there a way i…
Muflix
  • 1,099
  • 5
  • 15
  • 27
2
votes
1 answer

PostgreSQL, PostGIS: Copying a schema with a geometry column

According to the PostGIS manual, geometry columns should not by added directly, but using AddGeometryColumn(): AddGeometryColumn — Adds a geometry column to an existing table of attributes. I am copying a schema from one PostgreSQL instance to…
Adam Matan
  • 12,079
  • 30
  • 82
  • 96
2
votes
1 answer

mysqldump takes forever to dump tables from a remote server

I'm trying to pull the database schema from a remote server, but mysqldump is too slow to output (125+) tables' structures. If I enter the said server and run mysqldump --compact --compress --no-data --add-drop-database --databases --user=myuser…
igorsantos07
  • 133
  • 1
  • 6
1
vote
0 answers

SQL Server - cloning a subset of a database for a test environment

I'm trying to set up an automated script which I can run on a schedule in SQL Server 2016 (probably nightly) to copy the structure of our production database and then transfer a subset of data into each table in the new database from the old one so…
wizzardmr42
  • 460
  • 1
  • 4
  • 8
1
vote
1 answer

How to copy a database using scripts only, no bak, no mdf, no ldf

Too often I face a terrible situation: I have a little time to take the backup of a customer db and download it for studying problems on it. The problem is that the database contains many BLOBs (as sql server filestream). Typically tabular data…
user193655
  • 145
  • 1
  • 7
1
vote
1 answer

Permission denied on function after schema copy

I had one schema 'public' and simple function in the schema named 'isvalidoption'. What was done: backup schema using pg_dump; rename schema to 'development'; create new schema 'public'; restore it from backup using pg_restore. The problem: when…
Roman Shevtsiv
  • 121
  • 4
0
votes
2 answers

Is it possible to copy a dev. database schema over a prod. one, but keep the prod. database' data?

I'm using MySQL 5.6. I have a production database and a development database. As I'm developing the application, I sometimes need to add new columns/tables to the database. Is it possible, when I'm ready to update the production database, to take…
Nathan Lutterman
  • 163
  • 1
  • 5
0
votes
1 answer

Oracle database transfer: database isolation

If I want to duplicate a schema with Oracle, I sometimes encounter problems with triggers or views that reference the schema name. I can search & replace the original schema name, but it is risky. In comparison, I can duplicate a postgres or mssql…
Kiruahxh
  • 101
  • 1
0
votes
1 answer

How to execute complete database script without checks on objects existence

I'm working on a customer data archiving. I created a complete database script to prepare the new database for archiving data with the same DB schema of the production one. At the moment I'm preparing a Test environment to verify all the steps we…
El See
  • 1
  • 2
0
votes
1 answer

View or build create table schema statement programmatically with T-SQL

Is it possible to view or build create statement for selected table? For example SSMS has feature Table > Script Table as > Create To > New Query Editor Window that can generate similar statement (without constraints though). But how to generate…
Muflix
  • 1,099
  • 5
  • 15
  • 27
0
votes
1 answer

How to daily copy selected data from MySQL and PostgreSQL servers to another PostgreSQL server

I have two source servers, a MySQL 5.7 and a PostgreSQL 9.6, respectively. I would like to daily update some tables from each source to another target PostgreSQL 11 server (via a cron-job). On the target server I also have some views involving the…
Vlad
  • 1
0
votes
2 answers

Sync database table schema and stored procedures

We have 2 databases. One is at our server, the other is local. We need to sync them both in a manner that local database has all data and server database has no data only empty tables and stored procedures and functions. In server database we are…
Sadiqabbas Hirani
  • 131
  • 1
  • 4
0
votes
1 answer

Can I ignore queries about extendedproperty?

I want to reuse the structure of a database in a new project. So using SSMS I use Generate Script and in settings I set to generate schema only. In the result there are too many queries about extendedproperty. Is there any important data or settings…
Ali Sheikhpour
  • 231
  • 2
  • 11