SQL Server 数据库备份迁移相关

    数据库的迁移

启用数据库shell 窗口

sp_configure 'show advanced options',1

reconfigure

go

sp_configure 'xp_cmdshell',1

reconfigure

go

执行结果：

　　配置选项 'show advanced options' 已从 0 更改为 1。

　　配置选项 'xp_cmdshell' 已从 0 更改为 1。

    如需关闭只需将“sp_configure 'xp_cmdshell',1”改为“sp_configure 'xp_cmdshell',0”即可。

1. 备份 （选择数据库备份的位置-本地或者网络共享）

NAS共享盘 

--建立映射

EXEC master..xp_cmdshell 'net use X: \\10.164.24.136\temp ******** /USER:CNSZIDCFS002\mlcndcone'

--check映射

EXEC master..xp_cmdshell 'dir X:\'

--删除映射

EXEC master..xp_cmdshell 'net use X: /y /del'

1. 备份语句（old）

（db name and 磁盘看实际情况）

Backup database db_name to disk = N'D:\db_name_full_20200101.bak'with stats = 1

同时备份多个数据库

select 'backup database '+name + ' to disk = N''Y:\tempbackup\'+name+'_full_20200103.bak'' with stats = 1'

from sys.sysdatabases where dbid > 4

and name not in (

'distribution',

'ReportServer',

'ReportServerTempDB')

 

切割大数据库进行备份

backup database DBname to

disk =N'X:\tempbackup\Database_full_20191224_00.bak',

disk =N'X:\tempbackup\Database_full_20191224_01.bak',

disk =N'X:\tempbackup\Database_full_20191224_02.bak',

disk =N'X:\tempbackup\Database_full_20191224_03.bak',

disk =N'X:\tempbackup\Database_full_20191224_04.bak',

disk =N'X:\tempbackup\Database_full_20191224_05.bak',

disk =N'X:\tempbackup\Database_full_20191224_06.bak',

disk =N'X:\tempbackup\Database_full_20191224_07.bak',

disk =N'X:\tempbackup\Database_full_20191224_08.bak',

disk =N'X:\tempbackup\Database_full_20191224_09.bak' with stats=1

1. 还原（右击datebase选择还原数据库）（new·）

USE [master]

RESTORE DATABASE [v3x] FROM  DISK = N'Y:\tempbackup\v3x_full_20191203.bak' WITH  FILE = 1,  MOVE N'v51' TO N'E:\SQLData\v3x.mdf',  MOVE N'v51_log' TO N'E:\SQLData\v3x_log.ldf',  NOUNLOAD,  STATS = 5

GO

4  迁移数据库账号，原服务器执行操作--sp_hexadecimal

USE [tempdb]

GO

/****** Object:  StoredProcedure [dbo].[sp_hexadecimal]    Script Date: 05/21/2015 15:38:28 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*

-------------------------------------------------------------------------------------

用于取得用户密码，被spm_login_details的sql2005版本（或更高版本）调用

-------------------------------------------------------------------------------------

*/

create PROCEDURE [dbo].[sp_hexadecimal]

    @binvalue varbinary(256),

    @hexvalue varchar (514) OUTPUT

AS

DECLARE @charvalue varchar (514)

DECLARE @i int

DECLARE @length int

DECLARE @hexstring char(16)

SELECT @charvalue = '0x'

SELECT @i = 1

SELECT @length = DATALENGTH (@binvalue)

SELECT @hexstring = '0123456789ABCDEF'

WHILE (@i <= @length)

BEGIN

  DECLARE @tempint int

  DECLARE @firstint int

  DECLARE @secondint int

  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

  SELECT @firstint = FLOOR(@tempint/16)

  SELECT @secondint = @tempint - (@firstint*16)

  SELECT @charvalue = @charvalue +

    SUBSTRING(@hexstring, @firstint+1, 1) +

    SUBSTRING(@hexstring, @secondint+1, 1)

  SELECT @i = @i + 1

END

SELECT @hexvalue = @charvalue

 

5 取得加密账号密码--spm_login_details

USE [tempdb]

GO

/****** Object:  StoredProcedure [dbo].[spm_login_details]    Script Date: 2014/2/26 20:32:22 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*

---------------------------------------------------------------------------------

spm_login_details

功能：生成所有login或指定login的create 脚本(含密码)

参数：@login_name --登陆名

             --默认值：null 表示取所有login

调用示例：

     1.取us_chliu的create脚本

        spm_login_details 'us_chliu'

     2.取所有login的create脚本

        spm_login_details

---------------------------------------------------------------------------------

*/

