Sql-server – SQL Server Partial Only Database Move

backuprestoresql server

My company purchased part of another company. Now they have a large SQL Server 20TB database that is supposed to be moved to our data center. The problem is that only part of the database is being moved because the rest was not part of the merger.

For instance, part numbers and stuff are moving but only parts that are part of the merger deal. The rest is still considered the other companies property.

Is this possible?

I am a new Jr DBA and I mostly focus on Oracle not SQL Server.

Best Answer

If the objects you want to move are part of a separate filegroup, then you can do a filegroup backup and restore. This is a little tricky - with SQL Server, you can do a piecemeal restore of the primary filegroup, plus other filegroups, and the objects are then online. However, this won't work if the database wasn't designed for this from the start (by separating objects into filegroups.)

If the objects are relatively small, and if all objects are in a filegroup other than the primary, AND if you were allowed to make changes to the database, then you could create a new filegroup, move the objects into it, and then restore just that filegroup. However, you still have to restore the primary filegroup first, so this only works if there's not much in the primary filegroup.

If those aren't the case, you could set up a new database in the other company's data center, and copy the objects in that you'd like to transfer over. You could do this with simple insert statements, or by setting up replication into the new database.

None of what I've said above is easy - but hopefully it plants a few seeds on other ways you could pull it off.

Define the requirements

I can give you an example of how to model multilingual name data by assuming these business requirements:

The database should store the names of the ingredients of drugs in English, French and Spanish, and more languages yet to be defined.
Each ingredient is known by one name in each langauge.

Identify the languages

You need a way to identify langauges in your database so that you can associate drug names with a particular langauge.

A simple standard for identifying languages is ISO 619-1, which defines a two-letter code for the world's most common languages. For example, the code for English is en, French is fr, and Spanish is es.

The following SQL creates a table with a language code column, then populates the table with the example languages:

CREATE TABLE Languages (
  LanguageCode CHAR(2) NOT NULL,
  CONSTRAINT PK_Languages PRIMARY KEY (LanguageCode),
  CONSTRAINT CK_LowerAlpha CHECK (LanguageCode NOT LIKE '%[^a-z]%' COLLATE Latin1_General_BIN2)
);

INSERT INTO Languages (
  LanguageCode
)
VALUES
  ('en'),
  ('fr'),
  ('es');

There is nothing in your entity model that corresponds to this table, which could explain why you found it difficult to model multiligual data.

Identify the chemicals

Next you need a way to identify the chemicals that compose each drug.

A common way to identify chemicals is CAS registry numbering, which defines a numerical code for every chemical known to Science. For example, the code for water is 7732-18-5, formaldehyde is 50-00-0, and mustard oil is 8007-40-7.

The following SQL creates a table with a chemical code column, then populates the table with the example chemicals:

CREATE TABLE Chemicals (
  ChemicalCode VARCHAR(12) NOT NULL,
  CONSTRAINT PK_Chemicals PRIMARY KEY (ChemicalCode),
  CONSTRAINT CK_Dashes CHECK (ChemicalCode LIKE '%_-__-_'),
  CONSTRAINT CK_Digits CHECK (REPLACE(ChemicalCode, '-', '') NOT LIKE '%[^0-9]%')
);

INSERT INTO Chemicals (
  ChemicalCode
)
VALUES
  ('7732-18-5'),
  ('50-00-0'),
  ('8007-40-7');

The Chemicals table roughly corresponds to the Drugs_Ingredients entity in your diagram, but lacks a column for the name.

For simplicity, the table does not enforce the checksum constraint. You should enforce the checksum constraint before using CAS registry numbers in production.

Identify the chemical name

If you were to store the name in a column in the Chemicals table, it would be difficult to support more than one language. You could have one column per langauge, but you would have to modify the schema every time you wanted to support a new langauge.

To free yourself from these limitations, you should model the chemical name as an identifiable entity in itself. You can identify the chemical name using a compound identity of the chemical code and the language code.

The following SQL creates a table with columns for the chemical code, the language code, and the checmical name, then populates the table with one name in each language for each chemical:

CREATE TABLE ChemicalNames (
  ChemicalCode VARCHAR(12) NOT NULL,
  LanguageCode CHAR(2) NOT NULL,
  ChemicalName NVARCHAR(50) NOT NULL,
  CONSTRAINT PK_ChemicalNames PRIMARY KEY (ChemicalCode, LanguageCode),
  CONSTRAINT FK_ChemicalNames_ChemicalCode FOREIGN KEY (ChemicalCode) REFERENCES Chemicals(ChemicalCode),
  CONSTRAINT FK_ChemicalNames_LanguageCode FOREIGN KEY (LanguageCode) REFERENCES Languages(LanguageCode)
);

INSERT INTO ChemicalNames (
  ChemicalCode,
  LanguageCode,
  ChemicalName
)
VALUES
  ('7732-18-5', 'en', 'Water'),
  ('7732-18-5', 'fr', 'Eau'),
  ('7732-18-5', 'es', 'Agua'),
  ('50-00-0', 'en', 'Formaldehyde'),
  ('50-00-0', 'fr', 'Méthanal'),
  ('50-00-0', 'es', 'Formaldehído'),
  ('8007-40-7', 'en', 'Mustard oil'),
  ('8007-40-7', 'fr', 'Huile de moutarde'),
  ('8007-40-7', 'es', 'Aceite de mostaza');

The ChemicalName column is of type NVARCHAR so that it can store any Unicode string. You should always use Unicode in international applications to store text so that you can handle the writing systems of different langauges in a uniform way.

Query the chemical names

I can think of two questions that you might reasonably ask of your database. With the ChemicalNames table, you can answer both of them with simple queries.

What are the names of all the chemicals in Spanish?

Query:

SELECT ChemicalCode, ChemicalName
FROM ChemicalNames
WHERE LanguageCode = 'es';

Result:

CHEMICALCODE    CHEMICALNAME
50-00-0 Formaldehído
7732-18-5   Agua
8007-40-7   Aceite de mostaza

What is water called in each langauge?

Query:

SELECT LanguageCode, ChemicalName
FROM ChemicalNames
WHERE ChemicalCode = '7732-18-5';

Result:

LANGUAGECODE    CHEMICALNAME
en  Water
es  Agua
fr  Eau

Sql-server – Move named instance to another server

You are not moving instances, you are copying databases via backup restore. All you need is to get the syntax right, as per RESTORE spec:

<general_WITH_options> [ ,...n ]::= 
--Restore Operation Options
   MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' 
          [ ,...n ]

Each WITH option is separated from the preceding with a comma. What you're missing is the , and you need to move the files into the destination location:

RESTORE DATABASE Backup_Test
FROM DISK = 'C:\SQLBackup\Backup_Tesk.bak' \\\Stored in the destination
WITH REPLACE
 , MOVE 'Backup_Test' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\Backup_Test.mdf',
 , MOVE 'Backup_Test_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\Backup_Test_log.ldf'

Judging from your folder names (MSSQL10 vs. MSSQL10_50) your source server is SQL Server 2008 (not R2) and only the destination is SQL Server 2008 R2. Which means that the copy will upgrade the database and the database will never be possible to copy/restore on the original non-R2 instance.