Restore SQL Server 2012 backup to a SQL Server 2008 database

You have couple of options :

Option A : Script out database in compatibility mode using Generate script option :

Note : If you script out database with schema and data, depending on your data size, the script will be massive and wont be handled by SSMS, sqlcmd or osql (might be in GB as well).

DjVa5

Option B:

First script out tables first with all Indexes, FK’s, etc and create blank tables in the destination database – option with SCHEMA ONLY (No data).

Use BCP to insert data

  1. bcp out the data using below script. set SSMS in Text Mode and copy the output generated by below script in a bat file.
    -- save below output in a bat file by executing below in SSMS in TEXT mode
    
    -- clean up: create a bat file with this command --> del D:\BCP\*.dat 
    
    select '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" ' /* path to BCP.exe */
        +  QUOTENAME(DB_NAME())+ '.' /* Current Database */
        +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'            
        +  QUOTENAME(name)  
        +  ' out D:\BCP\'  /* Path where BCP out files will be stored */
        +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' 
        +  REPLACE(name,' ','') 
        + '.dat -T -E -SServerName\Instance -n' /* ServerName, -E will take care of Identity, -n is for Native Format */
    from sys.tables
    where is_ms_shipped = 0 and name <> 'sysdiagrams'                       /* sysdiagrams is classified my MS as UserTable and we dont want it */
    /*and schema_name(schema_id) <> 'unwantedschema'    */                             /* Optional to exclude any schema  */
    order by schema_name(schema_id)
  2. Run the bat file that will generate the .dat files in the folder that you have specified.
  3. Run below script on the destination server with SSMS in text mode again.
    --- Execute this on the destination server.database from SSMS.
    
    --- Make sure the change the @Destdbname and the bcp out path as per your environment.
    
    declare @Destdbname sysname
    set @Destdbname = 'destinationDB' /* Destination Database Name where you want to Bulk Insert in */
    select 'BULK INSERT ' 
    /*Remember Tables must be present on destination database */ 
    + QUOTENAME(@Destdbname) + '.' 
    + QUOTENAME(SCHEMA_NAME(SCHEMA_ID)) 
    + '.' + QUOTENAME(name) 
    + ' from ''D:\BCP\' /* Change here for bcp out path */ 
    + REPLACE(SCHEMA_NAME(schema_id), ' ', '') + '_' + REPLACE(name, ' ', '') 
    + '.dat'' with ( KEEPIDENTITY, DATAFILETYPE = ''native'', TABLOCK )' 
    + char(10) 
    + 'print ''Bulk insert for ' + REPLACE(SCHEMA_NAME(schema_id), ' ', '') + '_' + REPLACE(name, ' ', '') + ' is done... ''' 
    + char(10) + 'go'
       from sys.tables
       where is_ms_shipped = 0
    and name <> 'sysdiagrams' /* sysdiagrams is classified my MS as UserTable and we dont want it */
    and schema_name(schema_id) <> 'unwantedschema' /* Optional to exclude any schema */
        order by schema_name(schema_id) 
  4. Run the output using SSMS to insert data back in the tables.

This is very fast bcp method as it uses Native mode.

How do I generate insert statements/ script (including data) with Sql server 2012

  • Right click on your database and select Tasks -> Generate Scripts
  • Click Next to move past the intro screen and onto the next page, then select the objects you want to script. Note that you can script all SQL objects here, not just tables. This is because this wizard allows you to script schema and data. Importantly, schema only is the default, which you’ll need to change on the next page. Click Next.
  • Now you’ll be presented with a set of options mainly relating to how the script is going to be output, however there is an unassuming button labelled Advanced. Clicking this button will give you a dialog with a list of options. The last option under the General section is “Types of data to script”, which you should change from “Schema only” to “Data only”, or “Schema and data”.
  • Finally, decide where you want the script to do and you should be able to generate your INSERT statements.

Curtsy: https://raresql.com/2012/09/30/generating-insert-statements-in-sql-server-2012/

Generating INSERT statements in SQL Server 2012

Recently, I wrote an article on “Generating Insert statements, data & schema using SQL Server Publishing Wizard”. It is a very nice tool to generate insert into statement from table data.

In SQL server 2012, the feature of this tool plus additional features are provided. Lets proceed step by step to generate insert into statement from table data in SQL server 2012.

Step 1 :

In the first step, you need to right click on Databases >> Tasks >> Generate Scripts…

Step 2 :

This is the welcome screen. If you don’t need this screen again and again you can check on “Do not show this page again”.

Step 3 :

In this step, you need to generate the script for one object or all objects available in the database. You can further drill down if you need to generate any table. For example :

Setup 4 :

The next step is to define the script location, clip board or in a new window. In the same step, if you click on Advanced button, it will give you further control on scripting.

This is the advance screen, in this screen you can choose whether you need data, schema or data and schema scripts.’

But if you need only Insert into statement from table data, you must select data only. By default it is schema only.

It also allows you to select the SQL version.

Step 5 :

In this step it will display the summary of your selection. If you need you can go back and change the settings.

Step 6 :

In this step, it will display the result.

Advertisements

SQL Server: SELECT TOP Statement (TOP n th Position) by using WITH TIES

SELECT TOP 8000 WITH TIES  *
FROM [Hall_Info].[dbo].[ug_applicant_general_info]
ORDER BY HSC_TOTAL DESC, HSC_MATH_GP DESC, HSC_PHY_GP DESC, HSC_CHEM_GP DESC, HSC_ENG_GP DESC, SSC_GPA_MARKS_OBTAINED DESC

[Curtsy] Original Post: http://www.techonthenet.com/sql_server/select_top.php

SQL Server: SELECT TOP Statement

This SQL Server tutorial explains how to use the SELECT TOP statement in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) SELECT TOP statement is used to retrieve records from one or more tables in SQL Server and limit the number of records returned based on a fixed value or percentage.

Syntax

The syntax for the SELECT TOP statement in SQL Server (Transact-SQL) is:

