Script required to restore Database

Question

Script required to restore Database

SQLAli

Ten Centuries

Points: 1079
More actions
November 2, 2013 at 4:25 am

#395765

Hi,
I need to restore a database. My database consists of 7345 data files. I got a request to restore the database with another name and files should be in different volume. Could any one help me by giving the procedure to restore?
I know if it is small database i would have restore by using the below script but i struck in restoring 7345 files.
Restore database DB_Name
from disk='path\Backupfile'
with
MOVE N'Logical_name' TO N'D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\AppData\physical_file.mdf',
Thanks,
I’m nobody but still I’m somebody to someone………….

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

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

mrdenny SSCrazy Points: 2898 More actions · Answer 1

I've got good news and bad news. The good news is that you already know the command to use. The bad news is that you have a lot of typing in your future.

The better question is, why on earth do you have so many database files?

SQLAli Ten Centuries Points: 1079 More actions · Answer 2

Hi Denny,

Etl job pushes daily 1 file into the database. Acutally i have resolved by using below solution, but i am looking for valid solution.

1) Genereate the restore script

2) Copy it in MS Word and replace new path with old.

Thanks,
I’m nobody but still I’m somebody to someone………….

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

That's probably what I would have done. Why's it not a valid solution?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLAli Ten Centuries Points: 1079 More actions · Answer 4

Shaw,

I was thinking about a script, to resolve this rather than editing in MSWORD.

Thanks,
I’m nobody but still I’m somebody to someone………….

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

I suppose you could insert the results of RESTORE FILELISTONLY into a table run an update to change the file paths, then write a script to generate a RESTORE from that, but to be honest, a quick find/replace in any text or document editor will likely be faster.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ash SSC Eights! Points: 947 More actions · Answer 6

SQLAli,

Just in case, you do not want to use MS Office, you can create a case statement as below and script out the move templates for creating restore script. You can tweak the same to use on lower version of SQL Server.

declare @data varchar(200)

declare@log varchar(200)

--Enter Data Location.

set @data = 'D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\AppData'

--Enter Log Location.

set @log = 'D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\AppData'

select

db_name(database_id) as database_name,

name as logical_file_name,

case [file_id]

when 1

then ', MOVE '''+s.NAME+''' TO '''+@data+'\'+s.NAME+'.mdf'''

else

(case [type]

when 0

then ', MOVE '''+s.NAME+''' TO '''+@data+'\'+s.NAME+'.ndf'''

else ', MOVE '''+s.NAME+''' TO '''+@log+'\'+s.NAME+'.ldf'''

end)

end as HardCodedMoveTemplates

from sys.master_files s

where db_name(database_id) not in ('master','model','msdb','tempdb')

order by database_id asc

Hope this helps...

-Arshpreet