Often you see people complaining that although the file exists in the directory, the code throws an error Operating system error 2(The system cannot find the file specified.). Ok. The following need the file path to be specified in the code

1 Backup/Restore a database
2 Attach a database file
3 Bulk Insert
4 BCP to a file
5 Accessing files (.txt,.csv,etc) via OPENROWSET like Export to Excel with column names
etc

You need to be awared that the file path specified in your code is, by default, expected to be available in SERVER's physical directory and not in your LOCAL system's directory. Most of the newbies forget this and have the files in their local system and expect SQL Server to access the files. If you have files in your local system do either of these

1 Copy the files to SERVER's directory and run the code
2 Have files in your LOCAL system, let the SERVER has enough permission to access files from your local system and use UNC Path

Here is an example of how to let the SERVER use files from local system using UNC path. If you have backup file named test.bak in D directory of your local system named test, use the following code to restore it without copying it the Server's directory

restore database mydb from disk='\\test\D$\test.bak'