SELECT TOP (top_value) [ PERCENT ] [ WITH TIES ]
expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]];

Parameters or Arguments

TOP (top_value)
Returns the top number of rows in the result set based on top_value. For example, TOP(10) would return the top 10 rows from the full result set.
PERCENT
Optional. If PERCENT is specified, then the top rows are based on a percentage of the total result set (as specfied by the top_value). For example, TOP(10) PERCENT would return the top 10% of the full result set.
WITH TIES
Optional. If the WITH TIES clause is specified, then rows tied in last place within the limited result set are returned. This may result in more rows be returned than the TOP parameter permits.
expressions
The columns or calculations that you wish to retrieve.
tables
The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
WHERE conditions
Optional. The conditions that must be met for the records to be selected.
ORDER BY expression
Optional. It is used in the SELECT TOP statement so that you can order the results and target those records that you wish to return. ASC is ascending order and DESC is descending order.

Example – Using TOP keyword

Let’s look at a SQL Server example, where we use the TOP keyword in the SELECT statement.

For example:

SELECT TOP(5)
employee_id, last_name, first_name
FROM employees
WHERE last_name = 'Anderson'
ORDER BY employee_id;

This SQL Server SELECT TOP example would select the first 5 records from the employees table where the last_name is ‘Anderson’. If there are other records in the employees table that have a last_name of ‘Anderson’, they will not be returned by the SELECT statement.

You could modify this example to include the WITH TIES clause as follows:

SELECT TOP(5) WITH TIES
employee_id, last_name, first_name
FROM employees
WHERE last_name = 'Anderson'
ORDER BY employee_id;

The WITH TIES clause would include rows that may be tied in last place within the limited result set. So if the 5th top record is a tie, then all records in this tie position would be returned by the SELECT TOP statement. This will result in more than 5 records being returned.

Example – Using TOP PERCENT keyword

Let’s look at a SQL Server example, where we use the TOP PERCENT keyword in the SELECT statement.

For example:

SELECT TOP(10) PERCENT
employee_id, last_name, first_name
FROM employees
WHERE last_name = 'Anderson'
ORDER BY employee_id;

This SQL Server SELECT TOP example would select the first 10% of the records from the full result set. So in this example, the SELECT statement would return the top 10% of records from the employees table where the last_name is ‘Anderson’. The other 90% of the result set would not be returned by the SELECT statement.

You could modify this example to include the WITH TIES clause as follows:

SELECT TOP(10) PERCENT WITH TIES
employee_id, last_name, first_name
FROM employees
WHERE last_name = 'Anderson'
ORDER BY employee_id;

The WITH TIES clause would include rows that may be tied in last place within the limited result set. So if the last position in the SELECT TOP(10) PERCENT record set is a tie, then these tied records would be returned by the SELECT TOP statement. This will result in more than 10% of the full record set being returned.

 

Finding Duplicate Records Using GROUP BY in SQL Server

Original Post: http://www.tech-recipes.com/rx/49959/finding-duplicate-records-using-group-by-in-sql-server/

There are various times when we need to find duplicate records in SQL Server. It is possible to find duplicates using DISTINCT, ROW NUMBER as well as the GROUP BY approach.

Duplicate records can create problems sometimes when displaying reports or performing a Multiple Insert update. Finding duplicate records in a database needs further investigation. In some cases, duplicate records are positive, but it all depends on the data and the database design as well.

For example, if a customer has ordered the same product twice on the same date with the the same shipping and billing address, then this may result in a duplicate record.

Let us create a table Customer with First Name, Last Name, and Mobile Number fields.

CREATE TABLE CUSTOMER
(
FirstName VARCHAR(50),
LastName  VARCHAR(50),
MobileNo  VARCHAR(15)
);

INSERT INTO CUSTOMER VALUES ('Niraj','Yadav',989898);

INSERT INTO CUSTOMER VALUES ('Chetan','Gadodia',959595);
INSERT INTO CUSTOMER VALUES ('Chetan','Gadodia',959595);

INSERT INTO CUSTOMER VALUES ('Atul','Kokam',42424242);
INSERT INTO CUSTOMER VALUES ('Atul','Kokam',42424242);

INSERT INTO CUSTOMER VALUES ('Vishal','Parte',9394453);
INSERT INTO CUSTOMER VALUES ('Vishal','Parte',9394453);
INSERT INTO CUSTOMER VALUES ('Vishal','Parte',9394453);

INSERT INTO CUSTOMER VALUES ('Jinendra','Jain',121212);
INSERT INTO CUSTOMER VALUES ('Jinendra','Jain',121212);

SELECT * FROM CUSTOMER;

Finding Duplicates Records Using Group by in SQL Server (2)

Using the DISTINCT approach, we can quickly get unique rows in a table.

SELECT DISTINCT FirstName, LastName, MobileNo FROM CUSTOMER;

Finding Duplicates Records Using Group by in SQL Server (3)

However, this does not show how many times a row has been duplicated. Using the GROUP BY approach, we can find this.

Finding Duplicates Using GROUP BY

Adding grouping and a counting field to our display of FirstName, LastName and MobileNo combination shows how many times each customer’s name appears.

SELECT		FirstName, LastName, MobileNo, COUNT(1) as CNT
FROM		CUSTOMER
GROUP BY	FirstName, LastName, MobileNo;

Finding Duplicates Records Using Group by in SQL Server (4)

GROUP BY will show just one record for each combination of FirstName, LastName and MobileNo.

The count CNT shows how many times the row has been duplicated.
CNT = 1 indicates that row appears only once.

Let us filter out using the Having clause to exclude rows that appear only once.

SELECT		FirstName, LastName, MobileNo, COUNT(1) as CNT
FROM		CUSTOMER
GROUP BY	FirstName, LastName, MobileNo
HAVING		COUNT(1) > 1;

Finding Duplicates Records Using Group by in SQL Server (1)

How to update data in one table from corresponding data in another table in SQL Server

