November 2, 2013 at 4:25 am
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………….
November 2, 2013 at 7:56 am
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?
November 2, 2013 at 8:47 am
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………….
November 2, 2013 at 12:16 pm
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
November 3, 2013 at 1:43 am
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………….
November 3, 2013 at 4:01 am
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
November 4, 2013 at 5:15 am
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