Migrating SQL Server 2000 user databases to SQL server 2012

Migrating SQL Server 2000 user databases to SQL server 2012

Rate This
  • Comments 3

T-SQL scripts to migrate SQL server 2000 user databases to SQL server 2012 

 

1.      Backup SQL 2000 user databases using below scripts: Please customize parts highlighted in yellow

/********************************************************************************

*         Backup SQL server 2000 user databases

********************************************************************************/

 

use master;

go

 

DECLARE UserDatabases_CTE_Cursor Cursor

FOR

 

-- Selecting user database names.

select name as DatabaseName

from sysdatabases

where ([dbid] > 4) and ([name] not like '$')

 

OPEN UserDatabases_CTE_Cursor

DECLARE @dbName varchar(100);

DECLARE @backupPath varchar(100);

DECLARE @backupQuery varchar(500);

 

-- make sure that the below path exists

set @backupPath = 'c:\SQL2000Backup\'

 

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

While (@@FETCH_STATUS <> -1)

 

BEGIN

-- Backup SQL statement

set @backupQuery =  'backup database ' + @dbName + ' to disk = ''' + @backupPath + @dbName + '_[SQL2000].bak'''

 

-- Print SQL statement

print @backupQuery

 

-- Execute backup script

EXEC (@backupQuery)

 

-- Get next database

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

END

 

CLOSE UserDatabases_CTE_Cursor

DEALLOCATE UserDatabases_CTE_Cursor

GO


2.      Restore SQL 2000 backed-up user databases to SQL server 2008 or SQL Server 2008 R2 using below scripts: Please customize parts highlighted in yellow.

/********************************************************************************

*             Restore SQL server 2000 backed databases to

*             SQL server 2008 or SQL Server 2008 R2 instances       

********************************************************************************/

use master;

go

 

 

/*   Please un-comment below part to enable cp_cmdshell procedures

 Don't forget to disable it after migration completed.

 EXEC sp_configure'xp_cmdshell', 1

 RECONFIGURE

 GO

 

 EXEC sp_configure 'show advanced options', 1

 RECONFIGURE

 GO*/

 

 DECLARE @Return int

 IF OBJECT_ID('tempdb..#SQL2000BackedUpdatabases') IS NOT NULL DROP TABLE #SQL2000BackedUpdatabases

 

 CREATE TABLE #SQL2000BackedUpdatabases (BakFileName varchar(400), ID int IDENTITY(1,1))

 INSERT  #SQL2000BackedUpdatabases EXECUTE @Return = master.dbo.xp_cmdshell  'dir /B *.bak c:\SQL2000Backup'

 

 

DECLARE UserDatabases_CTE_Cursor Cursor

FOR

 

-- Restoring user database names.

 select BakFileName from #SQL2000BackedUpdatabases

 where  BakFileName like '%bak'

 

OPEN UserDatabases_CTE_Cursor

DECLARE @BakFileName varchar(100);

DECLARE @dbName varchar(100);

DECLARE @backupPath varchar(100);

DECLARE @SQL2008RestoreFolder varchar(100);

DECLARE @backupQuery varchar(500);

 

-- make sure that the below path exists

set @backupPath = 'c:\SQL2000Backup\'

-- make sure that the below path exists

set @SQL2008RestoreFolder = 'c:\SQL2008RestoreFolder\'

 

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @BakFileName

While (@@FETCH_STATUS <> -1)

 

BEGIN

set @dbName = REPLACE(@BakFileName, '_[SQL2000].bak', '')

-- Restore SQL 2000 backed databases to SQL server 2008 or SQL Server 2008 R2.

set @backupQuery =  'RESTORE DATABASE ' +@dbName

+ ' FROM DISK = ''' + @backupPath + @BakFileName +''' '

+'WITH MOVE ''' + @dbName +''' TO ''' + @SQL2008RestoreFolder + @dbName + '.mdf'','

  + 'MOVE ''' + @dbName +'_Log'' TO ''' + @SQL2008RestoreFolder + @dbName +'.ldf'''

 

-- Print SQL statement

print @backupQuery

 

-- Execute backup script

EXEC (@backupQuery)

 

-- Get next database

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @BakFileName

END

 

CLOSE UserDatabases_CTE_Cursor

DEALLOCATE UserDatabases_CTE_Cursor

GO

 

 

 

3.      Backup SQL Server 2008 user databases using below script: Please customize parts highlighted in yellow

 

 

