Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MSSQL .dmp to MySQL command line???

Posted on 2004-10-19
9
Medium Priority
?
1,110 Views
Last Modified: 2012-06-27
Is there such a tool that will take a MSSQL backup file .dmp and convert it importing it into MySQL on a command line?  Is there a tool we can do this with that would not require connecting to the MSSQL server?  I don't want to have to connect to the MSSQL server at all.  Its the company's phone system that I am consulting for and its their lifeblood.  We are converting all their systems to MySQL/PHP and the phones are on a server based PBX.  It exports/backups .dmp easily.

Thanks,

Dave
0
Comment
Question by:lucasdeskywalker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
9 Comments
 
LVL 6

Accepted Solution

by:
btutt earned 1500 total points
ID: 12372447
Believe it or not I have created exactly this. The code is perhaps not perfect, but it works for me very well. I have this procedure:
--example usage:
-- Restore_Script_from_backup_file_new '<Full qualified path to backupfile>'
Create Procedure Restore_Script_from_backup_file @backupfile varchar(100)
as
 
Set nocount on

DECLARE @SQL_Statement varchar(300),@SQL_Statement1 varchar(300)
DECLARE @logicalname varchar(300), @physicalname varchar(300)
DECLARE @dbname varchar(50)
DECLARE @error bit


create table #backup_records
(
  LogicalName varchar(255),
  PhysicalName varchar(255),
  Type        varchar(2),
  Groupname varchar(25),
  Size_DB_File  decimal,
  Max_Size_File decimal                   
)

create table #backup_records_header_native_70
(
 backup_name varchar(200),
 backup_desc varchar(50),
 backup_type int,
 expiration_date datetime,
 compressed int,
 position int,
 device_type int,
 user_name1  varchar(50),
 server_name varchar(50),
 database_name varchar(50),
 version int,
 dbcreation datetime,
 Backup_size numeric (20,0),
 FirstLSN numeric (25,0),
 LastLSN  numeric (25,0),
 Checkpoint1LSN numeric (25,0),
 DatabaseBackupLsn numeric(25,0),
 BackupStartDate datetime,
 BackupFinishDate  datetime,
 sortorder int,
 Codepage int,
 UnicodelocaleID int,
 UnicodeComparisonStyle int,
 CompatibilityLevel int,
 SoftwareVendorId int,
 SoftwareVersionMajor int,
 SoftwareVersionMinor int,
 SoftwareVersionBuild int,
 MachineName varchar(50)
 )

create table #backup_records_header_native_2000
(
 backup_name varchar(200),
 backup_desc varchar(50),
 backup_type int,
 expiration_date datetime,
 compressed int,
 position int,
 device_type int,
 user_name1  varchar(50),
 server_name varchar(50),
 database_name varchar(50),
 version int,
 dbcreation datetime,
 Backup_size numeric (20,0),
 FirstLSN numeric (25,0),
 LastLSN  numeric (25,0),
 Checkpoint1LSN numeric (25,0),
 DatabaseBackupLsn numeric(25,0),
 BackupStartDate datetime,
 BackupFinishDate  datetime,
 sortorder int,
 Codepage int,
 UnicodelocaleID int,
 UnicodeComparisonStyle int,
 CompatibilityLevel int,
 SoftwareVendorId int,
 SoftwareVersionMajor int,
 SoftwareVersionMinor int,
 SoftwareVersionBuild int,
 MachineName varchar(50),
 Flags bit,
 BindingID uniqueidentifier,
 RecoveryForkID uniqueidentifier,
 Collation varchar(50)
 )
