Possible restore issue with 7.3.0.383 to replace database

ChrisAVWood

Hi,

I am running on W2K12 on SQL2012 SP1 and when I try to use a script that has worked before on W2K3R2 on SQL2005 to restore a database with replace it seems to want to place the database on the same drive as the original backup. I am trying to restore to a folder on our E:drive, where the existing database is currently, from a backup that backed up a database that resided on a D:drive.

Now I am forced to add the move parameters. Looks like this means the default has changed from 7.1.

Here is a snippet of my script

EXECUTE master..sqlbackup N'-SQL "RESTORE DATABASE [AHAH_DEVL] FROM DISK = ''G:\DBABackupsOnly\FULL_AHAH_PROD_20130320_030300_23895.sq6'' WITH RECOVERY, REPLACE"'
go

The AHAH_DEVL resides on the E:drive and the AHAH_PROD resides on the D:drive.

This would tend to make this version unusable for us.

Chris

ChrisAVWood

I should add the GUI works as expected. It is when you use a script.

Chris

petey

I don't recall anything changed with regards to how the REPLACE function worked. The most important thing is getting the database logical names to match, and SQL Server handles the rest. Note that the following examples use native SQL Server backup/restore commands, and the results would be identical if you used SQL Backup commands.

E.g. this fails because the logical names in the backup set do not match that of the 'db2' database:

CREATE DATABASE db1 ON PRIMARY (NAME = 'db1_dat', FILENAME = 'f:\data\db1\db1.mdf') LOG ON (name = 'db1_log', FILENAME = 'f:\data\db1\db1.ldf')
GO
CREATE DATABASE db2 ON PRIMARY (NAME = 'db2_dat', FILENAME = 'f:\data\db2\db2.mdf') LOG ON (name = 'db2_log', FILENAME = 'f:\data\db2\db2.ldf')
GO
BACKUP DATABASE db1 TO DISK = 'f:\data\db1.bak'
GO
RESTORE DATABASE db2 FROM DISK = 'f:\data\db1.bak' WITH REPLACE
GO

This succeeds because we restored 'db2' from the 'db1' backup set, preserving the logical names:

DROP DATABASE db2
GO
RESTORE DATABASE db2 FROM DISK = 'f:\data\db1.bak' WITH MOVE 'db1_dat' TO 'f:\data\db2\db2.mdf', MOVE 'db1_log' TO 'f:\data\db2\db2.ldf', REPLACE
GO
RESTORE DATABASE db2 FROM DISK = 'f:\data\db1.bak' WITH REPLACE
GO

This also works because the logical names of both databases match.

DROP DATABASE db2
GO
CREATE DATABASE db2 ON PRIMARY (NAME = 'db1_dat', FILENAME = 'f:\data\db2\db2.mdf') LOG ON (name = 'db1_log', FILENAME = 'f:\data\db2\db2.ldf')
GO
RESTORE DATABASE db2 FROM DISK = 'f:\data\db1.bak' WITH REPLACE
GO

ChrisAVWood

Pete,

Here is what I did.

I created the new database under E:\applctn\ahah\devl\database with 3 files, SYS/DATA_001 and LOG_001. This is in SQL2012 running on W2K12. I had a backup taken using SQLBackup 7.1 in SQL2005 on W2K3R2 of the prod database where it resides under \applctn\ahah\prod\database with the same 3 files, SYS/DATA_001 and LOG_001. The backup creates two files to speed the process up.

I copied the 2 backups files to the W2K12 server. When I tried to run the script it failed because it tried to restore to \applctn\ahah\prod\database. Once I put the moved statements in it worked fine and replaced the original databases I had created. I had this happen to another database under the same circumstances. This made me believe that maybe the restore from script had looked at the backup files for its location information rather than the master DB locations.

We do lots of restored from production backups to at and dev servers where the file locations differ from the original ones and the script works fine. It gets its location information from the master DB rather than the backup file itself.

Can you check this out please?

Thanks

Chris

petey

You are right, SQL Server 2012 seems to have a different behavior with regards to restoring over an existing database.

In SQL Server 2005 and 2008, you could overwrite an existing database, and have the existing database's data files (whose logical names match those in the backup set) replaced by those in the backup set, using the REPLACE option.

In SQL Server 2012, this is no longer the case. SQL Server 2012 insists on placing the data files in their original location, unless the existing database was originally restored from a backup of the same database.

In your case, you would need to first restore the dev database from a backup of the production database, using the MOVE options. After that, subsequent restores can be performed without the need for the MOVE options.

If it's any consolation, SQL Backup 7.3 can help somewhat with the MOVE options. If all the data files and trx log files can be grouped in the same folders, you can use the MOVE DATAFILES/LOGFILES shortcut e.g.

EXEC master..sqlbackup '-sql "RESTORE DATABASE ahah_devl FROM DISK = [...] WITH MOVE DATAFILES TO [E:\applctn\ahah\devl\database\data\], MOVE LOGFILES TO [E:\applctn\ahah\devl\database\log\]"'

There is also MOVE FILESTREAMS ... for filestreams, and MOVE FULLTEXTCATALOGS ... for full text catalogs.

ChrisAVWood

Thanks for the update.

That is really interesting. I will certainly test out the MOVE DATAFILES and MOVE LOGFILES options.

I now need to ask this further question. We will be using AlwaysOn Availability Groups. The thinking is this currently. We have a database on say the dev server, in a different location than the prod backup. We take the dev database out of the availability group and then restore the prod backup to both server A with recovery and to server B with norecovery. Then put the databases back into the availability group.

Based on what you have said and I saw I need the MOVE statements this first time. Now this stays the same for a few days and we start taking backups, on the node that the availability group allows. Now we have to perform another restore from prod to dev. Will I still need the MOVE or will SQL2012 remember where the databases reside on dev and I can restore without the MOVE?

Thanks

Chris

petey

I think you would not need to use the MOVE options, since the database you are overwriting was originally restored using backups from the same database whose backup sets you are now using to restore from.

From what I can tell, as long as the database you are trying to restore over, and the backup set you are using to restore from, have the same family GUID value, SQL Server will use the file locations of the database you are now overwriting.

I don't know how to find out the family GUID value of an active database, but if you back up that database, you can read this value using the RESTORE HEADERONLY command.