December 22, 2008 at 7:24 am
hello all,
I have made a Standardization of sqlserver 2005 like this:
%SQL_BASE%\sqldata\MSSQL.x\
where x is the number of the instance.
Datafiles & logile are in the directory
I need to script many of administration tasks, and i already made script for backup database, backup log, and full restore of databases,etc....
Now, i need to make a Duplicate script which can restore databases from database backup of one instance to another. ( like from production platform to development one)
The problem is that datafiles directories are in the backup file and sqlserver will keep the source directory structure when it restore them. so i need to changed it.
I see only the "MOVE" option in the restore command, but to use it, i need to know each datafile and their directories on the source database. Nightmare !!!
Do you know another option to restore every datafiles and logfile in one directory choosen and keep only datafile filenames ?
Pierre
Oracle/SQLServer consultant
December 22, 2008 at 7:45 am
You can use RESTORE FILELISTONLY to get a list of the files in the database.
The Redneck DBA
December 22, 2008 at 7:48 am
Yes, you should be able to use restorefilelistonly as Jason recommended and collect the current information of where the files are located by querying sys.master_files.
Sounds like a fun project. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 22, 2008 at 8:08 am
thanks for yours answers.
It really sound hard but i'll do it.:w00t:
Is it not easier to get information from to connect to the source instance and query the master_files view rather than use "restore filelistonly" ? It seems harder to parse results from this command, doesn't it ?
Pierre
Oracle/SQLServer consultant
December 22, 2008 at 8:33 am
it should be possible to dump the RESTORE FILELISTONLY to a temp table and then build a restore command from the data in that temp table
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 22, 2008 at 8:41 am
You would have to use the sys.master_files to get the final destination information. The restorefilelistonly would only be used to pull the logicalname. If they were consistent between the source (where backup was generated) and the destination then you probably wouldn't have to retrieve information from restorefilelistonly.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 22, 2008 at 8:44 am
Oh, yea, if you have the DB attached somewhere you can just query out of the system tables.
I'm not sure that will make the script any easier to write or not, but might make it run a little faster.
The Redneck DBA
December 22, 2008 at 9:18 am
Ok, thanks all for your help. I'll connect to the source instance to query system views.
It is acceptable that the source instance must be started when administrators wants to duplicate databases.
I could be interesting that in future release, it will be possible to choose a restore directory easier...
Now, start to write the script.
thanks !!!!!!!
Oracle/SQLServer consultant
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply