MSSQL和MYSQL数据库的备份转移教程(kangle服务器+easypanel环境下测试,MSSQL数据库备份是重点)

怎么备份数据库?

MYSQL数据库备份:a.可以通过easypanel后台备份数据库,适合小数据、中等数据规模。b.可通过phpmyadmin备份,适合小规模数据(8M了不起了)。c.可以直接再通windows的远程桌面直接转移货复制MYSQL数据文件,非常适合大数据、超大数据、比如d:\mysql里的.frm、.MYD、.MYI等文件。

MSSQL数据库备份:

第一类方法(kangle环境特有):
kangle特有的办法,kangle会在每个网站用户的根目录下生成一个database的文件夹,那里存放着诸如sq_aspnet2.ss和sq_aspnet2_log.LDF的数据库文件和数据库log文件,其中aspnet2是网站用户名字。

1.下面的这个例子,是从同一个服务器或不同服务器里把一个用户的数据库数据迁到另一个用户名下(要求用户名不同的情况)。如果想把用户aspnet用户的数据库和网站搬到aspnet2里,这里显示的是原网站aspnet的数据库文件:
001
搬过来后要注意把sq_aspnet.ss和sq_aspnet_log.LDF改名,这还没完。
要重新申明用户名sq_aspnet2和数据库sq_aspnet2以及构架sq_aspnet2的拥有关系。

2.下面先讲解同一个服务器或不同服务器,且用户名不同情况,比如aspnet是原网站,aspnet2是现网站,而不同服务器同名网站的情况会变得复杂,稍后再讲。
SQL SERVER MANAGEMENT STUDIO ->  安全性 -> 用户,找到用户sq_aspnet2,右击,选择属性,再点击用户映射设置如下图所示。
012

3.还有有很多程序诸如z-blog博客的数据库表都有原来的sq_aspnet前缀也要改过来成为sq_aspnet2(你会发现sq_aspnet2数据库里的表前缀是sq_aspnet),怎么改呢?

011

首先,通过进入SQL server management studio express后,有两种解决方法。

修改表前缀:.

批量修改请用:
先选择数据库,在执行以下代码。如果方法一或第二种方法均可。
013
方法一:使用游标(中文处请修改)
use 数据库名(这里是sq_aspnet2)

go
declare @name sysname
declare csr cursor
for select TABLE_NAME from INFORMATION_schema.TABLES
open csr

FETCH next FROM csr INTO @name

while (@@FETCH_STATUS=0)

BEGIN

SET @name='原构架名.' + @name

print 'alter schema 新构架名 TRANSFER ' + @name

fetch next from csr into @name

END

CLOSE csr

DEALLOCATE csr

或以下代码:

declare @name  sysname
declare c cursor
for select   TABLE_NAME  from INFORMATION_SCHEMA.TABLES
open c
FETCH   c  INTO   @name
while   (@@FETCH_STATUS=0)
BEGIN
SET   @name='原架构名.'+@name
EXEC SP_ChangeObjectOwner @name, '新架构名'
fetch c into @name
END
CLOSE   c
DEALLOCATE  c

007

方法二:
使用系统存储过程sp_MSforeachtable
EXEC sp_MSforeachtable @command1=”EXEC   SP_ChangeObjectOwner   ’?',’新架构名’”
有些程序比如discuz!nt还要求修改存储过程的构架,运行以下代码

SELECT ‘ALTER SCHEMA dbo TRANSFER ‘ + s.Name + ‘.’ + p.Name

FROM sys.Procedures p INNER JOIN sys.Schemas s on p.schema_id = s.schema_id

WHERE s.Name = ‘sq_aspnet’

—————————————————

将这段执行的结果(上述方法一或方法二)全部复制,略加修改成这样的代码,执行一遍就OK:
诸如以下生成的代码。
ALTER SCHEMA sq_aspnet3 TRANSFER sq_aspnet.dnt_getlastposttid
ALTER SCHEMA sq_aspnet3 TRANSFER sq_aspnet.dnt_deletepost1bypid
ALTER SCHEMA sq_aspnet3 TRANSFER sq_aspnet.dnt_deluserallinf
ALTER SCHEMA sq_aspnet3 TRANSFER sq_aspnet.dnt_deletetopictags
ALTER SCHEMA sq_aspnet3 TRANSFER sq_aspnet.dnt_addusergroup
ALTER SCHEMA sq_aspnet3 TRANSFER sq_aspnet.dnt_getalltopiccount
……
ALTER SCHEMA sq_nt3 TRANSFER sq_nt.dnt_split
以上过程看图再熟悉一下。复制,选择数据库,粘贴,执行。
008
最后别忘了刷新表,看最终结果。
009

