Migrating SQL Server 2000 user databases to SQL server 2012


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. 

 

 

 

 

Comments (3)

  1. Marx says:

    Great post. It saved me tons of times. Thanks again.

  2. Manas says:

    Thank You for saving my time..

  3. Shagaf Khot says:

    Great Job Ahmed. Thanks very much.