Complete Restore with new name and Location

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

High Availability (2005)

Complete Restore with new name and Location

Author

Topic

malikjaved
Starting Member

21 Posts

Posted - 2008-01-21 : 13:01:01

Hi guys

i m working on SQL SERVER 2005, actually i am looking for some procedure/funcation that i can use to restore the whole database with different location along with log/files/filegroups on different location in hard disk, acutally i can do this all by using server management Studio, but i am looking for prefinded coded that i can use any time ?

plz do help me

Thanks and looking forward.
-MALIK

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-21 : 13:13:41

Did you look through RESTORE DATABASE from Books Online?

This works in 2000:
(you would obviously have to declare and initialize the variable names)


restore database @newDB_Name
	from	disk = @BackupFileNameFullRename 
	with	move '<Data>' TO @MoveToMdf
		,move '<Log>' TO @MoveToLdf

'<Data>' and '<Log>' will be the names returned from:

restore filelistonly from disk = '<full_path.BAK>'

Be One with the Optimizer
TG

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-21 : 16:01:42

And use 'with replace' option.

malikjaved
Starting Member

21 Posts

Posted - 2008-01-24 : 04:09:47

Thanks TG,

i have already done this, acutally 1 have two type of database, few database has indexe and catalog files and few have only data and log files,

so is there any procedure or funcation that read how many files in the backup and then i can restore them on my specific location?

Thanks again.
-MALIK

Haywood
Posting Yak Master

221 Posts

Posted - 2008-01-24 : 10:44:02

quote:
Originally posted by malikjaved

so is there any procedure or funcation that read how many files in the backup

RESTORE LABELONLY FROM DISK = 'Foo.BAK'

Look for the FamilyCount column...

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-24 : 22:59:18

You mean 'restore filelistonly'?

Haywood
Posting Yak Master

221 Posts

Posted - 2008-01-25 : 09:53:58

LABELONLY will tell you how many backup files comprise the media set...not everyone backs up to just one file/device. FILELISTONLY will tell you how many files comprise the database.

Maybe I misunderstood the question...

malikjaved
Starting Member

21 Posts

Posted - 2008-01-28 : 06:25:24

In other words. you can say i want to create a new database on behalf of current database,

what i am now doing,i hv T-sql code that take backup and then restore it' as new database, it's working fine,
but i hv to change the data and log file location manually when i run the t-sql code on another server,

i have almost done it, can you tell me is there any way to find out the directory of the files of the database, i hv come to find out htt sys.files will give u actuall physical location of the file, but i just need the last directory only where the files are store, like
this
what sys.files table give me

C:\data\x.mdf
what i need
c:\data
is there any way ?

i hope i could able to covay my question what i m looking

-Thanks and looking forward.
-MALIK

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-28 : 20:45:04

You can get file path with 'restore filelistonly'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources