Sometimes, you just need to get a list of filenames into Management Studio. I typically need this when choosing files to restore for non-production backups, but there’s plenty of possible uses.
The easiest way I’ve found of getting a list of files into SSMS (or any other editor) is via the clipboard, specifically using clip.exe, found in C:\Windows\System32 on Windows 7 and Windows Server 2008.
Clip.exe allows you to pipe any cmd.exe or Powershell output directly into the clipboard, without having to copy out of the console window. For example, we have the following directory with 5 long filenames:
Volume in drive D is Seagate 2TB Volume Serial Number is E6B1-FEB0 Directory of D:\SQLBackup 24/08/2012 04:39 AM <DIR> . 24/08/2012 04:39 AM <DIR> .. 16/08/2012 09:39 PM 193,052,672 AdventureWorks_Full_20120806_1833.bak 16/08/2012 09:39 PM 193,052,672 AdventureWorks_Full_20120807_1834.bak 16/08/2012 09:39 PM 93,052,672 Northwind_Full_20120806_1830.bak 16/08/2012 09:39 PM 93,122,304 Northwind_Full_20120807_1834.bak 16/08/2012 09:39 PM 23,052,672 Pubs_Full_20120806_1833.bak 6 File(s) 965,333,176 bytes 2 Dir(s) 1,417,170,239,488 bytes free
By using dir’s /b (bare format) switch, and piping the results to clip.exe, we get:
dir /b | clip
Of course, you can use any filter on the dir command, to show all .bak files for a specific date (based on filename):
dir *20120806*.bak /b | clip
The results are now in the clipboard, and can be pasted into any application:
AdventureWorks_Full_20120806_1833.bak Northwind_Full_20120806_1830.bak Pubs_Full_20120806_1833.bak
In Powershell, you can use the following (where “dir” can be replaced with gci, ls, or Get-ChildItem):
dir *20120806*.bak -name | clip
But I’m on Windows Server 2003!
If you don’t have clip.exe available, you can get similar functionality by adding one extra step – redirecting the output to a text file, from where you can easily cut and paste. Note that using the “start” command will allow you to use whichever program is set as the default for .txt files.
The cmd version is:
dir *20120806*.bak /b > tmp.txt start tmp.txt
Powershell allows you to keep the entire contents on a single line:
dir *20120806*.bak -name > tmp.txt; start tmp.txt
Remember to go back and delete tmp.txt.
It’s a simple trick, but one that can be incredibly useful, and well worth a place in your toolbox.