SQL Server2005还可以使用系统存储过程sp_changeobjectowner更改数据库对象的所有者。sp_changeobjectowner   ‘对象名(包括架构名)’,'新架构名’

4.最后别忘了修改相应域名和域名解析(域名当然可以不修改,根据需要)和程序源码的数据库配置文件,诸如z-blog这款博客程序:010

5.最后把不需要的用户名和架构删除干净:
先删除此用户对应的架构,然后在删除对应的用户
步骤
1。SQL SERVER MANAGEMENT STUDIO -> 数据库sq_aspnet2 -> 安全性 -> 构架,先删除对应的构架sq_aspnet
2。SQL SERVER MANAGEMENT STUDIO -> 数据库sq_aspnet2 -> 安全性 -> 用户,删除对应的用户sq_aspnet
014

6.上面这个例子,是从同一个服务器或不同服务器里把一个用户的数据库数据迁到另一个用户名下(要求用户名不同的情况)。有人会说,我是搬家,为啥要取不同的用户名名字呢?
我就想把原服务器上的aspnet网站和数据库搬到现在服务器上同名的网站名下,按照上面方法不就行了。事实上没那么简单,因为kangle这方面没做好,所以才麻烦,我们继续来看。我们把原数据库的文件直接从打包文件里解压出来(注意早在先前的教程里就讲了要先打包备份,并且把打包文件放到现在目标网站的目录下解压,否则会有权限问题错误。这是针对kangle服务器而言的,不懂的可以看这里第七大点网站搬家第2小点:点击这里)覆盖现在目标服务器上相应默认生成的数据库文件。

首先,覆盖后的数据库用户映射关系已经丢失,见下图:
021

修改为下面正确的用户映射,并允许对sq_aspnet数据库有db_owner和public权限:
005

但确定后发现错误:用户角色已经存在。
022

所以先要删除sq_aspnet用户然后建立映射关系后再重建:SQL SERVER MANAGEMENT STUDIO -> 数据库sq_aspnet -> 安全性 -> 用户,删除对应的用户sq_aspnet。结果又发生错误:数据库主体在该数据库中拥有构架,无法删除。
023

那就去先删除架构,在删除用户名吧,结果又发现如下错误:有数据表在引用该构架。
024

所以又回到第3点更换表前缀的步骤,然后建立好构架后还要再改回表前缀。中间过程还要先新建用户和构架。

027
028
总结就是以下顺序过程:

1。SQL SERVER MANAGEMENT STUDIO -> 数据库sq_aspnet -> 安全性 -> 用户,右击新建用户sq_aspnet3。
2。SQL SERVER MANAGEMENT STUDIO -> 数据库sq_aspnet -> 安全性 -> 构架,右击新建构架sq_aspnet3,并使它属于sq_aspnet3用户
3。修改表前缀为sq_aspnet3
4。SQL SERVER MANAGEMENT STUDIO -> 数据库sq_aspnet -> 安全性 -> 构架,先删除对应的构架sq_aspnet
5。SQL SERVER MANAGEMENT STUDIO -> 数据库sq_aspnet -> 安全性 -> 用户,删除对应的用户sq_aspnet
6。SQL SERVER MANAGEMENT STUDIO ->  安全性 -> 登录名,做用户映射,并允许对sq_aspnet数据库有db_owner和public权限。如图所示:
005
7。修改表前缀为sq_aspnet。
8。SQL SERVER MANAGEMENT STUDIO -> 数据库sq_aspnet -> 安全性 -> 构架,先删除对应的构架sq_aspnet3
9。SQL SERVER MANAGEMENT STUDIO -> 数据库sq_aspnet -> 安全性 -> 用户,删除对应的用户sq_aspnet3(似乎这部已经自动删除了该用户)
10。完成。(如果网站还不能正常访问,可能是重建网站造成的,可以重新设置密码)

第二类方法(生成的bak文件适用于大多数情况备份还原,非kangle特列):

