Solved

MSSQL .dmp to MySQL command line???

Posted on 2004-10-19
9
1,058 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
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Invalid Identifier Error 3 82
Client backup and data retention? 3 54
syadmin MSSQL 2 58
SQL Server memory sizing - reallocation 16 29
Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
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…

910 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

21 Experts available now in Live!

Get 1:1 Help Now