Exporting/Importing (Migrating) All Non System MSSQL Databases

Posted by Rose Bush on May 4th, 2015

I want all DB’s moved, from SQL Server/Instance A to SQL Server/Instance B. The easiest way to do this of course, is scripted :D. Oh how I love the idea of automation.

Generate export script:

use master
GO
select '
BACKUP DATABASE [' + [name] + ']
TO  DISK = N''C:\RESTORE\PATH\' + [name] + '.bak'' 
WITH NOFORMAT, NOINIT,  NAME = N''' + [name] + '-Full Database Backup''
, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO' from master..sysdatabases where dbid > 4

1

2

3

4

5

6

7

8

use master

GO

select '

BACKUP DATABASE [' + [name] + ']

TO DISK = N''C:\RESTORE\PATH\' + [name] + '.bak''

WITH NOFORMAT, NOINIT, NAME = N''' + [name] + '-Full Database Backup''

, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO' from master..sysdatabases where dbid > 4

Use the output to backup the databases.

Then run the following command to get the import commands you would run on the destination instance:

declare @cmd1 varchar(500)
declare @cmd2 varchar(500)
declare @cmd3 varchar(500)
declare @pre varchar(500)

set @cmd1 = 'insert into #filesinfo (db, dn, dfn )
	select ''?'', [name], [filename] from [?]..sysfiles where groupid=1'

set @cmd2 = 'update #filesinfo set ln = [name], lfn =[filename]
	from [?]..sysfiles sf
	where sf.groupid=0 and #filesinfo.db=''?'''

set @cmd3 = 'update #filesinfo set nf = (select count(*) from [?]..sysfiles)
	where #filesinfo.db=''?'''

CREATE TABLE #filesinfo (db sysname, dn sysname, ln sysname null, dfn nvarchar(500), lfn nvarchar(500) null, nf int, sa sysname null)

EXEC sp_MSforeachdb @command1=@cmd1,@command2=@cmd2, @command3=@cmd3

set @cmd1 = 'update #filesinfo set sa = (select sl.[name] from master..syslogins sl inner join [?]..sysusers su on sl.sid=su.sid where su.name=''dbo'')
	where #filesinfo.db=''?'''

EXEC sp_MSforeachdb @command1=@cmd1

--EXEC sp_MSforeachdb 'insert into #filesinfo (db) select ''?''' 

DELETE FROM #filesinfo 
WHERE db in ('model','master','tempdb','pubs','Northwind','msdb') 

--SELECT 'backup database ' + db + ' to disk=''' + db + '.bak''' FROM #filesinfo

declare @restorepath nvarchar(1024)
declare @datapath nvarchar(1024)
declare @logpath nvarchar(1024)
set @datapath  = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'
set @logpath  = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'
set @restorepath  = N'C:\EdgeWeb\migrate\'

select '
RESTORE DATABASE [' + db + '] 
FROM  DISK = ''' + @restorepath + db + '.bak''
WITH  FILE = 1
,  MOVE N''' + dn + ''' TO N''' + @datapath + db + '.mdf''
,  MOVE N''' + ln + ''' TO N''' + @logpath + db + '_log.ldf''
,  NOUNLOAD,  REPLACE,  STATS = 10
GO

'
from #filesinfo

drop table #filesinfo

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

declare @cmd1 varchar(500)

declare @cmd2 varchar(500)

declare @cmd3 varchar(500)

declare @pre varchar(500)

set @cmd1 = 'insert into #filesinfo (db, dn, dfn )

select ''?'', [name], [filename] from [?]..sysfiles where groupid=1'

set @cmd2 = 'update #filesinfo set ln = [name], lfn =[filename]

from [?]..sysfiles sf

where sf.groupid=0 and #filesinfo.db=''?'''

set @cmd3 = 'update #filesinfo set nf = (select count(*) from [?]..sysfiles)

where #filesinfo.db=''?'''

CREATE TABLE #filesinfo (db sysname, dn sysname, ln sysname null, dfn nvarchar(500), lfn nvarchar(500) null, nf int, sa sysname null)

EXEC sp_MSforeachdb @command1=@cmd1,@command2=@cmd2, @command3=@cmd3

set @cmd1 = 'update #filesinfo set sa = (select sl.[name] from master..syslogins sl inner join [?]..sysusers su on sl.sid=su.sid where su.name=''dbo'')

where #filesinfo.db=''?'''

EXEC sp_MSforeachdb @command1=@cmd1

--EXEC sp_MSforeachdb 'insert into #filesinfo (db) select ''?'''

DELETE FROM #filesinfo

WHERE db in ('model','master','tempdb','pubs','Northwind','msdb')

--SELECT 'backup database ' + db + ' to disk=''' + db + '.bak''' FROM #filesinfo

declare @restorepath nvarchar(1024)

