Where are my files?
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
1
down vote
favorite
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
New contributor
add a comment |
up vote
1
down vote
favorite
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
New contributor
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
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
New contributor
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
New contributor
New contributor
edited 2 days ago
Kin
52k478186
52k478186
New contributor
asked 2 days ago
Sergio Branda
61
61
New contributor
New contributor
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
up vote
3
down vote
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
Thanks a lot! I am testing it.
– Sergio Branda
yesterday
add a comment |
up vote
0
down vote
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>')
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
yesterday
after you alter the database to point its path to a new location, thisalter
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 Ialter
the file location but BEFORE setting the db offline and online again. This is indeed an interesting question.
– jyao
yesterday
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
3
down vote
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
Thanks a lot! I am testing it.
– Sergio Branda
yesterday
add a comment |
up vote
3
down vote
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
Thanks a lot! I am testing it.
– Sergio Branda
yesterday
add a comment |
up vote
3
down vote
up vote
3
down vote
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
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
edited 2 days ago
answered 2 days ago
Kin
52k478186
52k478186
Thanks a lot! I am testing it.
– Sergio Branda
yesterday
add a comment |
Thanks a lot! I am testing it.
– Sergio Branda
yesterday
Thanks a lot! I am testing it.
– Sergio Branda
yesterday
Thanks a lot! I am testing it.
– Sergio Branda
yesterday
add a comment |
up vote
0
down vote
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>')
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
yesterday
after you alter the database to point its path to a new location, thisalter
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 Ialter
the file location but BEFORE setting the db offline and online again. This is indeed an interesting question.
– jyao
yesterday
add a comment |
up vote
0
down vote
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>')
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
yesterday
after you alter the database to point its path to a new location, thisalter
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 Ialter
the file location but BEFORE setting the db offline and online again. This is indeed an interesting question.
– jyao
yesterday
add a comment |
up vote
0
down vote
up vote
0
down vote
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>')
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 2 days ago
jyao
2,339419
2,339419
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
yesterday
after you alter the database to point its path to a new location, thisalter
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 Ialter
the file location but BEFORE setting the db offline and online again. This is indeed an interesting question.
– jyao
yesterday
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
yesterday
after you alter the database to point its path to a new location, thisalter
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 Ialter
the file location but BEFORE setting the db offline and online again. This is indeed an interesting question.
– jyao
yesterday
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
yesterday
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
yesterday
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
yesterday
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
yesterday
add a comment |
Sergio Branda is a new contributor. Be nice, and check out our Code of Conduct.
Sergio Branda is a new contributor. Be nice, and check out our Code of Conduct.
Sergio Branda is a new contributor. Be nice, and check out our Code of Conduct.
Sergio Branda is a new contributor. Be nice, and check out our Code of Conduct.
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