create table #backup_records_header_lite
(
 Filenumber int,
 backupformat int,
 Guid1 uniqueidentifier,
 backup_name varchar(50),
 Backup_Description varchar(200),
 Backup_type int,
 expiration datetime,
 Compressed int,
 Position int,
 Device_type int,
 UserName varchar(50),
 Server_Name varchar(50),
 Database_Name varchar(50),
 DatabaseVersion int,
 DatabaseCreationDate datetime,
 BackupSize  numeric(20,0),
 FirstLSN numeric(25,0),
 LastLSN numeric(25,0),
 CheckpointLSN numeric(25,0) ,
 DifferentialBaseLsn numeric(25,0),
 BackupStartDate datetime,
 BackupFinishDate  datetime,
 sortorder int,
 Codepage int,
 CompatibilityLevel int,
 SoftwareVendorId int,
 SoftwareVersionMajor int,
 SoftwareVersionMinor int,
 SoftwareVersionBuild int,
 MachineName varchar(50),
 Bindingid uniqueidentifier,
 RecoveryForkId uniqueidentifier,
 Encryption1 bit
 )


exec('restore filelistonly from disk=' + '''' + @backupfile + '''')

if  @@error = 0
--if UPPER(@type) ='NATIVE'
BEGIN
Set @error=0

Set @SQL_Statement='insert into #backup_records exec(' + '''' + 'restore filelistonly from disk=''''' + @backupfile  + '''''' +  '''' + ')'
END

ELSE

BEGIN
Set @error=1
Print replicate('*',60)
Print 'ALthough error occurred, script will attempt to render command script' + Char(13) + Char(13)
Print replicate('*',30)
Print 'Script Starts here'
Print replicate('*',30)
Set @SQL_Statement='insert into #backup_records exec(' + '''' + 'master..xp_restore_filelistonly @filename=''''' + @backupfile  + '''''' +  '''' + ')'
END


--Now since we know the error it will be possible to get the database name from the header

if @error=1 --litespeed  restore
BEGIN
Set @SQL_Statement1='insert into #backup_records_header_lite exec(' + '''' + 'master..xp_restore_headeronly @filename=''''' + @backupfile  + '''''' +  '''' + ')'
END


ELSE
--A this point the problem is that the header restore command will render different table structures between 7 and 2000 versions

BEGIN
declare @sql_version varchar(300)
select @sql_version =@@version
if Patindex('%SQL Server  2000%',@sql_version)=0
BEGIN
Set @SQL_Statement1='insert into #backup_records_header_native_70 exec(' + '''' + 'restore headeronly from disk=''''' + @backupfile  + '''''' +  '''' + ')'

END

