移动Sql Server数据库的脚本代码-mysql教程-PHP中文網

首頁

資料庫

mysql教程

移动Sql Server数据库的脚本代码

Jun 07, 2016 pm 05:49 PM

腳本

我想很多朋友以前做数据库移动或备份时都是直接使用人工来制作吧，下面我来介绍利用Sql Server脚本来实现，有需要学习的朋友可参考。

注意事项：这个脚本只有2个参数需要输入，即：目标名字和目标目录

例1

代码如下

复制代码

USE master
GO

DECLARE
    @DBName sysname,
    @DestPath varchar(256)
DECLARE @DB table(
    name sysname,
    physical_name sysname)

BEGIN TRY

SELECT
@DBName = 'TargetDatabaseName', --input database name
@DestPath = 'D:SqlData' --input destination path

-- kill database processes
DECLARE @SPID varchar(20)
DECLARE curProcess CURSOR FOR

SELECT spid
FROM sys.sysprocesses
WHERE DB_NAME(dbid) = @DBName

OPEN curProcess
    FETCH NEXT FROM curProcess INTO @SPID
    WHILE @@FETCH_STATUS = 0
    BEGIN
            EXEC('KILL ' + @SPID)
            FETCH NEXT FROM curProcess
    END
CLOSE curProcess
DEALLOCATE curProcess

-- query physical name
INSERT @DB(
    name,
    physical_name)
SELECT
    A.name,
    A.physical_name
FROM sys.master_files A
INNER JOIN sys.databases B
    ON A.database_id = B.database_id
        AND B.name = @DBName
WHERE A.type

--set offline
EXEC('ALTER DATABASE ' + @DBName + ' SET OFFLINE')

--move to dest path
DECLARE
    @login_name sysname,
    @physical_name sysname,
    @temp_name varchar(256)
DECLARE curMove CURSOR FOR
SELECT
    name,
    physical_name
FROM @DB
OPEN curMove
    FETCH NEXT FROM curMove INTO @login_name,@physical_name
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @temp_name = RIGHT(@physical_name,CHARINDEX('',REVERSE(@physical_name)) - 1)
            EXEC('exec xp_cmdshell ''move "' + @physical_name + '" "' + @DestPath + '"''')
            EXEC('ALTER DATABASE ' + @DBName + ' MODIFY FILE ( NAME = ' + @login_name
                    + ', FILENAME = ''' + @DestPath + @temp_name + ''')')
            FETCH NEXT FROM curMove INTO @login_name,@physical_name
        END
CLOSE curMove
DEALLOCATE curMove

-- set online
EXEC('ALTER DATABASE ' + @DBName + ' SET ONLINE')

-- show result
SELECT
    A.name,
    A.physical_name
FROM sys.master_files A
INNER JOIN sys.databases B
    ON A.database_id = B.database_id
        AND B.name = @DBName
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage
END CATCH
GO

例2

创建一个sqlserver的代码模板，加上一个创建表的模板。开发的时候可以拷贝过去直接改动一下就可以用了

代码如下

复制代码

USE [master]
GO
IF EXISTS(SELECT 1 FROM sysdatabases WHERE NAME=N'HkTemp')
BEGIN
DROP DATABASE HkTemp --如果数据库存在先删掉数据库
END
GO
CREATE DATABASE HkTemp
ON
PRIMARY --创建主数据库文件
(
NAME='HkTemp',
FILENAME='E:DatabasesHkTemp.dbf',
SIZE=5MB,
MaxSize=20MB,
FileGrowth=1MB
)
LOG ON --创建日志文件
(
NAME='HkTempLog',
FileName='E:DatabasesHkTemp.ldf',
Size=2MB,
MaxSize=20MB,
FileGrowth=1MB
)
GO
--添加表
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Hksj_User') AND type in (N'U'))
BEGIN
CREATE TABLE Hksj_User
(
Id INT IDENTITY(1,1) NOT NULL,
SName NVARCHAR(20) NOT NULL,
SNickName NVARCHAR(20),
SPassWord NVARCHAR(30) NOT NULL,
DCreateDate DATETIME ,
SCreator NVARCHAR(20),
SEmail NVARCHAR(50),
SPhone NVARCHAR(50),
SIdentifyId NVARCHAR(30),
DLastTimeLogOn DATETIME
PRIMARY KEY CLUSTERED
(
Id ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
--添加注释
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'登录名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Hksj_User', @level2type=N'COLUMN',@level2name=N'SName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'邮箱' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Hksj_User', @level2type=N'COLUMN',@level2name=N'SEmail'

本網站聲明

本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn