Oracle 12.1 upgrade

Remote Database Administration, Monitoring and Support

  • SATS Remote DBAis dedicated to optimizing database uptime and performance, and to reducing the cost of IT ownership. Our Like Clockwork RDBA service saves you time, recourses, continuity, and helps you to ally IT tasks with the business goals of your organization. Many other companies chose SATS for our dependability, cost-effectiveness, and expert technological support. SATS RDBA advantages help our customers achieve costs savings of up to 75% while increasing the quality of DBA coverage.
  • SATS Remote DBA could be a one stop service provider for all this platforms:
  • Oracle, PostgreSQL, Hadoop, MS SQL, MySQL, Mongo DB

For specific inquiries please contact SATS RDBA Services or call 650 889-5533.

Clone an existing SQL Server database

PROBLEM CASE

For development or testing purpose we need a copy of existing production database, made with different name and located in a different physical storage. There are other ways but the one below works and gives some control over objects creation.

SAMPLE SOLUTION STEPS

Here we clone the source MyouxDB to MyouxDBTest

  1. Using SQL Studio, create a new target database MyouxDBTest
  2. Grant/create user who will access this database with SQL authorization. (webousr).
    Grant to this user all necessary ownership privs on this new empty database.
    (under Security->Login)
  3. Migrate the schema from old to new. To extract the schema use Database properties
    pop-up on a right mouse and choose Generate Scripts from database tasks list. This will
    produce a file with DDL for the whole database.
    1. Suppose I scripted the database into Zschema.sql file.
      Next install this schema into newly created database - modify the Zschema.sql script and
      change the database name being used to my desired target database which is MyouxDBTest (one place to edit only)
    2. Now install this schema into my MyouxDBTest by running the script as follows
      
          sqlcmd -U webousr -P wpassword -S dbserver1 -d MyoxDBTest -i Zschema.sql -o Zschema.log
      
      
    3. review Zschema.log file for any errors.
  4. Now move actual data - use Export wizard under Database Tasks.
    In right mouse on Db properties choose Export task and follow the wizard to export from source MyouxDB to target MyouxDBTest.

Questions/Comments? - feel free to contact Cerberus (which is me) on RDBA.net forums under Oracle DBA corner.