else
BEGIN
Set @SQL_Statement1='insert into #backup_records_header_native_2000 exec(' + '''' + 'restore headeronly from disk=''''' + @backupfile  + '''''' +  '''' + ')'
END

END

--print @SQL_Statement1
exec(@SQL_Statement1)

exec(@SQL_Statement)
--print @SQL_Statement

if @error=1

BEGIN
select distinct @dbname = Database_Name from  #backup_records_header_lite

END

ELSE

BEGIN
if Patindex('%SQL Server  2000%',@sql_version)<>0
select distinct @dbname = Database_Name from  #backup_records_header_native_2000

else
select distinct @dbname = Database_Name from  #backup_records_header_native_70
END


Print replicate('*',30)
Print 'Script Starts here'
Print replicate('*',30)
DECLARE Script_Cursor CURSOR FOR
SELECT LogicalName, PhysicalName
FROM #backup_records


OPEN Script_Cursor

FETCH NEXT FROM Script_Cursor INTO @logicalname, @physicalname

if @error=1
  BEGIN
  PRINT 'master.dbo.xp_restore_database @databasename=' +  @dbname + ', ' + '@filename =' + '''' + @backupfile + '''' + ',' + Char(13)       
  END      

else
 BEGIN
 PRINT 'restore database ' +  @dbname +  ' from  disk=' + '''' + @backupfile + '''' + char(13)

 END

Declare @loop int

set @loop = 0

WHILE @@FETCH_STATUS = 0
BEGIN

 if @error=1
  BEGIN
   PRINT '@with =' + '''' + 'MOVE' +  ' "' + @logicalname + '" ' + 'TO ' + '"' + '<Place full path name here>' +  substring(@physicalname,3,len(@physicalname)) + + '"' + '''' + Char(13)  + ','
 
  END

 ELSE
 BEGIN
 if @loop =0  
 PRINT 'with move ' + '''' + @logicalname + '''' + ' TO ' + '''' + '<Place full path name here>' +  substring(@physicalname,3,len(@physicalname)) + '''' + ',' + Char(13)
 
else
PRINT 'move ' + '''' + @logicalname + '''' + ' TO ' + '''' + '<Place full path name here>' +  substring(@physicalname,3,len(@physicalname)) + '''' + ',' + Char(13)

END      
 
set @loop = @loop +1
FETCH NEXT FROM Script_Cursor INTO @logicalname, @physicalname
END

if @error=1
BEGIN
PRINT '@with= ' + '''' + 'replace' + '''' + Char(13)
END

ELSE

BEGIN
PRINT 'replace,' + Char(13) + 'stats=10'
END


CLOSE Script_Cursor
DEALLOCATE Script_Cursor

drop table #backup_records
drop table #backup_records_header_native_70
drop table #backup_records_header_native_2000

Print replicate('*',30)
Print 'Script Ends here'
Print replicate('*',30)

GO


----------------------------------------------
The procedure will differentiate between SQL litespeed backups and native backups, the output should provide you exaclty the script you are looking for. I just hope that you use this with reference to myself and not claim that it is anybody else's :)

Hope that helped,

Bryce
0
 
LVL 6

Expert Comment

by:btutt
ID: 12372491
Mind you this does not require access to the server in question, you will however need to be in an environement that understands these commands. It could be your local server or any other tool that understands TSQL.
0
 

Author Comment

by:lucasdeskywalker
ID: 12499822
Well I don't have a TSQL environment, I have a MySQL environment.  I am not sure if the soft phone PBX compiling the data (creating the .dmp, one per month about 45MB per file) has anything more than a runtime version of MS-SQL?  Can I run this script in MySQL?  Could you tell me what kind of stuffs I would need to create a TSQL environment?  I don't want to pay for MS-SQL and if I did, we would just use it as the SQL server we read/write to.

Another question I had, I did try to run your script against our development database server, using MySQL.  It stopped because of my path?  You wrote

-- Restore_Script_from_backup_file_new '<Full qualified path to backupfile>'   <Is this where we put the path and file name and then uncomment this?  Will this thing run in MySQL?

Create Procedure Restore_Script_from_backup_file @backupfile varchar(100)  <does the path and file name go here?
as

Can you give me back the syntax for exactly what the line would read if my file path and name were c:\test\MS.dmp

Thanks for helpin' us with this.  This is BIG if it works cause I won't have to suffer extra cost to get the data into something we can work with.

Dave
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 6

Expert Comment

by:btutt
ID: 12506299
I tell you what, there is some clarification I need from you. Call me at (***) ***-****.

Bryce

{phone number removed - ee_ai_construct, cs admin}
0
 
LVL 6

Expert Comment

by:btutt
ID: 12508950
We have clarified some issues over the phone. The issue was that the .dmp file really doenst do us any good since it is of a SQL Server proprietary format which MySQL cannot understand. I have suggested that Dave use a command line tool called OSQL.exe to fire a TSQL command script that will then BCP (bulk copy) each user data table to distinct text files. Those files can then be used to import to MYSQL.
0
 
LVL 6

Expert Comment

by:btutt
ID: 12737330
One moment, I had spoken to the individual on the phone and he informed me that he would update the case granting me the points. He has unfortunately not done so. I cannot tell you as to why this is.

Apologies,

Bryce
0
 

Author Comment

by:lucasdeskywalker
ID: 12753362
Sorry, I have been traveling and unable to be here.  Bryce's solution did work, but you will need a copy of SQL and not just the run time modules that come with this kind of installation, our soft PBX.  We ran the .dmp against Bryce's code and it worked, but again, it takes MS-SQL.  I ended up buy a copy of MySQLtoMSSQL from Intelligent Converters and that is what we ended up using for a long term solution.

Thanks for your patience and Bryce did a bang up job, including solve the problem.

Thanks again Bryce.

Dave
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
In this article, we’ll look at how to deploy ProxySQL.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question