I have a query regarding MSSQL DB restore --- hardware reconfiguration

[Former Member]

Dear Experts,

I have a query regarding MSSQL DB restore.

In our development system right now all DB files means Datafiles, Transaction Log Files

and TEMPDB file are in same drive (drive G:\)

Now we are going to hardware reconfiguration and as a result we need to do a SAP 
reinstallation and then DB restore. (As our client do not want any IP change or Production Data 
to be copied in Development system via system copy.)

1.  So I have a take a full back up.
2.  Performed a test restore and it was successful.

Now my question is if after hardware reconfiguration ,

we want to distribute the DB files in multiple drive that is all Datafiles in same drive

and Transaction Log Files, TEMPDB file are in different drive. In such scenario, whether

restore of the DB wil be successful using the previousl back-up.

Please answer and give kind advice.

Thanks in advance.

Regards,

Partha

[ken_halvorsen2 · Accepted solution]

Assuming that the Source and Target systems have the same OS & DB versions you should be fine.

Personally, if possible, I would just copy the data (data1, data2, data3) & translog files to the target system no matter which drive they fit onto, and attach them. I'm assuming that the source and target systems have the same <sid>, (target system being the new Dev system of course) if they are different <sid> there's a little bit more work.

[Former Member · Accepted solution]

Yes, the restore process will work for changing location of datafiles. If your DB files are huge i would suggest to keep each in a separate drive to avoid I/O problems

To move user database files you can also refer to:

SAP NOTE 363018 -File management for SQL Server

Thanks.

[Former Member]

Thank you all

[former_member524429]

Hi,

During the restore of MSMSQL Database, you can change the directory location of all database files.

On the screen "DB restore" switch to the tab "Options" and type in new file locations for each DB file. Files must not exist, but all folders in the path must be created beforehand manually.

Please ,refer the following SAP Notes to get more info.

Note 1420452 - FAQ: Restore and recovery with MS SQL Server (Point 13)

Note 193816 - Restore with SQL Server

Regards,

Bhavik G. Shroff