Where are my files?
The DBA run a file relocation script to move the physical Database file from where it was running to I:.
ALTER DATABASE [cro01] MODIFY FILE ( name=cro01,filename=N'i:SQLDatacro01.MDF')
He take the Database offline ad copy the file.
But for some strange reason he don't finnish the job correctly. We have now mdf files duplicated and we dont know which one is running. How do I know the "real" running location of the files via a Tsql query? Is it posible? I want to avoid checking manually each disk file location against each sys.master_files entry.
sql-server alter-database
edited Nov 15 '18 at 18:16
Kin
53.3k480188
asked Nov 15 '18 at 18:14
Sergio BrandaSergio Branda
61
add a comment |
The DBA run a file relocation script to move the physical Database file from where it was running to I:.
ALTER DATABASE [cro01] MODIFY FILE ( name=cro01,filename=N'i:SQLDatacro01.MDF')
He take the Database offline ad copy the file.
But for some strange reason he don't finnish the job correctly. We have now mdf files duplicated and we dont know which one is running. How do I know the "real" running location of the files via a Tsql query? Is it posible? I want to avoid checking manually each disk file location against each sys.master_files entry.
sql-server alter-database
edited Nov 15 '18 at 18:16
Kin
53.3k480188
asked Nov 15 '18 at 18:14
Sergio BrandaSergio Branda
61
add a comment |
The DBA run a file relocation script to move the physical Database file from where it was running to I:.
ALTER DATABASE [cro01] MODIFY FILE ( name=cro01,filename=N'i:SQLDatacro01.MDF')
He take the Database offline ad copy the file.
But for some strange reason he don't finnish the job correctly. We have now mdf files duplicated and we dont know which one is running. How do I know the "real" running location of the files via a Tsql query? Is it posible? I want to avoid checking manually each disk file location against each sys.master_files entry.
sql-server alter-database
edited Nov 15 '18 at 18:16
Kin
53.3k480188
asked Nov 15 '18 at 18:14
Sergio BrandaSergio Branda
61
The DBA run a file relocation script to move the physical Database file from where it was running to I:.
ALTER DATABASE [cro01] MODIFY FILE ( name=cro01,filename=N'i:SQLDatacro01.MDF')
He take the Database offline ad copy the file.
But for some strange reason he don't finnish the job correctly. We have now mdf files duplicated and we dont know which one is running. How do I know the "real" running location of the files via a Tsql query? Is it posible? I want to avoid checking manually each disk file location against each sys.master_files entry.
sql-server alter-database
sql-server alter-database
edited Nov 15 '18 at 18:16
Kin
53.3k480188
asked Nov 15 '18 at 18:14
Sergio BrandaSergio Branda
61
edited Nov 15 '18 at 18:16
Kin
53.3k480188
asked Nov 15 '18 at 18:14
Sergio BrandaSergio Branda
61
edited Nov 15 '18 at 18:16
Kin
53.3k480188
edited Nov 15 '18 at 18:16
Kin
53.3k480188
edited Nov 15 '18 at 18:16
Kin
53.3k480188
53.3k480188
asked Nov 15 '18 at 18:14
Sergio BrandaSergio Branda
61
asked Nov 15 '18 at 18:14
Sergio BrandaSergio Branda
61
asked Nov 15 '18 at 18:14
Sergio BrandaSergio Branda
61
61
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
dbatools to the rescue - use Find-DbaOrphanedFile
This command searches all directories associated with SQL database files for database files that are not currently in use by the SQL Server instance.
By default, it looks for orphaned .mdf, .ldf and .ndf files in the rootdata directory, the default data path, the default log path, the system paths and any directory in use by any attached directory.
You can specify additional filetypes using the -FileType parameter, and additional paths to search using the -Path parameter.
e.g. Below command will - Connect to sqlserver2014a, authenticating with SQL Server authentication, and searches for orphaned files. Returns server name, local filename, and unc path to file.
$cred = get-credential -UserName yourUserThatHasAccessToYourServer -Message "Enter correct Password"
#above will pop up message box to enter the password !
Find-DbaOrphanedFile -SqlInstance sqlserver2014a -SqlCredential $cred
answered Nov 15 '18 at 18:46
KinKin
53.3k480188
add a comment |
You can use the following to find the real physical name
exec sp_helpdb <your db name>
-- or
select * from master.sys.master_files
where database_id = db_id('<your db name>')
answered Nov 15 '18 at 18:28
jyaojyao
2,319419
Sorry, this query tell me where it should be, not where it really is. Pf I run the above alter and change the path where the files are running now to a new location, the Database continue to point to the "old" place while the script tell you where it should be. When you take the DB offline and online again, then the DB start using the new path and forget the old location. Of course, you need to copy or move the files to the new location also.
– Sergio Branda
Nov 16 '18 at 15:50
after you alter the database to point its path to a new location, thisalteraction only changes the metadata info. But I kind of understand you now, what you want to know is where is the real data file location the sql server engine uses after Ialterthe file location but BEFORE setting the db offline and online again. This is indeed an interesting question.
– jyao
Nov 16 '18 at 17:57
Right jyao!! You got it. It seems difficult to get the info with a single query on the SQL:
– Sergio Branda
Nov 19 '18 at 11:52
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f222661%2fwhere-are-my-files%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
dbatools to the rescue - use Find-DbaOrphanedFile
This command searches all directories associated with SQL database files for database files that are not currently in use by the SQL Server instance.
By default, it looks for orphaned .mdf, .ldf and .ndf files in the rootdata directory, the default data path, the default log path, the system paths and any directory in use by any attached directory.
You can specify additional filetypes using the -FileType parameter, and additional paths to search using the -Path parameter.
e.g. Below command will - Connect to sqlserver2014a, authenticating with SQL Server authentication, and searches for orphaned files. Returns server name, local filename, and unc path to file.
$cred = get-credential -UserName yourUserThatHasAccessToYourServer -Message "Enter correct Password"
#above will pop up message box to enter the password !
Find-DbaOrphanedFile -SqlInstance sqlserver2014a -SqlCredential $cred
answered Nov 15 '18 at 18:46
KinKin
53.3k480188
add a comment |
dbatools to the rescue - use Find-DbaOrphanedFile
This command searches all directories associated with SQL database files for database files that are not currently in use by the SQL Server instance.
By default, it looks for orphaned .mdf, .ldf and .ndf files in the rootdata directory, the default data path, the default log path, the system paths and any directory in use by any attached directory.
You can specify additional filetypes using the -FileType parameter, and additional paths to search using the -Path parameter.
e.g. Below command will - Connect to sqlserver2014a, authenticating with SQL Server authentication, and searches for orphaned files. Returns server name, local filename, and unc path to file.
$cred = get-credential -UserName yourUserThatHasAccessToYourServer -Message "Enter correct Password"
#above will pop up message box to enter the password !
Find-DbaOrphanedFile -SqlInstance sqlserver2014a -SqlCredential $cred
answered Nov 15 '18 at 18:46
KinKin
53.3k480188
add a comment |
dbatools to the rescue - use Find-DbaOrphanedFile
This command searches all directories associated with SQL database files for database files that are not currently in use by the SQL Server instance.
By default, it looks for orphaned .mdf, .ldf and .ndf files in the rootdata directory, the default data path, the default log path, the system paths and any directory in use by any attached directory.
You can specify additional filetypes using the -FileType parameter, and additional paths to search using the -Path parameter.
e.g. Below command will - Connect to sqlserver2014a, authenticating with SQL Server authentication, and searches for orphaned files. Returns server name, local filename, and unc path to file.
$cred = get-credential -UserName yourUserThatHasAccessToYourServer -Message "Enter correct Password"
#above will pop up message box to enter the password !
Find-DbaOrphanedFile -SqlInstance sqlserver2014a -SqlCredential $cred
answered Nov 15 '18 at 18:46
KinKin
53.3k480188
dbatools to the rescue - use Find-DbaOrphanedFile
This command searches all directories associated with SQL database files for database files that are not currently in use by the SQL Server instance.
By default, it looks for orphaned .mdf, .ldf and .ndf files in the rootdata directory, the default data path, the default log path, the system paths and any directory in use by any attached directory.
You can specify additional filetypes using the -FileType parameter, and additional paths to search using the -Path parameter.
e.g. Below command will - Connect to sqlserver2014a, authenticating with SQL Server authentication, and searches for orphaned files. Returns server name, local filename, and unc path to file.
$cred = get-credential -UserName yourUserThatHasAccessToYourServer -Message "Enter correct Password"
#above will pop up message box to enter the password !
Find-DbaOrphanedFile -SqlInstance sqlserver2014a -SqlCredential $cred
answered Nov 15 '18 at 18:46
KinKin
53.3k480188
edited Nov 15 '18 at 20:18
answered Nov 15 '18 at 18:46
KinKin
53.3k480188
answered Nov 15 '18 at 18:46
KinKin
53.3k480188
answered Nov 15 '18 at 18:46
KinKin
53.3k480188
53.3k480188
add a comment |
add a comment |
You can use the following to find the real physical name
exec sp_helpdb <your db name>
-- or
select * from master.sys.master_files
where database_id = db_id('<your db name>')
answered Nov 15 '18 at 18:28
jyaojyao
2,319419
Sorry, this query tell me where it should be, not where it really is. Pf I run the above alter and change the path where the files are running now to a new location, the Database continue to point to the "old" place while the script tell you where it should be. When you take the DB offline and online again, then the DB start using the new path and forget the old location. Of course, you need to copy or move the files to the new location also.
– Sergio Branda
Nov 16 '18 at 15:50
after you alter the database to point its path to a new location, thisalteraction only changes the metadata info. But I kind of understand you now, what you want to know is where is the real data file location the sql server engine uses after Ialterthe file location but BEFORE setting the db offline and online again. This is indeed an interesting question.
– jyao
Nov 16 '18 at 17:57
Right jyao!! You got it. It seems difficult to get the info with a single query on the SQL:
– Sergio Branda
Nov 19 '18 at 11:52
add a comment |
You can use the following to find the real physical name
exec sp_helpdb <your db name>
-- or
select * from master.sys.master_files
where database_id = db_id('<your db name>')
answered Nov 15 '18 at 18:28
jyaojyao
2,319419
Sorry, this query tell me where it should be, not where it really is. Pf I run the above alter and change the path where the files are running now to a new location, the Database continue to point to the "old" place while the script tell you where it should be. When you take the DB offline and online again, then the DB start using the new path and forget the old location. Of course, you need to copy or move the files to the new location also.
– Sergio Branda
Nov 16 '18 at 15:50
after you alter the database to point its path to a new location, thisalteraction only changes the metadata info. But I kind of understand you now, what you want to know is where is the real data file location the sql server engine uses after Ialterthe file location but BEFORE setting the db offline and online again. This is indeed an interesting question.
– jyao
Nov 16 '18 at 17:57
Right jyao!! You got it. It seems difficult to get the info with a single query on the SQL:
– Sergio Branda
Nov 19 '18 at 11:52
add a comment |
You can use the following to find the real physical name
exec sp_helpdb <your db name>
-- or
select * from master.sys.master_files
where database_id = db_id('<your db name>')
answered Nov 15 '18 at 18:28
jyaojyao
2,319419
You can use the following to find the real physical name
exec sp_helpdb <your db name>
-- or
select * from master.sys.master_files
where database_id = db_id('<your db name>')
answered Nov 15 '18 at 18:28
jyaojyao
2,319419
answered Nov 15 '18 at 18:28
jyaojyao
2,319419
answered Nov 15 '18 at 18:28
jyaojyao
2,319419
answered Nov 15 '18 at 18:28
jyaojyao
2,319419
2,319419
Sorry, this query tell me where it should be, not where it really is. Pf I run the above alter and change the path where the files are running now to a new location, the Database continue to point to the "old" place while the script tell you where it should be. When you take the DB offline and online again, then the DB start using the new path and forget the old location. Of course, you need to copy or move the files to the new location also.
– Sergio Branda
Nov 16 '18 at 15:50
after you alter the database to point its path to a new location, thisalteraction only changes the metadata info. But I kind of understand you now, what you want to know is where is the real data file location the sql server engine uses after Ialterthe file location but BEFORE setting the db offline and online again. This is indeed an interesting question.
– jyao
Nov 16 '18 at 17:57
Right jyao!! You got it. It seems difficult to get the info with a single query on the SQL:
– Sergio Branda
Nov 19 '18 at 11:52
add a comment |
Sorry, this query tell me where it should be, not where it really is. Pf I run the above alter and change the path where the files are running now to a new location, the Database continue to point to the "old" place while the script tell you where it should be. When you take the DB offline and online again, then the DB start using the new path and forget the old location. Of course, you need to copy or move the files to the new location also.
– Sergio Branda
Nov 16 '18 at 15:50
after you alter the database to point its path to a new location, thisalteraction only changes the metadata info. But I kind of understand you now, what you want to know is where is the real data file location the sql server engine uses after Ialterthe file location but BEFORE setting the db offline and online again. This is indeed an interesting question.
– jyao
Nov 16 '18 at 17:57
Right jyao!! You got it. It seems difficult to get the info with a single query on the SQL:
– Sergio Branda
Nov 19 '18 at 11:52
Sorry, this query tell me where it should be, not where it really is. Pf I run the above alter and change the path where the files are running now to a new location, the Database continue to point to the "old" place while the script tell you where it should be. When you take the DB offline and online again, then the DB start using the new path and forget the old location. Of course, you need to copy or move the files to the new location also.
– Sergio Branda
Nov 16 '18 at 15:50
Sorry, this query tell me where it should be, not where it really is. Pf I run the above alter and change the path where the files are running now to a new location, the Database continue to point to the "old" place while the script tell you where it should be. When you take the DB offline and online again, then the DB start using the new path and forget the old location. Of course, you need to copy or move the files to the new location also.
– Sergio Branda
Nov 16 '18 at 15:50
after you alter the database to point its path to a new location, this
alter action only changes the metadata info. But I kind of understand you now, what you want to know is where is the real data file location the sql server engine uses after I alter the file location but BEFORE setting the db offline and online again. This is indeed an interesting question.– jyao
Nov 16 '18 at 17:57
after you alter the database to point its path to a new location, this
alter action only changes the metadata info. But I kind of understand you now, what you want to know is where is the real data file location the sql server engine uses after I alter the file location but BEFORE setting the db offline and online again. This is indeed an interesting question.– jyao
Nov 16 '18 at 17:57
Right jyao!! You got it. It seems difficult to get the info with a single query on the SQL:
– Sergio Branda
Nov 19 '18 at 11:52
Right jyao!! You got it. It seems difficult to get the info with a single query on the SQL:
– Sergio Branda
Nov 19 '18 at 11:52
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f222661%2fwhere-are-my-files%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