1.这个例子讲的仍是同一个服务器或不同服务器里把一个用户的数据库数据迁到另一个用户名下(要求用户名不同的情况)。从aspnet网站的数据库备份,然后还原覆盖到aspnet2网站的数据库。
通过通常的备份再还原的方法进行:右击数据库sq_aspnet,任务,开始备份生成sq_aspnet.bak。
029

默认备份设置即可,确定:
030

2.点击数据库sq_aspnet2属性,点击任务,还原,数据库:
031
3.“常规”中选择源设备中添加文件sq_aspnet.bak,并勾选这个还原文件,“选项”中勾选覆盖现有数据库:
032
4.“选项”中将还原名和路径修改正确,必须为sq_aspnet2.ss和sq_aspnet2_log.LDF,路径也要正确,勾选覆盖原有数据库。点击确定,提示还原成功。
033
5.但是还原后的数据库和用户关系(用户映射)会变掉,这点比mysql数据库复杂。参见变掉的关系:SQL SERVER MANAGEMENT STUDIO ->  安全性 -> 登录名,用户映射,登录名sq_aspnet2属性,看到他没有关联数据库sq_aspnet2和架构sq_aspnet2,以及没有db_owner和public权限。
034

我们来恢复一下,参见下图:
035

6.修改错误的表前缀,表前缀sq_aspnet应该为sq_aspnet2,参见第一类备份还原方法中的第3小点:
011
7.不同服务器里把一个用户的数据库数据迁到另一个同名用户名下。执行还原操作会发生以下错误:
037

所以可以先关闭数据库连接:点击数据库sq_aspnet属性,点击任务,分离:
038
勾选删除连接,确定:
039
然后点击菜单“数据库”,还原数据库:
040

填好正确的参数,特别是要还原的数据库填正确:
041

选项卡也要填正确:比如换了服务器,硬盘路径也换了。确定,提示还原成功。
042

然后再进行数据库附加操作,点击菜单“数据库”,附加数据库:还原后的数据库已经自动附加。然后回到第一大类方法第6小点教程慢慢操作。

8.最后提醒这两种方法(第一大类和第二大类)如果更改了数据库名称和用户名,还要在你的程序中做出相应的修改。
——————————————

如何快速的建立一个MSSQL数据库和一个关联用户:1.新建一个数据库。2.新建一个用户,设置好密码和默认数据库为刚才新建的数据库。3.给这个用户做用户映射到刚才的数据库,并赋予数据库db_owner和public权限,同时设置好构架。

————————————–

最后奉上更改数据和备份路径,特别是备份路径的方法:
Windows Server 2008 Standard Version 64-bit
SQL Server 2008 Standard Version 64-bit

查询用户库Data文件默认路径:
DECLARE @DefaultData VARCHAR(100)
EXEC master..xp_regread @rootkey=’HKEY_LOCAL_MACHINE’,
@key=’Software\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer’,
@value_name=’DefaultData’,
@DefaultData=@DefaultData OUTPUT
SELECT @DefaultData

查询用户库Log文件默认路径:
DECLARE @DefaultLog VARCHAR(100)
EXEC master..xp_regread @rootkey=’HKEY_LOCAL_MACHINE’,
@key=’Software\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer’,
@value_name=’DefaultLog’,
@DefaultLog=@DefaultLog OUTPUT
SELECT @DefaultLog

查询用户库备份文件默认路径:
DECLARE @BackupDirectory VARCHAR(100)
EXEC master..xp_regread @rootkey=’HKEY_LOCAL_MACHINE’,
@key=’Software\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer’,
@value_name=’BackupDirectory’,
@BackupDirectory=@BackupDirectory OUTPUT
SELECT @BackupDirectory

修改用户库Data文件默认路径:
EXEC master..xp_regwrite
@rootkey=’HKEY_LOCAL_MACHINE’,
@key=’Software\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer’,
@value_name=’DefaultData’,
@type=’REG_SZ’,
@value=’D:\SQL2008\Data’

修改用户库Log文件默认路径:
EXEC master..xp_regwrite
@rootkey=’HKEY_LOCAL_MACHINE’,
@key=’Software\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer’,
@value_name=’DefaultLog’,
@type=’REG_SZ’,
@value=’D:\SQL2008\Log’

修改用户库备份文件默认路径:
EXEC master..xp_regwrite
@rootkey=’HKEY_LOCAL_MACHINE’,
@key=’Software\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer’,
@value_name=’BackupDirectory’,
@type=’REG_SZ’,
@value=’D:\SQL2008\DBBak’

 

发表评论