Solved

MSSQL .dmp to MySQL command line???

Posted on 2004-10-19
9
1,050 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
  • 5
  • 2
9 Comments
 
LVL 6

Accepted Solution

by:
btutt earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 6

Expert Comment

by:btutt
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now