SQL Server 2005/2008 backup and restore questions...

sshannysshanny Posts: 18 Bronze 2
edited December 4, 2008 5:40AM in SQL Backup Previous Versions
I have a couple of questions.

Everything is running on 64bit Enterprise Edition.

We have a SS 2005 based 15TB data warehouse with lots of filegroups and files mapped to all sorts of direct attached disks.

We are moving to SS 2008 on a SAN where we won't need to have all the drive mappings that we do now.

1. Is it possible to restore a SS 2005 backup to SS 2008? If so do I just need to change the compatibility level after restore to take advantage of the 2008 features like page level compression etc...

2. Is it possible to remap the file mount points during restore? ie I may have filegroup GLOBAL that contains files: global_1.ndf, global_2.ndf and global_3.ndf mapped to drives X, Y, and Z respectively. X,Y,Z are not going to exist on the new SAN so can I change this during the restore process that so those files map to drive G for example?



  • Hi Sean,

    Yes, it is possible to restore a SQL Server 2005 backup to a SQL Server 2008 instance. SQL Server will run the upgrade steps necessary when the database is brought online. As far as i'm aware changing the compatibility mode isn't necessary to use page level compression etc

    You can find out the differences between compatibility level 90 and 100 from Books Online.

    When restoring, you can specify physical locations for the logical file groups, the command will be something similar to the following :
    EXECUTE master..sqlbackup N'-SQL "RESTORE DATABASE [Filegroup]  FROM DISK = ''\\Share\Backups\FileGroup.sqb'' WITH RECOVERY, MOVE ''Test1dat1'' TO ''D:\Data\FilegroupTest1.ndf'', MOVE ''Test1dat2'' TO ''D:\Data\FilegroupTest2.ndf'', MOVE ''test1dat3'' TO ''D:\Data\FilegroupTest3.ndf'', MOVE ''test1dat4'' TO ''D:\Data\FilegroupTest4.ndf'', MOVE ''FileGroupTest_log'' TO ''D:\Data\FilegroupTest_log.LDF''"'
    I hope this helps.
    Matthew Flatt
    Redgate Foundry
Sign In or Register to comment.