Format:

update t2
set t2.deptid = t1.deptid
from test1 t1, test2 t2
where t2.employeeid = t1.employeeid

Example:

USE [UG_AdmissionDb]
GO
-- exec sp_BackupData

ALTER PROCEDURE [dbo].[sp_Modified_Data]
AS
BEGIN          
    -- Begin Trnsaction
    BEGIN TRY
        BEGIN TRANSACTION        
            
            UPDATE t2
            SET t2.[BANGLA] = t1.BANGLA,
            t2.[ENGLISH] = t1.[ENGLISH],
            t2.[PHYSICS] = t1.[PHYSICS],
            t2.[CHEMISTRY] = t1.[CHEMISTRY],
            t2.[MATHEMATICS] = t1.[MATHEMATICS],
            t2.[BIOLOGY] = t1.[BIOLOGY],
            t2.[SUB_4TH] = t1.[SUB_4TH],
            t2.[GPA] = t1.[GPA],
            t2.[CGPA] = t1.[CGPA],
            t2.[LTGRADE] = t1.[LTGRADE]

            FROM [ReScrutiny].[dbo].[ug_data_full_board_hsc] as t1, [UG_AdmissionDb].[dbo].[ug_data_full_board_hsc]  as t2
            WHERE
              t2.[ROLL] = t1.[ROLL]
              AND t2.[REG] = t1.[REG]
              AND t2.[PASSYEAR] = t1.[PASSYEAR]
              AND t2.[BOARDNAME] = t1.[BOARDNAME]
    
        COMMIT
        select 'Process Successful!' IsOk
    END TRY
    BEGIN CATCH
        IF(@@TRANCOUNT > 0)
            ROLLBACK        
        select ERROR_MESSAGE() IsOk    
    END CATCH
END

Migrate MySQL to Microsoft SQL Server

Migrating MySQL Databases to SQL Server / SQL Azure

To successfully migrate objects and data from MySQL databases to SQL Server or SQL Azure, use the following process:

  1. Working with SSMA Projects (MySQL to SQL).

    After you create the project, you can set project conversion, migration, and type mapping options. For more information about project settings, see Setting Project Options (MySQL to SQL). For information about how to customize data type mappings, seeMapping MySQL and SQL Server Data Types (MySQL to SQL)

  2. Connecting to MySQL (MySQL to SQL)
  3. Connecting to SQL Server(MySQL to SQL)
  4. Mapping MySQL Databases to SQL Server Schemas(MySQLToSQL)
  5. Connecting to SQL Azure
  6. Optionally, Assessing MySQL Databases for Conversion (MySQL to SQL) to assess database objects for conversion and estimate the conversion time.
  7. Converting MySQL Databases (MySQL to SQL)
  8. Synchronization
  9. You can do this in one of the following ways:
    • Save a script and run it on SQL Server or SQL Azure.
    • Synchronize the database objects.
  10. Migrating MySQL Data into SQL Server/ SQL Azure
  11. If necessary, update database applications.

 

Getting Started with SSMA for MySQL(MySQLToSQL)

SQL Server Migration Assistant (SSMA) for MySQL lets you quickly convert MySQL database schemas to SQL Server or SQL Azure schemas, upload the resulting schemas into SQL Server or SQL Azure, and migrate data from MySQL to SQL Server or SQL Azure.

This topic introduces the installation process, and then helps familiarize you with the SSMA user interface.

To use SSMA, you first must install the SSMA client program on a computer that can access both the source MySQL database and the target instance of SQL Server or SQL Azure. Then, install the MySQL providers (MySQL ODBC 5.1 Driver (trusted)) on the computer that is running SSMA Client Program. For installation instructions, see Installing SSMA for MySQL(MySqlToSql)

To start SSMA, click Start, point to All Programs, point to SQL Server Migration Assistant for MySQL, and then click SQL Server Migration Assistant for MySQL. The first time that you start SSMA, a licensing dialog box will appear. You must license SSMA by using a Windows Live ID before you can use SSMA. Licensing instructions are included with the installation instructions in the Installing SSMA for MySQL Client topic.

After SSMA is installed and licensed, you can use SSMA to migrate MySQL databases to SQL Server or SQL Azure. It helps to become familiar with the SSMA user interface before you start. The following diagram shows the user interface for SSMA, including the metadata explorers, metadata, toolbars, output pane, and error list pane:

SSMA for MySql Graphical User InterfaceTo start a migration, you must:

  1. Create a new project.
  2. Connect to a MySQL database.
  3. After a successful connection, MySQL schemas will appear in MySQL Metadata Explorer. Right-click objects in MySQL Metadata Explorer to perform tasks such as create reports that assess conversions to SQL Server/SQL Azure.

You can also perform these tasks by using the toolbars and menus.

You must also connect to an instance of SQL Server. After a successful connection, a hierarchy of SQL Server databases will appear in SQL Server Metadata Explorer. After you convert MySQL schemas to SQL Server schemas, select those converted schemas in SQL Server Metadata Explorer, and then synchronize the schemas with SQL Server.

You must connect to SQL Azure if you have selected SQL Azure from the Migrate to dropdown in new project dialog box. After a successful connection, a hierarchy of SQL Azure databases will appear in SQL Azure Metadata Explorer. After you convert MySQL schemas to SQL Azure schemas, select those converted schemas in SQL Azure Metadata Explorer, and then synchronize the schemas with SQL Azure.

After you synchronize converted schemas with SQL Server or SQL Azure, you can return to MySQL Metadata Explorer and migrate data from MySQL schemas into SQL Server or SQL Azure databases.

For more information about these tasks and how to perform them, see Migrating MySQL Databases to SQL Server / SQL Azure.

The following sections describe the features of the SSMA user interface.

Metadata Explorers

SSMA contains two metadata explorers to browse and perform actions on MySQL and SQL Server databases.

MySQL Metadata Explorer

MySQL Metadata Explorer shows information about MySQL schemas. By using MySQL Metadata Explorer, you can perform the following tasks:

