Migrating SQL Server 2000 user databases to SQL server 2012 - Ahmed Ibrahim - SQL Server - Site Home

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;

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

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;

/* 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

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

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

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

* Backup SQL server 2008 user databases

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

use master;

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

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;

/* 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

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\'

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

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