CREATE PROCEDURE [dbo].[spm_login_details]

    @login_name [sysname] = NULL

WITH EXECUTE AS CALLER

AS

DECLARE @name sysname 

DECLARE @type varchar (1) 

DECLARE @hasaccess int 

DECLARE @denylogin int 

DECLARE @is_disabled int 

DECLARE @PWD_varbinary  varbinary (256) 

DECLARE @PWD_string  varchar (514) 

DECLARE @SID_varbinary varbinary (85) 

DECLARE @SID_string varchar (514) 

DECLARE @tmpstr  varchar (1024) 

DECLARE @is_policy_checked varchar (3) 

DECLARE @is_expiration_checked varchar (3) 

DECLARE @tb_login_details  table (id int identity(1,1),login_details varchar(max))

 

DECLARE @defaultdb sysname 

  

IF (@login_name IS NULL) 

  DECLARE login_curs CURSOR FOR   

    SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin

    FROM sys.server_principals p (nolock)

    LEFT JOIN master.sys.syslogins l (nolock) ON ( l.name = p.name )

    WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa' 

ELSE 

  DECLARE login_curs CURSOR FOR

   SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin

   FROM sys.server_principals p (nolock)

   LEFT JOIN master.sys.syslogins l (nolock)  ON ( l.name = p.name )

   WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name 

 

OPEN login_curs 

 

FETCH NEXT FROM login_curs

INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin 

 

IF (@@fetch_status = -1) 

BEGIN 

  PRINT 'No login(s) found.' 

  CLOSE login_curs 

  DEALLOCATE login_curs 

  RETURN -1 

END 

SET @tmpstr = '/* sp_help_revlogin script ' 

PRINT @tmpstr 

SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */' 

PRINT @tmpstr 

PRINT '' 

WHILE (@@fetch_status <> -1) 

BEGIN 

  IF (@@fetch_status <> -2) 

  BEGIN 

--    PRINT ''

    SET @tmpstr = '-- Login: ' + @name 

--    PRINT @tmpstr 

    insert into @tb_login_details

    select ''

    union

    select @tmpstr

 

    IF (@type IN ( 'G', 'U')) 

    BEGIN -- NT authenticated account/group 

 

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']' 

    END 

    ELSE BEGIN -- SQL Server authentication 

        -- obtain password and sid 

        SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) ) 

        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT 

        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT 

  

        -- obtain password policy state 

        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END

        FROM sys.sql_logins (nolock) WHERE name = @name

 

        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END

        FROM sys.sql_logins (nolock) WHERE name = @name 

  

        SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']' 

 

        IF ( @is_policy_checked IS NOT NULL ) 

        BEGIN 

          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked 

        END 

        IF ( @is_expiration_checked IS NOT NULL ) 

        BEGIN 

          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked 

        END 

    END 

    IF (@denylogin = 1) 

    BEGIN -- login is denied access 

      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name ) 

    END 

    ELSE IF (@hasaccess = 0) 

    BEGIN -- login exists but does not have access 

      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name ) 

    END 

    IF (@is_disabled = 1) 

    BEGIN -- login is disabled 

      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE' 

    END 

--    PRINT @tmpstr 

    insert into @tb_login_details  select @tmpstr

  END  

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin 

END 

CLOSE login_curs 

DEALLOCATE login_curs 

select login_details from @tb_login_details order by id asc

RETURN 0 

 

6.迁移账号

spm_login_details 'user'  取得某账号的密码密文 至 新数据库执行同步此账号，检查

 

----------完整备份与还原----------               

--完整备份数据库--
backup database studb to disk='e:\stu.bak'
backup log studb to disk='e:\stu_log.bak'

use master
go
--还原数库库--

restore database studb from disk='e:\stu.bak' with replace,norecovery

restore log studb from disk='e:\stu_log.bak' with replace

 

---------差异备份----------

概念：以前数据库进行过完整备份，现在为保证数据完整性再进行差异备份
backup database studb to disk='e:\stu.bak' --完整备份--
backup database studb to disk='e:\stu_diff.bak' with differential  --差异备份--

use master
go
restore database studb from disk='e:\stu.bak' with replace,norecovery  --先还原完整备份--
restore database studb from disk='e:\stu_diff.bak'  with replace --再还原差异备份--

注：
--replace 覆盖原有数据库
--recovery 还原数据库后，数据库处于正常状态
--norecovery 还原数据库后，数据库处于非正常状态，等待下一步还原