SQL Server or SQL Azure Metadata Explorer

SQL Server or SQL Azure Metadata Explorer shows information about an instance of SQL Server or SQL Azure. When you connect to an instance of SQL Server or SQL Azure, SSMA retrieves metadata about that instance and stores it in the project file.

You can use this Metadata Explorer to select converted MySQL database objects, and then synchronize those objects with the instance of SQL Server or SQL Azure.

For more information, see Synchronization (MySQL to SQL Server / SQL Azure)

Metadata

To the right of each metadata explorer are tabs that describe the selected object. For example, if you select a table in MySQL Metadata Explorer, nine tabs will appear: Table, SQL, Type Mapping, Data, Settings, Charset Mapping, SQL Modes, Properties, andReport. The Report tab contains information only after you create a report that contains the selected object. If you select a table in SQL Server Metadata Explorer, three tabs will appear: Table, SQL and Data.

Most metadata settings are read-only. However, you can alter the following metadata:

  • In MySQL Metadata Explorer, you can alter type mappings, Charset Mapping, SQL Modes. To convert the altered type mappings or Charset Mapping or SQL Modes, make changes before you convert schemas.
  • In SQL Server Metadata Explorer, you can alter the table and index properties on the Table tab. To see these changes in SQL Server, make these changes before you load the schemas into SQL Server.

Changes made in a metadata explorer are reflected in the project metadata, not in the source or target databases.

Toolbars

SSMA has two toolbars: a project toolbar and a migration toolbar.

The Project Toolbar

The project toolbar contains buttons for working with projects, connecting to MySQL, and connecting to SQL Server or SQL Azure. These buttons resemble the commands on the File menu.

Migration Toolbar

The following table shows the migration toolbar commands:

Button Function
Create Report Converts the selected MySQL objects to SQL Server or SQL Azure objects, and then creates a report that shows how successful the conversion was.

This command is disabled unless objects are selected in MySQL Metadata Explorer.

Convert Schema Converts the selected MySQL objects to SQL Server or SQL Azure objects.

This command is disabled unless objects are selected in MySQL Metadata Explorer.

Migrate Data Migrates data from the MySQL database to SQL Server or SQL Azure. Before you run this command, you must convert the MySQL schemas to SQL Server or SQL Azure schemas, and then load the objects into SQL Server or SQL Azure.

This command is disabled unless objects are selected in MySQL Metadata Explorer.

Stop Stops the current process.

Menus

The following table shows the SSMA menus.

Menu Description
File Contains commands for working with projects, connecting to MySQL, and connecting to SQL Server or SQL Azure.
Edit Contains commands for finding and working with text in the details pages. To open Manage Bookmarks dialog, on the Edit menu click Manage Bookmarks. In the dialog you will see a list of existing bookmarks. You can use the buttons on the right side of the dialog to manage the bookmarks.
View Contains the Synchronize Metadata Explorers command. That synchronizes the objects between MySQL Metadata Explorer and SQL Server or SQL Azure Metadata Explorer. Also contains commands to show and hide the Output and Error List panes and an option Layouts to manage with the Layouts.
Tools Contains commands to create reports, convert schema, refresh from database, migrate objects and data, and Save as Script. Also provides access to the Global Settings, Default Project Settings and Project Settings dialog boxes.
Help Provides access to SSMA Help, to a dialog box for setting usage reporting options, and to the About dialog box.

Output Pane and Error List Pane

The View menu provides commands to toggle the visibility of the Output pane and the Error List pane:

  • The Output pane shows status messages from SSMA during object conversion, object synchronization, and data migration.
  • The Error List pane shows error, warning, and informational messages in a sortable list.

Introduction

This article describes a few simple steps in order to migrate MySQL into Microsoft SQL Server 2005. The technique is very easy, but useful if you plan to move your data from MySQL and upgrade it finally to a Microsoft SQL Server environment.

Background

Initially, I started my search for an article on CodeProject regarding MySQL->MS SQL migration without any success. I had an old PHPbb forum running, that needed to be upgraded to a Microsoft environment entirely. I could have just kept MySQL and Apache server, but instead I decided to migrate the entire concept of PHPbb to a YAF-forum.

Setup ODBC Connection to MySQL Database

This article will not go through how to setup a MySQL or Microsoft SQL server, but make sure you have downloaded at least the MySQL ODBC Connector from here.

For this article, I downloaded the MySQL ODBC Connector 5.1.

The setup of this connector is pretty simple:

  • Open your ODBC Data Source Administrator from the Control Panel -> Administrative Tools. Under the tab labelled as “System DSN”, press the “Add” button.Setup_ODBC1.jpg
  • On the “Create New Data Source” dialog that appeared, choose MySQL ODBC 5.1 Driver and then press the “Finish” button.Setup_ODBC2.jpg
  • After that, a MySQL connection configuration dialog will appear. Add your MySQL database account information in it, preferably the “root” account which has full access to your databases in MySQL. In this case, my database is called “tigerdb“. Do not change the port to anything other than 3306, unless during your MySQL server installation, you have defined something else.Setup_ODBC3.jpg
  • Press the “Test” button to ensure your connection settings are set properly and then the “OK” button when you’re done.

Create a Microsoft SQL Link to your MySQL Database

In this state, you are ready to establish a link towards MySQL database from your Microsoft SQL Server Management Studio. Open a query window and run the following SQL statement:

 Collapse | Copy Code
EXEC master.dbo.sp_addlinkedserver 
@server = N'MYSQL', 
@srvproduct=N'MySQL', 
@provider=N'MSDASQL', 
@provstr=N'DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; _
	DATABASE=tigerdb; USER=root; PASSWORD=hejsan; OPTION=3'

This script will produce a link to your MySQL database through the ODBC connection you just created in the previous stage of this article. The link will appear in the Microsoft SQL Server Management Studio like this:

Create_link.jpg

If it doesn’t show up in the treeview, press the refresh button.

