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

我想很多朋友以前做数据库移动或备份时都是直接使用人工来制作吧，下面我来介绍利用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'