Post reply

TSQL to restore Database

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

     

  • December 14, 2004 at 3:15 pm

    #533859

    Use restore filelistonly

  • December 14, 2004 at 6:32 pm

    #533876

    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'

  • December 14, 2004 at 6:39 pm

    #533877

    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

  • December 14, 2004 at 6:49 pm

    #533878

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

     

     

  • December 15, 2004 at 2:20 am

    #533907

    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

     

  • December 15, 2004 at 7:44 am

    #533951

    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

    ----------------------------------------------------------

    01010011010100010100110000100000010100110110010101110010011101100110010101110010
    001000000101001001101111011000110110101101110011

  • December 15, 2004 at 5:04 pm

    #534065

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

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

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