Import Data between the Databases

Create a new database in Microsoft SQL Server. I called mine “testMySQL“. In the query window, run the following SQL statement to import table shoutbox from the MySQL database tigerdb, into the newly created database in Microsoft SQL called testMySQL.

 Collapse | Copy Code
SELECT * INTO testMySQL.dbo.shoutbox
FROM openquery(MYSQL, 'SELECT * FROM tigerdb.shoutbox')

That’s it!

Basic Steps for Migration of MySQL to SQL Server

To start a migration, you’ll need to perform the following high level steps:

  1. Create a new project.
  2. Connect to a MySQL database.
  3. After a successful connection, MySQL schemas will appear in MySQL Metadata Explorer. Right-click objects in MySQL Metadata Explorer to perform tasks such as create reports that assess conversions to SQL Server 2008 R2 Express. You can also perform these tasks by using the toolbars and menus.

You’ll then connect to your instance of SQL Server 2008 R2 Express. After a successful connection, a hierarchy of your existing databases will appear in SQL Server Metadata Explorer. After you convert MySQL schemas to SQL Server schemas, select those converted schemas in SQL Server Metadata Explorer, and then synchronize the schemas with SQL Server.

After you synchronize converted schemas with SQL Server 2008 R2 Express, you can return to MySQL Metadata Explorer and migrate data from MySQL schemas into target database.

Let’s walk through the specifics.

Create a MySQL Migration Project

To get started, you’ll create your new project using the File | New Project command.

02 Create Project

You’ll enter in your project name and then confirm that you are migrating to SQL Server. The Migrate To dropdown also allows you to choose SQL Azure, but that’s for another post. Once you make your selection, you are locked into the target backend.

Connect to a MySQL Database

To Connect to your MySQL instance, you’ll issue the File | Connect to MySQL command or click on the tool bar button that launches the following dialog:

03 Connect to MySQL

If you forgot to to install the MySQL ODBC driver mentioned at the beginning of this blog, simply go to the download site, install the driver, and then issue the Connect to MySQL command.

Create Report of Potential Conversion Issues

Once you are connected, you’ll see the MySQL instance in the MySQL Metadata Explorer. You’ll want to expand the Databases node along with the Sakila database node and then check the box next to Sakila. This selects the database you want to migrate. Next, right click on the Sakila database and select the Create Report command or press the Create Report command on the toolbar as shown below.

04 Create Report

Here is an example of the Assessment Report for the Sakila database.

05 Assessment Report

The Assessment Report window contains three panes:

  • The left pane contains the hierarchy of objects that are included in the assessment report. You can browse the hierarchy, and select objects and categories of objects to view conversion statistics and code.
  • The content of the right pane depends on the item that is selected in the left pane.

    If a group of objects is selected, such as schema, the right pane contains a Conversion statistics pane and Objects by Categories pane. The Conversion Statistics pane shows the conversion statistics for the selected objects. The Objects by Categories pane shows the conversion statistics for the object or categories of objects.

    If a function, procedure, table or view is selected, the right pane contains statistics, source code, and target code.

    • The top area shows the overall statistics for the object. You might have to expand Statistics to view this information.
    • The Source area shows the source code of the object that is selected in the left pane. The highlighted areas show problematic source code.
    • The Target area shows the converted code. Red text shows problematic code and error messages.
  • The bottom pane shows conversion messages, grouped by message number. You can click Errors, Warnings, or Info to view categories of messages, and then expand a group of messages. Click an individual message to select the object in the left pane and display the details in the right pane.

In future blog posts, we’ll work through the specific problems that are in this report. For now, we’ll ignore the problematic objects for the schema and data migration steps. For now, close the report and then uncheck Functions, Procedures and Views nodes to take them out of the conversion. Then uncheck the tables with errors as shown below.

06 Ignore Errors

Go ahead and click on the Create Reports command to verify that there are no errors.

Connect to SQL Server

It’s time to connect SSMA to your SQL Server 2008 R2 Express instance. For the Server name, you’ll need the server name and instance for the target server. Since we are using the WPI installation of SQL Server 2008 R2 Express, you’ll enter in the server name as .\SQLEXPRESS.

You can select an existing database to migrate to using the Database control. You can also type in the name of a new database. In this case, use Sakila as shown below.

07 Connect to SQL Server

Once you click connect, SSMA prompts you if you want to create the database. Choose Yes to create the new database. When connecting to SQL Server Express instances, you’ll receive the following warning indicating that you won’t be able to use the server-side data migration engine. This engine is used for larger migration projects.

08 No SQL Agent

You can Continue from this dialog to start the actual migration process.

Convert Schema

Now that you’ve connected to the target SQL Server instance, SSMA enables the Convert Schema command. Click the Convert Schema command. Once the conversion is finished, you should see the SQL Server Metadata Explorer populated with the tables listed in bold as shown below.

09 Convert schema

Synchronize with Database

To write the tables to the target, select the dbo node in the SQL Server Metadata Explorer and then issue the Tools | Synchronize with Database command. SSMA displays the Synchronize with Database dialog as shown below. In this example, the Tables node was manually expanded to show that no tables are actually on the database at this time.

10 Sync with database

When you click OK, SSMA issues the CREATE TABLE statements to create the objects on the SQL Server target. There are some errors in this example because many of the tables selected have foreign key relationships to some of the tables that we excluded earlier. These errors can be ignored for now.

Migrate Data

The last step is to migrate the data into the tables. To complete the migration, select the Tables node within the MySQL Metadata Explorer for the Sakila database. Then issue the Tools | Migrate Data command or press the command on the toolbar. The Data Migration process requires you to connect to the MySQL database and to the SQL Server database again. SSMA then proceeds with the data migration process and displays the Data Migration Reports as shown below.

11 Migrate Data

Using SQL Server Management Studio

