December 16, 2011 at 5:41 am
Let's say for example, I have a staging database which I intend to refresh from a production backup. In this staging database, I have recently made some modifications including renaming logical filenames, changing physical filenames, etc. If I perform a restore with replace from the production backup does this present a problem? Will the renamed physical files be removed?
December 16, 2011 at 6:10 am
Very much so yes. You specify that you want to replace the existing database with the one in your backup.
For more information: Restore arguments
December 16, 2011 at 6:12 am
Yes, that's what replace means. Replace the DB that's there with the one in the backup and overwrite any files with the same name.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 16, 2011 at 6:21 am
NJ-DBA (12/16/2011)
Let's say for example, I have a staging database which I intend to refresh from a production backup. In this staging database, I have recently made some modifications including renaming logical filenames, changing physical filenames, etc. If I perform a restore with replace from the production backup does this present a problem? Will the renamed physical files be removed?
OK- let me clarify a little- with some of my own testing, I found that if I rename a phyiscal and logical file and even the filegroup, SQL SErver somehow "knows" which file previously belonged to the logical file... How does this work? For example, if I create a database like:
USE [master]
GO
/****** Object: Database [testrestore] Script Date: 12/16/2011 08:06:21 ******/
CREATE DATABASE [testrestore] ON PRIMARY
( NAME = N'testrestore123', FILENAME = N'E:\MSSQLServer2008\Data\testrestore123.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [newfg]
( NAME = N'testrestore456', FILENAME = N'E:\MSSQLServer2008\Data\testrestore456.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testrestore_log123', FILENAME = N'E:\MSSQLServer2008\TLog\testrestore_log123.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
and immediately take a backup. Then make changes like:
ALTER DATABASE testrestore
MODIFY FILEGROUP newfg NAME = changedfg
ALTER DATABASE testrestore
MODIFY FILE (NAME = testrestore123, NEWNAME = changedrestore123)
ALTER DATABASE testrestore
MODIFY FILE (NAME =changedrestore123, FILENAME = 'E:\MSSQLServer2008\Data\changedrestore123.mdf')
ALTER DATABASE testrestore
MODIFY FILE (NAME = testrestore456, NEWNAME = changedrestore456)
ALTER DATABASE testrestore
MODIFY FILE (NAME =changedrestore456, FILENAME = 'E:\MSSQLServer2008\Data\changedrestore456.ndf')
alter database testrestore set offline with rollback immediate
<rename physical files at OS>
alter database testrestore set online
How does sql server know which files go where if I used the WITH REPLACE option?
December 16, 2011 at 7:21 am
NJ-DBA (12/16/2011)
NJ-DBA (12/16/2011)
Let's say for example, I have a staging database which I intend to refresh from a production backup. In this staging database, I have recently made some modifications including renaming logical filenames, changing physical filenames, etc. If I perform a restore with replace from the production backup does this present a problem? Will the renamed physical files be removed?OK- let me clarify a little- with some of my own testing, I found that if I rename a phyiscal and logical file and even the filegroup, SQL SErver somehow "knows" which file previously belonged to the logical file... How does this work? For example, if I create a database like:
USE [master]
GO
/****** Object: Database [testrestore] Script Date: 12/16/2011 08:06:21 ******/
CREATE DATABASE [testrestore] ON PRIMARY
( NAME = N'testrestore123', FILENAME = N'E:\MSSQLServer2008\Data\testrestore123.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [newfg]
( NAME = N'testrestore456', FILENAME = N'E:\MSSQLServer2008\Data\testrestore456.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testrestore_log123', FILENAME = N'E:\MSSQLServer2008\TLog\testrestore_log123.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
and immediately take a backup. Then make changes like:
ALTER DATABASE testrestore
MODIFY FILEGROUP newfg NAME = changedfg
ALTER DATABASE testrestore
MODIFY FILE (NAME = testrestore123, NEWNAME = changedrestore123)
ALTER DATABASE testrestore
MODIFY FILE (NAME =changedrestore123, FILENAME = 'E:\MSSQLServer2008\Data\changedrestore123.mdf')
ALTER DATABASE testrestore
MODIFY FILE (NAME = testrestore456, NEWNAME = changedrestore456)
ALTER DATABASE testrestore
MODIFY FILE (NAME =changedrestore456, FILENAME = 'E:\MSSQLServer2008\Data\changedrestore456.ndf')
alter database testrestore set offline with rollback immediate
<rename physical files at OS>
alter database testrestore set online
How does sql server know which files go where if I used the WITH REPLACE option?
To simplify my question, what I am essentially asking is what happens when there is a mismatch between the logical and/or physical filenames in the existing database and those in the restore? I can see that the physical file names do not go back to the original configuration, so how does SQL Server "know" which logical file goes with which physical file... does this make sense?
December 16, 2011 at 7:26 am
If I understand your scenario right. First of all, have you checked the link I posted? Secondy, SQL Server does not know what files or filegroups were in the previous database. You have specified with replace, meaning that the previous databsae is deleted, and you restore the database in your backup. The backup contains information about files, filegroups and filepaths. If these filepaths is not available, you have to specify the move option when you restore.
December 16, 2011 at 7:33 am
okbangas (12/16/2011)
If I understand your scenario right. First of all, have you checked the link I posted? Secondy, SQL Server does not know what files or filegroups were in the previous database. You have specified with replace, meaning that the previous databsae is deleted, and you restore the database in your backup. The backup contains information about files, filegroups and filepaths. If these filepaths is not available, you have to specify the move option when you restore.
Hi OKbangas,
I'm familiar with the restore arguments, and everything you said above is exactly in alignment with my understanding, however this doesnt seem to be what I am observing.
After making the changes, and then restoring from the pre-changes backup using WITH REPLACE, SQL Server is placing the original logical files (testrestore123 and testrestore456) onto the newly named phyiscal files (changedrestore123 and changedrestore456)... but the original backup has no information about these files... and the changed database doesnt have the old logical files, but somehow it knos to put testrestore123 onto the physical file changedrestore123... how does it make this determination- or really how does it know thta testrestore123 does not belong on changedrestore456...
December 16, 2011 at 7:42 am
NJ-DBA (12/16/2011)
okbangas (12/16/2011)
If I understand your scenario right. First of all, have you checked the link I posted? Secondy, SQL Server does not know what files or filegroups were in the previous database. You have specified with replace, meaning that the previous databsae is deleted, and you restore the database in your backup. The backup contains information about files, filegroups and filepaths. If these filepaths is not available, you have to specify the move option when you restore.Hi OKbangas,
I'm familiar with the restore arguments, and everything you said above is exactly in alignment with my understanding, however this doesnt seem to be what I am observing.
After making the changes, and then restoring from the pre-changes backup using WITH REPLACE, SQL Server is placing the original logical files (testrestore123 and testrestore456) onto the newly named phyiscal files (changedrestore123 and changedrestore456)... but the original backup has no information about these files... and the changed database doesnt have the old logical files, but somehow it knos to put testrestore123 onto the physical file changedrestore123... how does it make this determination- or really how does it know thta testrestore123 does not belong on changedrestore456...
ah, found:
If the database catalog is accessible and the name is in the catalog, the Database Engine determines the GUID of the corresponding file or filegroup and restores the file or filegroup with that GUID from the backup. If the name does not match one of the current files or filegroups, an error occurs.
from : http://msdn.microsoft.com/en-us/library/ms189066.aspx
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply