Restore Database from folder

Question

Restore Database from folder

khushbu

SSCarpal Tunnel

Points: 4591
More actions
January 15, 2018 at 2:11 am

#325400

I want to restore databases residing in folder say "D:\Backups\". This folder contains many set of db backup.
I would want to do them automatically on my test server using "With MOVE" option where database's filename and path are obtained automatically.
Any one has such script?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Do you know the name of the database that each file is a backup of? If not, use RESTORE HEADERONLY to get the database name. You'll also need to use RESTORE FILELISTONLY to get the file names. Create temp tables to receive the result sets of those two commands. You can use that information to build your RESTORE DATABASE statements.

John

khushbu SSCarpal Tunnel Points: 4591 More actions · Answer 2

Yea I've used FILELISTONLY and HEADERONLY to get the information.
But I' m stuck at: The DB already exists on server and I have to place the data/log files at the same location as it existed.
Those location vary for each database. How do I get it at runtime?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

SELECT type_desc, name, physical_name FROM DBName.sys.database_files

Make sure you use the REPLACE option in your RESTORE DATABASE command if you're overwriting an existing database.

John

RandomStream Hall of Fame Points: 3735 More actions · Answer 4

If I were you, I would ask 'What is the best way to automate restore of multiple databases?'

You can be certain that my script would not fit your situation. As such, if I were to share with you, I'd have to take the time to explain how my script works, its prerequisites, and what you need to do to customize it to suit your needs. In the end you learn very little about methodology.

I see that you can retrieve the logical file names, great. In general, if you have, say, a dozen databases, create a SQL Server Agent job for each one of them using the RESTORE with REPLACE. That way, you can set the schedule and monitor each job.

But if you have a lot more, then yes, it might be worthwhile to come up with a script to extract logical file names and generate a job to restore each db. When you have it, please share in your blog. Thanks.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

khushbu - Monday, January 15, 2018 3:54 AM
Yea I've used FILELISTONLY and HEADERONLY to get the information.
But I' m stuck at: The DB already exists on server and I have to place the data/log files at the same location as it existed.
Those location vary for each database. How do I get it at runtime?

Are the backup files from the same databases that you are restoring, or is this a situation more like copying a similar database from another server? Also, do you have just full database backups or do you have transaction log backups you are dealing with also? There are a couple different ways to use a script to write the restore scripts you need depending on the answers to these questions.

khushbu SSCarpal Tunnel Points: 4591 More actions · Answer 6

Thanks for the valuable inputs.

I have the script already, dealingwith 50+ db over 500gb size.
Its a quarterly restore yet! Just re- writing to extract the physical file names and then restore with replace.

Thanks