declare @datapath nvarchar(1024)

declare @logpath nvarchar(1024)

set @datapath = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'

set @logpath = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'

set @restorepath = N'C:\EdgeWeb\migrate\'

select '

RESTORE DATABASE [' + db + ']

FROM DISK = ''' + @restorepath + db + '.bak''

WITH FILE = 1

, MOVE N''' + dn + ''' TO N''' + @datapath + db + '.mdf''

, MOVE N''' + ln + ''' TO N''' + @logpath + db + '_log.ldf''

, NOUNLOAD, REPLACE, STATS = 10

GO

'

from #filesinfo

drop table #filesinfo

Filed under: Code or Hosting | Comment (0)

Migrating MySQL Users Between Servers

Posted by Rose Bush on November 13th, 2012

I want a ONE liner to migrating MySQL users between servers. We know that migrating databases is easy enough using mysqldump, but migrating users from one MySQL instance or server to separate one can be annoying. Based on well known export commands, here is a neat little piped set of commands that you can run on the source DB server. The command will generate all the GRANT statements that you can simply run on the destination DB server.

mysql -u <ROOTUSERNAME> -B -N -p<ROOTPASSWORD> -e "SELECT user, host FROM user" mysql | awk '{print "mysql -u <ROOTUSERNAME> -p<ROOTPASSWORD> -B -N -e\"SHOW GRANTS FOR " $1 "@" $2 "\""}'

1	mysql -u <ROOTUSERNAME> -B -N -p<ROOTPASSWORD> -e "SELECT user, host FROM user" mysql \| awk '{print "mysql -u <ROOTUSERNAME> -p<ROOTPASSWORD> -B -N -e\"SHOW GRANTS FOR " $1 "@" $2 "\""}'

If you are logged in as root user and do NOT need to supply user and password:

mysql -B -N -e "SELECT user, host FROM user" mysql | awk '{print "mysql -B -N -e\"SHOW GRANTS FOR " $1 "@" $2 "\""}'

1	mysql -B -N -e "SELECT user, host FROM user" mysql \| awk '{print "mysql -B -N -e\"SHOW GRANTS FOR " $1 "@" $2 "\""}'

Hey, lets go two better, lets run the output, and add semicolons at the end of the lines to make the output appropriate to run on the destination server.

mysql -B -N -e "SELECT user, host FROM user" mysql | awk '{system( "mysql -B -N -e\"SHOW GRANTS FOR " $1 "@" $2 "\"")}' | sed 's/$/;/'

1	mysql -B -N -e "SELECT user, host FROM user" mysql \| awk '{system( "mysql -B -N -e\"SHOW GRANTS FOR " $1 "@" $2 "\"")}' \| sed 's/$/;/'

The beauty of this is that you need not know the passwords for any of the mysql user accounts and are then given one block of output, instead of the commands to run to get the grants.

*Migrating all databases except mysql and information_schema:

mysql -u <MYSQLROOTTUSERNAME> -p<MYSQLROOTPWD> -B -N -e"SHOW DATABASES;" | grep -v mysql | grep -v information_schema | xargs mysqldump -u <MYSQLROOTTUSERNAME> -p<MYSQLROOTPWD> --databases > mysqldbdump.sql

1	mysql -u <MYSQLROOTTUSERNAME> -p<MYSQLROOTPWD> -B -N -e"SHOW DATABASES;" \| grep -v mysql \| grep -v information_schema \| xargs mysqldump -u <MYSQLROOTTUSERNAME> -p<MYSQLROOTPWD> --databases > mysqldbdump.sql

Originaly found on http://jasonbos.co/migrating-mysql-users-from-one-server-to-anot

Filed under: Code or Hosting | Comment (0)

MySQL to MySQL move of data!

Posted by Rose Bush on November 12th, 2012

Sometimes you want to move a database between hosts, this is an easy way to cut down on the clutter created in the process, is to output the backup directly to the new host.

mysqldump -uExapmleUser -pExapmlePass -hExapmleHostNameOrIp ExapmleDatabaseName | mysql -uExapmleUser -pExapmlePass -hExapmleHostNameOrIp ExapmleDatabaseName

1	mysqldump -uExapmleUser -pExapmlePass -hExapmleHostNameOrIp ExapmleDatabaseName \| mysql -uExapmleUser -pExapmlePass -hExapmleHostNameOrIp ExapmleDatabaseName

Filed under: Code or Hosting | Comment (0)

Edventures in Normalcy

Exporting/Importing (Migrating) All Non System MSSQL Databases

Migrating MySQL Users Between Servers

MySQL to MySQL move of data!

Archives

Edventures in Normalcy

Exporting/Importing (Migrating) All Non System MSSQL Databases

Migrating MySQL Users Between Servers

MySQL to MySQL move of data!

Search:

Archives