The migrated tables are now ready on the target SQL Server instance. To see the results, launch SQL Server Management Studio (SSMS) and connect using the server name as .\SQLEXPRESS. Expand out the Databases node to see the Sakila database. Expand the out the Sakila database tables and then right click on the actor table and issue the Select Top 1000 Rows command to view the data as shown below.

12 Verifying the results

SQL Server Management Studio that is part of the WPI is a free rich Windows client tool from Microsoft that offers a rich development and management experience like  SQLyog and MONyog.

[Issue-1] Basic Responsibility of DBA (Database Backup, Restore, Data/ Database Migration): MySql to SQL Server Data and Schema Migration

SQL Server: Basic Job Responsibility of a DBA includes

1. Data Backup (Manual, Auto Backup, Schedule Backup, Schedule Backup to a Different Server- Make Share folder of other Server as a Drive)

2. Data Restore

3. Data Migration (MySql to SQL Server, among different BDMS (MySql, SQL Server, Oracle, MS Access, XML, Excel, FoxPro, Text etc) )

Database Related Project (DBA Task)

Task-1: Restore MySql Database from .sql Dump Backup (large size zip file)

Task-2: Migrate Data from MySql Database to SQL Server Database

Task-3: Generate Auto Data backup (.rar Schedule Step) and transfer to remote Server

Task-1:

–         Unzip and rename (.sql) dump backup

–         Put the .sql file to MySql bin folder

–         (cmd) >c:>mysql>bin> [using cd ..]

–         mysql –uroot (-p) database_name<file.sql (database will be restored from the dump backup; web interface can backup only 2M file)

–        [You can use MySqlyog tool to Backup or Restore Large MyQsl Dump database]

Task-2:

Tools List: Microsoft SQL Server Migration Assistant for MySQL

–         MySql 5.2 Database Server

–         MS SQL Server 2008

–         mysql-connector-odbc-5.1.13-win32.msi

–         SSMA for MySQL 5.2

–         mysql-ssma.license

–         SSMA for MySQL 5.2.exe

–         SSMA for MySQL 5.2 Extension Pack.exe

Migrate Schama and Data from MySql to SQL Server

–         Install the Above

–         Run Microsoft SQL Server Migration Assistant for MySQL

–         Create New Project

–         Connect to MySql

–         Connect to SQL Server

–         Create Schema by right click on table list (before that create SQL database)

–         Convert Schema

–         Synchronies with Database

–         Migrate Database/ Data

Task-3: [See Previous Post]

MySQL to SQL Server Migration: How to Use SSMA

[Updated 2/6/2012 Han Wong – Microsoft SQL Server Migration Assistant (SSMA) for MySQL v5.2.  The information provided below is still valid for SSMA for MySQL v5.2.  Users should download the lastest SSMA for MySQL]

In this blog, I’m going to walk you through the process of converting the MySQL Sakila-DB sample database toSQL Server 2008 R2 Express using the SQL Server Migration Assistant for MySQL v1.0 [Updated:  Please obtain the lastest SSMA for MySQL] (SSMA). The Sakila-DB database has tables, views, stored procedures, functions and triggers that make the conversion interesting. The sample is based on the Inno-DB example, but does have one MyISAM table. SSMA also allows you to migrate your MySQL databases to SQL Azure, but we’ll save that topic for another post.

Downloading SQL Server 2008 R2 Express and SSMA

The easiest way to download SQL Server 2008 R2 Express, SQL Server Management Studio and SSMA is through theMicrosoft Web Platform Installer (WPI). Once you’ve downloaded WPI, you can select from a variety of tools and products that can get you up and running using IIS, PHP, and SQL Server in no time.

00 Web Platform Installer

I’ll focus on the minimum set of tools you need to get SQL Server 2008 R2 Express and SSMA up and running. once you launch WPI, click on the Products tab at the top tool and then select Database in the navigation page. In the image above, I’ve already installed the tools, but for the new install, you’ll click on the Add buttons to the right of the circled products to get you up and running. If you are running your application under PHP, you might also want to select one of the two PHP drivers for SQL Server as well. Once you’ve selected your tools, just click on the install button to start the process.

Downloading the MySQL ODBC Driver

WPI is not without flaws. SSMA requires the “MySQL OSBC Driver 5.1 or above” download to connect to your MySQL instance that comes from the MySQL downloads site. Once at the Download Connector/ODBC page, your need to download either the x32 or x64 version of the driver based on the machine architecture for the system you are running the SSMA client. Just follow the installation instructions from the installer. The default installation settings will be good enough to get you going.

Other Helpful Downloads for SSMA and this Blog

You’ll also want to download the “Guide to Migrating from MySQL to SQL Server 2008” white paper, though this blog and others to follow will keep you on track.

If you don’t already have the Sakila-DB database for MySQL installed, the link to the download and instructions for installing it can be found at the blog post titled “Learn MySQL With Sakila sample Mysql Database

Using SSMA for MySQL

SQL Server Migration Assistant (SSMA) 2008 for MySQL lets you quickly convert MySQL database schemas to SQL Server 2008, SQL Server 2008 R2 or SQL Azure schemas, upload the resulting schemas the target instance and migrate the data using a single tool.

Licensing SSMA

SSMA is a free tool, but does require you to associate a Microsoft Live ID for identification purposes. You must download a registration key. To help you with the registration process, a License Key Required dialog box opens the first time that you start the SSMA program. Use the following instructions to download a license key and associate the key with SSMA.

To license SSMA

  1. Click Start, point to All Programs, point to Microsoft SQL Server Migration Assistant 2008 for MySQL, and then select Microsoft SQL Server Migration Assistant 2008 for MySQL.
  2. In the License Management dialog box, click the license registration page link.
  3. On the Sign In Web page, enter your Windows Live ID user name and password, and click Sign In.A Windows Live ID is a Hotmail e-mail address, MSN e-mail address, or Microsoft Passport account. If you do not have one of these accounts, you will have to create a new account. To create a new account, click the Sign up now button.
  4. On the SQL Server Migration Assistant for MySQL License Registration Web page, fill in at least the required fields, which are marked with a red asterisk, and then click Finish.
  5. In the File Download dialog box, click Save.
  6. In the Save As dialog box, locate the folder that is shown in the License Management dialog box, and then click Save.The default location is C:\Documents and Settings\user name\Application Data\Microsoft SQL Server Migration Assistant\m2ss.
  7. In the License Management dialog box, click Refresh License.

