Possible restore issue with 7.3.0.383 to replace database

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: JonathanWatts, Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

Possible restore issue with 7.3.0.383 to replace database

Postby ChrisAVWood » Wed Apr 03, 2013 8:33 pm

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
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA

Postby ChrisAVWood » Wed Apr 03, 2013 8:41 pm

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

Chris
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA

Postby petey » Thu Apr 04, 2013 4:50 am

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:

Code: Select all
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:

Code: Select all
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.

Code: Select all
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
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2226
Joined: Sun Apr 24, 2005 12:34 pm

Postby ChrisAVWood » Thu Apr 04, 2013 7:32 pm

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 D:\\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 D:\\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
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA

Postby petey » Fri Apr 05, 2013 7:48 am

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.

Code: Select all
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.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2226
Joined: Sun Apr 24, 2005 12:34 pm

Postby ChrisAVWood » Fri Apr 05, 2013 4:18 pm

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
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA

Postby petey » Mon Apr 08, 2013 2:08 am

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.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2226
Joined: Sun Apr 24, 2005 12:34 pm


Return to SQL Backup 7

Who is online

Users browsing this forum: No registered users and 0 guests