0

Dynamically restore database in SQL server with multiple files. I have a requirement to restore database from multiple files as below.But how to write a query to get files which are available in that specific folder because no.of files today is 3 tomorrow it may raises to 4.

enter image description here

Paolo Mossini
  • 1,064
  • 2
  • 15
  • 23
Sudharshan
  • 1
  • 1
  • 2
  • [This](https://stackoverflow.com/a/15514168/12354911) answer may help for what you want. – Eldar Jul 13 '20 at 06:29

1 Answers1

0

There is a system defined extended stored procedure called xp_dirtree. You won't find any microsoft documentation on this - it is unsupported. It's easy, it's built in, but use it at your own risk. It could theoretically disappear when you upgrade your SQL install.

Alternatively, you can use xp_cmdshell, but the general advice to disable this extended procedure in sys.configurations, because it is inherently dangerous.

You could write your own CLR stored procedure. Jonathan Kehayias has an excellent article on this.

You could write your own application or service to manage your backups.

Or you could buy one of the several products which manage this kind of thing. A recommendation on which one is not really on topic for stackoverflow.

allmhuran
  • 4,154
  • 1
  • 8
  • 27
  • Yeah I knew to get filenames from that folder.My question is how to automate these restoring processing.I need script to get dynamical disk statements based on the file count. – Sudharshan Jul 13 '20 at 06:52