TSQL to restore Database

Question

Post reply

TSQL to restore Database

Ayaz Dadla

SSC Enthusiast

Points: 127
More actions
December 14, 2004 at 3:08 pm

#66651

I have a .bak file and I need to restore it using TSQL. I do not know what the logical files names are for the Data and Log files. I know I could use: restore FILELISTONLY FROM DISK = 'c:\test\test.bak' to see the logical names, but I need to know the logical names using TSQL so that I could use them in the restore TSQL which is :
restore database TEST5 from disk = 'c:\test\test.bak'
WITH MOVE 'logical DataFile name' TO 'c:\testdb5.mdf',
MOVE 'logical LogFile name' TO 'c:\testdb5.ldf'
The restore statement does require the logical names, so I need to programatically pass it to the restore statement.
How can this be accomplished?
Any help will be appreciated. Thanks in advance.
Thanks for the replies but they still do not fix my problem. The restore statement above works perfectly for me as long as I know the logical names... How do I put the logical names for data and log files into variables, which I can use in the above restore statement.

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

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

jxflagg Hall of Fame Points: 3817 More actions · Answer 1

jxflagg

Hall of Fame

Points: 3817

December 14, 2004 at 3:15 pm

#533859

Use restore filelistonly

DavidMcAfee SSC Eights! Points: 887 More actions · Answer 2

You need to kick off any users first:

DECLARE @dbname as VARCHAR(50)

SET @dbname = 'YourDataBaseNameHere'

DECLARE @strSQL varchar(255)

PRINT 'Killing Users'

PRINT '-----------------'

CREATE table #tmpUsers(

spid int,

eid int,

status varchar(30),

loginname varchar(50),

hostname varchar(50),

blk int,

dbname varchar(50),

cmd varchar(30))

INSERT INTO #tmpUsers EXEC SP_WHO

DECLARE LoginCursor CURSOR

READ_ONLY

FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname

DECLARE @spid varchar(10)

DECLARE @dbname2 varchar(40)

OPEN LoginCursor

FETCH NEXT FROM LoginCursor INTO @spid, @dbname2

WHILE (@@fetch_status -1)

BEGIN

IF (@@fetch_status -2)

BEGIN

PRINT 'Killing ' + @spid

SET @strSQL = 'KILL ' + @spid

EXEC (@strSQL)

END

FETCH NEXT FROM LoginCursor INTO @spid, @dbname2

END

CLOSE LoginCursor

DEALLOCATE LoginCursor

DROP table #tmpUsers

GO

Then, to restore:

RESTORE DATABASE Machines

FROM DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YourDatabase.Bak'

DavidMcAfee SSC Eights! Points: 887 More actions · Answer 3

Sorry, I was pulled away and thought I was answering the question when I returned

Can you not set a variable for the path and location and restore?

Yup, a quick test shows that the following works:

DECLARE @Path AS VARCHAR(255)

SET @Path = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MachinesLive.Bak'

RESTORE DATABASE Machines

FROM DISK = @path

Ayaz Dadla SSC Enthusiast Points: 127 More actions · Answer 4

Thanks for the replies but they still do not fix my problem. The restore statement above works perfectly for me as long as I know the logical names... How do I put the logical names of the data and log files into variables, which I can use in the above restore statement. use this example for your replies... you have , c:\myDB.bak. I need a TSQL statement that will restore this DB to c:\DBs. Please note that the logical file names are unknown and we must use a 'with move'. Thanks again...

Paul Smith-158386 SSC Enthusiast Points: 186 More actions · Answer 5

The following code will get you the Logical name of the Database. I believe you have sufficient info in earlier replies to use this to construct the recover query.

Declare @Script NVARCHAR(500)

Declare @LogicalDBName varchar(50)

set @Script = 'RESTORE FILELISTONLY FROM DISK = ''C:\myDB.BAK'''

create table #TBL (Logical varchar(50), Physical varchar (100), Type Char(1), FGroup varchar(20), FSize BIGINT, MaxSz BIGINT)

insert into #TBL exec sp_executesql @Script

select @LogicalDBName = Logical from #TBL Where Type = 'D'

drop table #TBL

print @LogicalDBName

hawg SSCertifiable Points: 6044 More actions · Answer 6

Assuming you only have one data file and one log file, try the following:

CREATE PROC RestoreDBs(

@db_name varchar(128),

@backup_device varchar(500),

@new_device varchar(500))

AS

DECLARE @sql varchar(100),

@data_logical_filename varchar(500),

@log_logical_filename varchar(500),

@new_data_device varchar(500),

@new_log_device varchar(500)

SET @new_data_device = @new_device + '.mdf'

SET @new_log_device = @new_device + '.ldf'

CREATE TABLE #filelist(

logicalname nvarchar(128),

physicalname nvarchar(260),

type char(1),

filegroupname nvarchar(128),

size numeric(20,0),

maxsize numeric(20,0))

SET @sql = 'RESTORE FILELISTONLY FROM DISK = ''' + @backup_device + ''''

INSERT INTO #filelist

EXEC(@sql)

SELECT @data_logical_filename = logicalname

FROM #filelist

WHERE type = 'D'

SELECT @log_logical_filename = logicalname

FROM #filelist

WHERE type = 'L'

RESTORE DATABASE @db_name

FROM DISK = @backup_device

WITH MOVE @data_logical_filename TO @new_data_device,

MOVE @log_logical_filename TO @new_log_device

GO

When you call this (using your first restore example) call it like the following:

EXEC RestoreDBs 'Test5', 'c:\test\test.bak', 'c:\testdb5'

I haven't tested this yet but it is taken (mostly) from a process I run every night to script out database restores for all of my databases.

Good Luck!

hawg

----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

Ayaz Dadla SSC Enthusiast Points: 127 More actions · Answer 7

Thanks guys, now that worked for me. I appreciate all the help.