SSMA for MySQL User Interface

After SSMA is installed and licensed, you can use SSMA to migrate MySQL databases to SQL Server 2008 or SQL Azure. It helps to become familiar with the SSMA user interface before you start. The following diagram shows the user interface for SSMA, including the metadata explorers, metadata, toolbars, output pane, and error list pane:

01 SSMA MySQL UI OverviewS

Basic Steps for Migration of MySQL to SQL Server

To start a migration, you’ll need to perform the following high level steps:

  1. Create a new project.
  2. Connect to a MySQL database.
  3. After a successful connection, MySQL schemas will appear in MySQL Metadata Explorer. Right-click objects in MySQL Metadata Explorer to perform tasks such as create reports that assess conversions to SQL Server 2008 R2 Express. You can also perform these tasks by using the toolbars and menus.

You’ll then connect to your instance of SQL Server 2008 R2 Express. After a successful connection, a hierarchy of your existing databases will appear in SQL Server Metadata Explorer. After you convert MySQL schemas to SQL Server schemas, select those converted schemas in SQL Server Metadata Explorer, and then synchronize the schemas with SQL Server.

After you synchronize converted schemas with SQL Server 2008 R2 Express, you can return to MySQL Metadata Explorer and migrate data from MySQL schemas into target database.

Let’s walk through the specifics.

Create a MySQL Migration Project

To get started, you’ll create your new project using the File | New Project command.

02 Create Project

You’ll enter in your project name and then confirm that you are migrating to SQL Server. The Migrate To dropdown also allows you to choose SQL Azure, but that’s for another post. Once you make your selection, you are locked into the target backend.

Connect to a MySQL Database

To Connect to your MySQL instance, you’ll issue the File | Connect to MySQL command or click on the tool bar button that launches the following dialog:

03 Connect to MySQL

If you forgot to to install the MySQL ODBC driver mentioned at the beginning of this blog, simply go to the download site, install the driver, and then issue the Connect to MySQL command.

Create Report of Potential Conversion Issues

Once you are connected, you’ll see the MySQL instance in the MySQL Metadata Explorer. You’ll want to expand the Databases node along with the Sakila database node and then check the box next to Sakila. This selects the database you want to migrate. Next, right click on the Sakila database and select the Create Report command or press the Create Report command on the toolbar as shown below.

04 Create Report

Here is an example of the Assessment Report for the Sakila database.

05 Assessment Report

The Assessment Report window contains three panes:

  • The left pane contains the hierarchy of objects that are included in the assessment report. You can browse the hierarchy, and select objects and categories of objects to view conversion statistics and code.
  • The content of the right pane depends on the item that is selected in the left pane.If a group of objects is selected, such as schema, the right pane contains a Conversion statistics pane and Objects by Categories pane. The Conversion Statistics pane shows the conversion statistics for the selected objects. The Objects by Categories pane shows the conversion statistics for the object or categories of objects.

    If a function, procedure, table or view is selected, the right pane contains statistics, source code, and target code.

    • The top area shows the overall statistics for the object. You might have to expand Statistics to view this information.
    • The Source area shows the source code of the object that is selected in the left pane. The highlighted areas show problematic source code.
    • The Target area shows the converted code. Red text shows problematic code and error messages.
  • The bottom pane shows conversion messages, grouped by message number. You can click Errors, Warnings, or Info to view categories of messages, and then expand a group of messages. Click an individual message to select the object in the left pane and display the details in the right pane.

In future blog posts, we’ll work through the specific problems that are in this report. For now, we’ll ignore the problematic objects for the schema and data migration steps. For now, close the report and then uncheck Functions, Procedures and Views nodes to take them out of the conversion. Then uncheck the tables with errors as shown below.

06 Ignore Errors

Go ahead and click on the Create Reports command to verify that there are no errors.

Connect to SQL Server

It’s time to connect SSMA to your SQL Server 2008 R2 Express instance. For the Server name, you’ll need the server name and instance for the target server. Since we are using the WPI installation of SQL Server 2008 R2 Express, you’ll enter in the server name as .\SQLEXPRESS.

You can select an existing database to migrate to using the Database control. You can also type in the name of a new database. In this case, use Sakila as shown below.

07 Connect to SQL Server

Once you click connect, SSMA prompts you if you want to create the database. Choose Yes to create the new database. When connecting to SQL Server Express instances, you’ll receive the following warning indicating that you won’t be able to use the server-side data migration engine. This engine is used for larger migration projects.

08 No SQL Agent

You can Continue from this dialog to start the actual migration process.

Convert Schema

Now that you’ve connected to the target SQL Server instance, SSMA enables the Convert Schema command. Click the Convert Schema command. Once the conversion is finished, you should see the SQL Server Metadata Explorer populated with the tables listed in bold as shown below.

09 Convert schema

Synchronize with Database

To write the tables to the target, select the dbo node in the SQL Server Metadata Explorer and then issue the Tools | Synchronize with Database command. SSMA displays the Synchronize with Database dialog as shown below. In this example, the Tables node was manually expanded to show that no tables are actually on the database at this time.

10 Sync with database

When you click OK, SSMA issues the CREATE TABLE statements to create the objects on the SQL Server target. There are some errors in this example because many of the tables selected have foreign key relationships to some of the tables that we excluded earlier. These errors can be ignored for now.

Migrate Data

The last step is to migrate the data into the tables. To complete the migration, select the Tables node within the MySQL Metadata Explorer for the Sakila database. Then issue the Tools | Migrate Data command or press the command on the toolbar. The Data Migration process requires you to connect to the MySQL database and to the SQL Server database again. SSMA then proceeds with the data migration process and displays the Data Migration Reports as shown below.

