Script required to restore Database

  • 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………….

  • 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?

  • 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………….

  • 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
  • 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………….

  • 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
  • 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

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

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