/********************************************************************************

*             Backup SQL server 2008 user databases

********************************************************************************/

 

use master;

go

 

DECLARE UserDatabases_CTE_Cursor Cursor

FOR

 

-- Selecting user database names.

select name as DatabaseName

from sysdatabases

where ([dbid] > 4) and ([name] not like '$')

 

OPEN UserDatabases_CTE_Cursor

DECLARE @dbName varchar(100);

DECLARE @backupPath varchar(100);

DECLARE @backupQuery varchar(500);

 

-- make sure that the below path exists

set @backupPath = 'c:\SQL2008Backup\'

 

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

While (@@FETCH_STATUS <> -1)

 

BEGIN

-- Backup SQL statement

set @backupQuery =  'backup database ' + @dbName + ' to disk = ''' + @backupPath + @dbName + '_[SQL2008].bak'''

 

-- Print SQL statement

print @backupQuery

 

-- Execute backup script

EXEC (@backupQuery)

 

-- Get next database

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

END

 

CLOSE UserDatabases_CTE_Cursor

DEALLOCATE UserDatabases_CTE_Cursor

GO

 

4.      Restore SQL 2008 backed-up user databases to SQL server 2012 using below scripts: Please customize parts highlighted in yellow.

 

 

/********************************************************************************

*             Restore SQL server 2008 backed databases to

*             SQL server 2012

********************************************************************************/

use master;

go

 

 

/*   Please un-comment below part to enable cp_cmdshell procedures

 Don't forget to disable it after migration completed.

 EXEC sp_configure'xp_cmdshell', 1

 RECONFIGURE

 GO

 

 EXEC sp_configure 'show advanced options', 1

 RECONFIGURE

 GO*/

 

 DECLARE @Return int

 IF OBJECT_ID('tempdb..#SQL2008BackedUpdatabases') IS NOT NULL DROP TABLE #SQL2008BackedUpdatabases

 

 CREATE TABLE #SQL2008BackedUpdatabases (BakFileName varchar(400), ID int IDENTITY(1,1))

 INSERT  #SQL2008BackedUpdatabases EXECUTE @Return = master.dbo.xp_cmdshell  'dir /B *.bak c:\SQL2008Backup'

 

 

DECLARE UserDatabases_CTE_Cursor Cursor

FOR

 

-- Restoring user database names.

 select BakFileName from #SQL2008BackedUpdatabases

 where  BakFileName like '%bak'

 

OPEN UserDatabases_CTE_Cursor

DECLARE @BakFileName varchar(100);

DECLARE @dbName varchar(100);

DECLARE @backupPath varchar(100);

DECLARE @SQL2012RestoreFolder varchar(100);

DECLARE @backupQuery varchar(500);

 

-- make sure that the below path exists

set @backupPath = 'c:\SQL2008Backup\'

-- make sure that the below path exists

set @SQL2012RestoreFolder = 'c:\SQL2012RestoreFolder\'

 

set @SQL2012RestoreFolder = 'c:\SQL2012RestoreFolder\'

 

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @BakFileName

While (@@FETCH_STATUS <> -1)

 

BEGIN

set @dbName = REPLACE(@BakFileName, '_[SQL2008].bak', '')

-- Restore SQL 2008 backed databases to SQL server 2012

set @backupQuery =  'RESTORE DATABASE ' +@dbName

+ ' FROM DISK = ''' + @backupPath + @BakFileName +''' '

+'WITH MOVE ''' + @dbName +''' TO ''' + @SQL2012RestoreFolder + @dbName + '.mdf'','

  + 'MOVE ''' + @dbName +'_Log'' TO ''' + @SQL2012RestoreFolder + @dbName +'.ldf'''

 

-- Print SQL statement

print @backupQuery

 

-- Execute backup script

EXEC (@backupQuery)

 

-- Get next database

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @BakFileName

END

 

CLOSE UserDatabases_CTE_Cursor

DEALLOCATE UserDatabases_CTE_Cursor

GO

 

 

P.S This posting is provided "AS IS" with no warranties, and confers no rights. 

 

 

 

 

Leave a Comment
  • Please add 7 and 3 and type the answer here:
  • Post
  • Great post. It saved me tons of times. Thanks again.

  • Thank You for saving my time..

  • Great Job Ahmed. Thanks very much.

Page 1 of 1 (3 items)