创建一个存储过程,在C#程序中调用这个存储过程,SQL SERVER 的存储过程好像可以复制数据库,我不太清楚
试试Select * into TableA from TableB
declare @name char(50)DECLARE cur CURSOR FOR select [name] from sysobjects where xtype='u' OPEN curFETCH NEXT FROM cur into @name WHILE @@FETCH_STATUS = 0 BEGIN exec ('insert into 数据库2.dbo.' + @name + ' select * from ' + @name) FETCH NEXT FROM cur into @name ENDCLOSE cur DEALLOCATE cur
谢谢antshome(忧郁的苦瓜),请问你用的是存储过程吗?我看不太懂,能否给些注释?谢谢
可以用backup命令备份数据库A到某文件,再用restore命令恢复该文件至数据B。 备份:(例) USE master EXEC sp_addumpdevice 'disk', 'MyNwind_1', DISK ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_1.dat'-- Back up the full MyNwind database. BACKUP DATABASE MyNwind TO MyNwind_1 恢复: RESTORE DATABASE MyNwind FROM MyNwind_1 WITH NORECOVERY 详细可参看相关帮助。 也可参考采用BCP工具,在Sql Server中的帮助中有某些例子。
这个不是存储过程,是sql语句,用ado.net或ado执行就可以了 declare @name char(50) --定义变量DECLARE cur CURSOR FOR select [name] from sysobjects where xtype='u' --定义游标,并从系统表里读出当前数据库所有表名OPEN cur--以下遍历全部表名 FETCH NEXT FROM cur into @name WHILE @@FETCH_STATUS = 0 BEGIN exec ('insert into 数据库2.dbo.' + @name + ' select * from ' + @name) --将指定表的数据插入到数据库2同名的表里 FETCH NEXT FROM cur into @name ENDCLOSE cur DEALLOCATE cur
WHILE @@FETCH_STATUS = 0
BEGIN
exec ('insert into 数据库2.dbo.' + @name + ' select * from ' + @name)
FETCH NEXT FROM cur into @name
ENDCLOSE cur
DEALLOCATE cur
备份:(例)
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_1',
DISK ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_1.dat'-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1
恢复:
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH NORECOVERY
详细可参看相关帮助。
也可参考采用BCP工具,在Sql Server中的帮助中有某些例子。
declare @name char(50) --定义变量DECLARE cur CURSOR FOR select [name] from sysobjects where xtype='u' --定义游标,并从系统表里读出当前数据库所有表名OPEN cur--以下遍历全部表名
FETCH NEXT FROM cur into @name
WHILE @@FETCH_STATUS = 0
BEGIN
exec ('insert into 数据库2.dbo.' + @name + ' select * from ' + @name) --将指定表的数据插入到数据库2同名的表里
FETCH NEXT FROM cur into @name
ENDCLOSE cur
DEALLOCATE cur