怎么用SQL创建远程服务器上的数据库

参考SELECT *INTO 表名FROM OPENDATASOURCE('SQLOLEDB','Data Source=远程服务器B的IpIntegrated Security=SSPI').[远程数据库名称].[架构].[表名]

一、创建新库

/*

测试 在sql2005中通过查询分析器，连接到sql2000，并创建了 tmpdb 库：成功

p_CreateDB 'tmpdb1','data','sa','sa'

*/

-----------------------创建存储过程开始--------------------

if object_id('p_CreateDB') is not null

drop procedure p_CreateDB

go

CREATE PROCEDURE p_CreateDB

@Des_DB sysname, --目标数据库

@ServerName sysname=N'', --服务器名

@UserName sysname=N'', --用户名,不指定则表示使用 Windows 身份登录

@pwd sysname=N'' --密码

AS

SET NOCOUNT ON

DECLARE @srvid int,--服务器的对象

@dbsid int,

@Dbid int,--新建数据库对象

@DBFile int,--新建数据库DB文件

@LogFile int,--新建数据库Log文件

@CmdStr nvarchar(4000)

declare @err int,@src varchar(255), @desc varchar(255) --错误处理变量

IF ISNULL(@ServerName,N'')=N'' SET @ServerName=@@ServerName --默认为本地数据库

--创建sqldmo对象·

EXEC @err=sp_oacreate 'sqldmo.sqlserver',@srvid OUT

IF @err<>0 GOTO lb_Err

--连接服务器

IF ISNULL(@UserName,N'')=N'' --使用 Windows 身份登录

BEGIN

EXEC @err=sp_oasetproperty @srvid,'loginsecure',-1

IF @err<>0 GOTO lb_Err

EXEC @err=sp_oamethod @srvid,'connect',NULL,@ServerName

END

ELSE

EXEC @err=sp_oamethod @srvid,'connect',NULL,@ServerName,@UserName,@pwd

IF @err<>0 GOTO lb_Err

--新数据库对象创建

EXEC @err=sp_oacreate 'SQLDMO.Database',@Dbid OUT

IF @err<>0 GOTO lb_Err

EXEC @err=sp_oasetproperty @Dbid, 'Name',@Des_DB

IF @err<>0 GOTO lb_Err

/*

---这里可以设置数据文件与日志文件的属性，不写就由sql server默认

--新数据库DB文件对象创建,并设置属性

EXEC @err=sp_oacreate 'SQLDMO.DBFile',@DBFile OUT

IF @err<>0 GOTO lb_Err

EXEC @err=sp_oasetproperty @DBFile, 'Name','tmpfile'

IF @err<>0 GOTO lb_Err

EXEC @err=sp_oasetproperty @DBFile, 'PhysicalName','c:/tmp.mdf'

IF @err<>0 GOTO lb_Err

EXEC @err=sp_oasetproperty @DBFile, 'PrimaryFile','true'

IF @err<>0 GOTO lb_Err

EXEC @err=sp_oasetproperty @DBFile, 'FileGrowthType',0

IF @err<>0 GOTO lb_Err

EXEC @err=sp_oasetproperty @DBFile, 'FileGrowth',1

IF @err<>0 GOTO lb_Err

--新数据库对象加入DB文件

EXEC @err = sp_OAMethod @Dbid,'FileGroups.Item("primary").DBFiles.Add',null,@DBFile

IF @err<>0 GOTO lb_Err

--新数据库LOG文件对象创建,并设置属性

EXEC @err=sp_oacreate 'SQLDMO.LogFile',@LogFile OUT

IF @err<>0 GOTO lb_Err

EXEC @err=sp_oasetproperty @LogFile, 'Name','tmplg'

IF @err<>0 GOTO lb_Err

EXEC @err=sp_oasetproperty @LogFile, 'PhysicalName','c:/tmp.ldf'

--新数据库对象加入DB文件

EXEC @err = sp_OAMethod @Dbid,'TransactionLog.LogFiles.Add',null,@LogFile

IF @err<>0 GOTO lb_Err

*/

--在服务器上创建 新数据库

EXEC @err = sp_OAMethod @srvid,'Databases.Add',null,@dbid

IF @err<>0 GOTO lb_Err

/*

EXEC @err= sp_OAGetProperty @srvid, 'Databases', @dbsid OUT

IF @err<>0 GOTO lb_Err

SET @CmdStr = 'Add'

EXEC @err = sp_OAMethod @dbsid,@CmdStr,null,@dbid

*/

--结束

SET @err=0

GOTO lb_Exit

--错误处理

lb_Err:

EXEC sp_oageterrorinfo NULL, @src OUT, @desc OUT

EXEC sp_OADestroy @LogFile

EXEC sp_OADestroy @DBFile

EXEC sp_OADestroy @Dbsid

EXEC sp_OADestroy @Dbid

EXEC sp_OADestroy @srvid

EXEC @err=sp_oamethod @srvid,'DisConnect'

RAISERROR(N'错误编号 %#x, 错误源 "%s", 错误描述 "%s"',16,1,@err,@src,@desc)

RETURN -1

lb_Exit:

EXEC sp_OADestroy @LogFile

EXEC sp_OADestroy @DBFile

EXEC sp_OADestroy @Dbsid

EXEC sp_OADestroy @Dbid

EXEC sp_OADestroy @srvid

EXEC @err=sp_oamethod @srvid,'DisConnect'

RETURN @err

GO

Windows Vista 和 Windows Server 2008 以及更高版本

以下过程通过使用具有高级安全 Microsoft 管理控制台 (MMC) 管理单元的 Windows 防火墙来配置该 Windows 防火墙。从 Windows Vista 和 Windows Server 2003 开始提供此管理单元。高级安全 Windows 防火墙仅配置当前配置文件。有关高级安全 Windows 防火墙的详细信息，请参阅配置 Windows 防火墙以允许 SQL Server 访问。

打开 Windows 防火墙的端口以进行 TCP 访问

在“开始”菜单上，单击“运行”，键入 WF.msc，然后单击“确定”。

在“高级安全 Windows 防火墙”的左窗格中，右键单击“入站规则”，然后在 *** 作窗格中单击“新建规则”。

在“规则类型”对话框中，选择“端口”，然后单击“下一步”。

在“协议和端口”对话框中，选择 TCP。选择“特定本地端口”，然后键入数据库引擎实例的端口号，例如默认实例的端口号 1433。单击“下一步”。

在“ *** 作”对话框中，选择“允许连接”，然后单击“下一步”。

在“配置文件”对话框中，选择在您想要连接到数据库引擎时描述计算机连接环境的任何配置文件，然后单击“下一步”。

在“名称”对话框中，输入此规则的名称和说明，再单击“完成”。

在使用动态端口时打开对 SQL Server 的访问

在“开始”菜单上，单击“运行”，键入 WF.msc，然后单击“确定”。

在“高级安全 Windows 防火墙”的左窗格中，右键单击“入站规则”，然后在 *** 作窗格中单击“新建规则”。

在“规则类型”对话框中，选择“程序”，然后单击“下一步”。

在“程序”对话框中，选择“此程序路径”。单击“浏览”，导航到要通过防火墙访问的 SQL Server 实例，再单击“打开”。默认情况下，SQL Server 位于 C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\Sqlservr.exe。单击“下一步”。

在“ *** 作”对话框中，选择“允许连接”，然后单击“下一步”。

在“配置文件”对话框中，选择在您想要连接到数据库引擎时描述计算机连接环境的任何配置文件，然后单击“下一步”。

在“名称”对话框中，输入此规则的名称和说明，再单击“完成”。

---