如何将所有数据库（快速）自sql server 2008快递到sql server 2008 R2（无快递）

well i had sql 2008 express, and now i have installed, sql server, now i want to delete sql express, but in that instance i have all my database i have worked (plus of 20), so they are very important for me, how can i pass it to sql server 2008 r2, i know i can do database of all but i dont want to work a lot of, there is a way short, for to pass al database? coping a folder? something? thanks!

好吧,我有sql 2008快递,现在我已经安装,sql server,现在我想删除sql express,但在那个实例中,我有我所有的数据库工作(加20),所以它们对我来说非常重要,我怎么能把它传递到sql server 2008 r2,我知道我可以做所有的数据库,但我不想工作很多,有一种方法简短,为了传递al数据库?应对文件夹?什么?谢谢!

2 个解决方案

#1

I Attached all database, and i added, all (but one for one) i added all in the same windows and in 5 minutos i had all database in sql (no express)

我附加了所有数据库,我添加,所有(但一对一)我添加所有在相同的窗口和5 minutos我有所有数据库在SQL(没有快递)

#2

Create an SSIS package with 4 steps.

创建一个包含4个步骤的SSIS包。

First, a Execute SQL task that backups all DBs to a specific location:

首先,执行SQL任务,将所有数据库备份到特定位置:

exec sp_msforeachdb '
IF DB_ID(''?'') > 4
Begin
BACKUP DATABASE [?] TO  DISK = N''\\Backups\?.BAK'' WITH NOFORMAT, INIT,
NAME = N''?-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N''?''
    and backup_set_id=(select max(backup_set_id) from msdb..backupset 
where database_name=N''?'' )
if @backupSetId is null begin
    raiserror(N''Verify failed. Backup information for database ''''?'''' not found.'', 16, 1)
end
RESTORE VERIFYONLY FROM  DISK = N''\\Backups\?.BAK'' WITH
   FILE = @backupSetId,  NOUNLOAD,  NOREWIND
End
'

Second, Create a SP to restore DBs using a variable for location and DB name:

其次,使用位置和数据库名称的变量创建SP以恢复数据库:

CreatePROCEDURE [dbo].[uSPMas_RestoreDB] @DBname         NVARCHAR(200), 
                                         @BackupLocation NVARCHAR(2000) 
AS 
  BEGIN 
      SET nocount ON; 

    create table #fileListTable 
(
    LogicalName          nvarchar(128),
    PhysicalName         nvarchar(260),
    [Type]               char(1),
    FileGroupName        nvarchar(128),
    Size                 numeric(20,0),
    MaxSize              numeric(20,0),
   )
insert into #fileListTable EXEC('RESTORE FILELISTONLY FROM DISK = '''+@BackupLocation+@dbname+'.bak''')

Declare @Dname varchar(500) 
Set @Dname = (select logicalname from #fileListTable where type = 'D')
Declare @LName varchar(500)
Set @LName = (select logicalname from #fileListTable where type = 'L')

declare @sql nvarchar(4000)
SET @SQL = 'RESTORE DATABASE  ['+ @dbname +'] FROM  
DISK = N'''+@BackupLocation + @dbname +'.BAK'' WITH  FILE = 1, 
 MOVE N'''+ @Dname +''' TO N''E:\YourLocation\'+ @dbname +'.mdf'', 
 MOVE N'''+ @LName +''' TO N''D:\Your2ndLocation\'+ @dbname +'_log.ldf'',
  NOUNLOAD,  REPLACE,  STATS = 10'

exec sp_executesql @SQL 

drop table #fileListTable
  END

Third, Create a ForEach Loop Container and put a Execute Sql Statement with variable:

第三,创建一个ForEach循环容器,并使用变量放置一个Execute Sql语句:

EXEC uSPMas_RestoreDB ? , ?

Use the for ForEach Loop Container to pass the variables

使用for ForEach循环容器传递变量

Fourth, Create a 'Transfer Logins' task to move over all DB logins

第四,创建“传输登录”任务以移动所有数据库登录

#1