11 Migrate Data

Using SQL Server Management Studio

The migrated tables are now ready on the target SQL Server instance. To see the results, launch SQL Server Management Studio (SSMS) and connect using the server name as .\SQLEXPRESS. Expand out the Databases node to see the Sakila database. Expand the out the Sakila database tables and then right click on the actor table and issue the Select Top 1000 Rows command to view the data as shown below.

12 Verifying the results

SQL Server Management Studio that is part of the WPI is a free rich Windows client tool from Microsoft that offers a rich development and management experience like  SQLyog and MONyog.

SQL Server Auto Backup: Create Folder Everyday Using xp_cmdshell and Automatic Backup

xp_cmdshell Server Configuration Option

Introduced in SQL Server 2005, the xp_cmdshell option is a server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system. By default, the xp_cmdshell option is disabled on new installations and can be enabled by using the Policy-Based Management or by running the sp_configure system stored procedure as shown in the following code example:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
How to Enable & Disable XP_CMDSHELL using SP_CONFIGUR

Introduction

In this tip we will take a look at the step to follow to enable or disable XP_CMDSHELL using SP_CONFIGURE system stored procedure. In order to use XP_CMDSHELL you need to be a system administrator. Read the following article which explains how to allow non-administrators to use XP_CMDSHELL.

Error Message Received when XP_CMDSHELL is disabled

You would end up getting the below mentioned error message when XP_CMDSHELL is disabled.

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.

How to Enable XP_CMDSHELL using SP_CONFIGURE

A database administrator can enable XP_CMDSHELL using SP_CONFIGURE system stored procedure. T o enable XP_CMDSHELL execute the below mentioned script.

Use Master
GO

EXEC master.dbo.sp_configure ‘show advanced options’, 1
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure ‘xp_cmdshell’, 1
RECONFIGURE WITH OVERRIDE
GO

Query Output

Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install. Configuration option ‘xp_cmdshell’ changed from 0 to 1. Run the RECONFIGURE statement to install.

How to Disable XP_CMDSHELL using SP_CONFIGURE

It is always considered as a best practice to disable XP_CMDSHELL when not in use. A database administrator can disable XP_CMDSHELL using SP_CONFIGURE system stored procedure. To disable XP_CMDSHELL execute the below mentioned script.

Use Master
GO

EXEC master.dbo.sp_configure ‘xp_cmdshell’, 0
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure ‘show advanced options’, 0
RECONFIGURE WITH OVERRIDE
GO

Query Output

Configuration option ‘xp_cmdshell’ changed from 1 to 0. Run the RECONFIGURE statement to install.
Configuration option ‘show advanced options’ changed from 1 to 0. Run the RECONFIGURE statement to install.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_DBBackup] Script Date: 11/06/2013 12:46:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[sp_DBBackup]
— exec sp_DBBackup
as
declare @DBString varchar(max),
@DelString varchar(400),
@FinalBackup varchar(max),
@dbnAME VARCHAR (max),
@MonthlyFolde varchar(500),
@DailyFolderName varchar(500),
@CreateFolder varchar(2000),
@CopyCommand varchar(500),
@destination varchar(200),
@DatabaseName varchar(50)
declare CursorName cursor for
SELECT name FROM sys.databases WHERE name NOT IN (‘POS’,’KSInventory’,’master’, ‘tempdb’, ‘model’, ‘msdb’,’Accounts_Backup’,’ReportServerTempDB’,’ReportServer’,’distribution’)
open CursorName

Fetch NEXT FROM CursorName into @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
set @MonthlyFolde = DATENAME(MONTH,GETDATE()) + DATENAME(YEAR,GETDATE())
set @DailyFolderName = DATENAME(DAY,GETDATE()) + DATENAME(MONTH,GETDATE()) + DATENAME(YEAR,GETDATE())

set @CreateFolder = ‘IF exist D:\DatabaseBackup\’ + @MonthlyFolde + ‘ (mkdir D:\DatabaseBackup\’ + @MonthlyFolde + ‘\’ + @DailyFolderName + ‘) ELSE (MKDIR D:\DatabaseBackup\’ + @MonthlyFolde + ‘\’ + @DailyFolderName + ‘)’
EXEC XP_CMDSHELL @CreateFolder
set @dbnAME = @DatabaseName + ‘_’+ CONVERT(varchar(12),GETDATE(),112) + ‘_’ + SUBSTRING( CONVERT(varchar(12),GETDATE(),108) ,1,2) + SUBSTRING( CONVERT(varchar(12),GETDATE(),108) ,4,2) +
SUBSTRING( CONVERT(varchar(12),GETDATE(),108) ,7,2)
set @DBString = ‘D:\DatabaseBackup\’ + @MonthlyFolde + ‘\’ + @DailyFolderName + ‘\’ + @dbnAME
set @FinalBackup = @DBString + ‘.bak’
set @DelString = ‘DEL ‘ + @FinalBackup

BACKUP DATABASE @DatabaseName TO DISK = @FinalBackup
— COMPAC RAR
DECLARE @SERVER VARCHAR(100), @COMMAND VARCHAR(300), @FILE VARCHAR(100)
SET @SERVER = @@SERVERNAME
SET @COMMAND = ‘”D:\DatabaseBackup\Rar” a D:\DatabaseBackup\’ + @MonthlyFolde + ‘\’ + @DailyFolderName + ‘\’ + @dbnAME + ‘.rar D:\DatabaseBackup\’ + @MonthlyFolde + ‘\’ + @DailyFolderName + ‘\’ + @dbnAME + ‘.bak’

–select @COMMAND
EXEC XP_CMDSHELL @COMMAND
—- EXCLUIR FILE BACKUP
EXEC XP_CMDSHELL @DelString

Fetch NEXT FROM CursorName into @DatabaseName
END
CLOSE CursorName
DEALLOCATE CursorName