Moving SQL2005 FileGroups to SQL2008

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Moving SQL2005 FileGroups to SQL2008

Author

Topic

njsql
Starting Member

4 Posts

Posted - 2010-09-19 : 21:49:00

Have an existing 2005 Database. Performed a full backup, using file and filegroups, on the database using enterprise manager. Recovery model: Full.

Scripted a "create database..." on the 2005 database. Executed this statement on 2008 database, changing the directory that the filesgroups were create in.

CREATE DATABASE [NJNGDIST] ON PRIMARY
( NAME = N'NJNGDIST',
FILENAME = N'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NJNGDIST.ndf' ,
SIZE = 16384KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [NJNGDIST_RASTER_ADD_DEL_IX]
( NAME = N'NJNGDIST_RASTER_ADD_DEL_IX',
FILENAME = N'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NJNGDIST_RASTER_ADD_DEL_IX.ndf' ,
SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
FILEGROUP [SDE]
( NAME = N'SDE1', FILENAME = N'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SDE.ndf' ,
SIZE = 416384KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) LOG ON
( NAME = N'NJNGDIST_LOG',
FILENAME = N'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NJNGDIST_LOG.ldf' , SIZE = 66809600KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

Performing a restore / file and filegroup on the new 2008 database using the following statement:

RESTORE DATABASE [NJNGDIST]
FILE = N'NJNGDIST',
FILE = N'SDE1',
FILE = N'NJNGDIST_RASTER_ADD_DEL_IX'
FROM DISK = N'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\NJNGDIST_FileGroup_Sunday.bak'
WITH FILE = 1,
MOVE N'NJNGDIST' TO N'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NJNGDIST.ndf',
MOVE N'SDE1' TO N'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SDE.ndf',
MOVE N'NJNGDIST_RASTER_ADD_DEL_IX' TO N'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NJNGDIST_RASTER_ADD_DEL_IX.ndf'
NOUNLOAD, REPLACE, STATS = 10
GO

Getting the following error on the restore.
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'NJNGDIST' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

The database has not changed. When I try this using enterprise manager, after selecting the backup, in the bottom window below the "Select the backup sets to restore:"....I see under database the name "NJNGDIST".

Any help would be appreciated. I have tried everything from "copy database" to attaching. I don't know what is wrong with the statement above that it doesn't recognize the database name as correct.

Thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-09-20 : 07:43:24

You don't need to pre-create the database. Drop that database that you created and just restore the backup as a new database with the desired name.

You can use the WITH MOVE clauses of the restore backup to specify where the files should go.

--
Gail Shaw
SQL Server MVP

njsql
Starting Member

4 Posts

Posted - 2010-09-20 : 07:49:31

Thank you. I have tried dropping the database and using the below statement to recreate. I struggling to find the syntax for the "with move".

RESTORE DATABASE [NJNGDIST]
FILE = N'NJNGDIST',
FILE = N'SDE1',
FILE = N'NJNGDIST_RASTER_ADD_DEL_IX'
FROM DISK = N'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\NJNGDIST_FileGroup_Sunday.bak'
WITH
MOVE N'NJNGDIST' TO N'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NJNGDIST.ndf',
MOVE N'SDE1' TO N'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SDE.ndf',
MOVE N'NJNGDIST_RASTER_ADD_DEL_IX' TO N'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NJNGDIST_RASTER_ADD_DEL_IX.ndf',
NOUNLOAD, REPLACE, STATS = 10
GO

Here is the error I get when I execute the above statement:

Msg 3176, Level 16, State 1, Line 1
File 'E:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NJNGDIST.ndf' is claimed by 'NJNGDIST_XML'(11) and 'NJNGDIST'(1). The WITH MOVE clause can be used to relocate one or more files.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

njsql
Starting Member

4 Posts

Posted - 2010-09-20 : 08:39:41

This has been resolved.

In SQL2008 when you restore a database with a file name the same as the database name, the restore failes. The fix was to rename the NJNGDIST.ndf create to something different than the database name.

For database NJNGDIST:

Old: MOVE N'NJNGDIST' TO N'NJNGDIST.ndf',
New: MOVE N'NJNGDIST' TO N'NJNGDISTnew.ndf',

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources