Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script required to restore Database Expand / Collapse
Author
Message
Posted Saturday, November 2, 2013 4:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, October 11, 2014 12:31 AM
Points: 115, Visits: 239
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………….
Post #1510850
Posted Saturday, November 2, 2013 7:56 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:18 AM
Points: 77, Visits: 217
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?
Post #1510855
Posted Saturday, November 2, 2013 8:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, October 11, 2014 12:31 AM
Points: 115, Visits: 239
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………….
Post #1510856
Posted Saturday, November 2, 2013 12:16 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 40,193, Visits: 36,597
That's probably what I would have done. Why's it not a valid solution?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1510864
Posted Sunday, November 3, 2013 1:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, October 11, 2014 12:31 AM
Points: 115, Visits: 239
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………….
Post #1510878
Posted Sunday, November 3, 2013 4:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 40,193, Visits: 36,597
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 2008, MVP
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

Post #1510882
Posted Monday, November 4, 2013 5:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 131, Visits: 642
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
Post #1511061
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse