• Hi Guys:

    I need some help.I want to generate restore statement dynamically.I will enter the db name and it should generate restore script with move option to d:\sql .How can i do that . .mdf .ldf files can vary (more then 2).

    Any help will be highly appreciated


    Wednesday, April 24, 2013 6:55 PM


All replies

  • Hi,

    Where exactly you want this to perform .. in an application or on database query editor ?

    If on database side, create a stored procedure and define a variable to store your restore statement. then pass database name as parameter to the procedure.

    For better solution..please explain some more

    Please Mark as Answer if my post solved your problem or Vote As Helpful if this helps. Blogs:

    Wednesday, April 24, 2013 8:04 PM
  • i will run it in sql management studio.Input parameter will be dbname
    Wednesday, April 24, 2013 8:54 PM
  • Hi Guys:

    When i run (exec @sqlstring1)this statement i am getting error:- Msg 203, Level 16, State 2, Line 128 The name ' is not a valid identifier.

        DECLARE @sqlstring1         nvarchar(max)
        DECLARE @sqlstring2         nvarchar(max)
        DECLARE @sqlstring3         nvarchar(max)
        DECLARE @staging_restore_path varchar(50)
        DECLARE @database_name      varchar(50)
        DECLARE @server_name        varchar(100)
        DECLARE @environment        varchar(100)
        DECLARE @status                char(1)
        SET @staging_restore_path='D:\MSSQL\'
        set @server_name = 'aaa\123
        set @database_name = 'us_qwe'    

                SET @sqlstring1=N'
                    CREATE TABLE #Tmp
                    (ID INT IDENTITY(1,1),
                    backupSetID INT,
                    Cmd VARCHAR(8000) )
                    DECLARE @f_bkp_set_id INT  
                    SELECT @f_bkp_set_id = MAX(backup_set_id)  
                    FROM ['+@server_name+'].msdb.dbo.backupset  
                    WHERE database_name = '''+@database_name+''' AND type = ''D''
                    DECLARE @Cmd NVARCHAR(MAX)
                    DECLARE @Phys VARCHAR(1000)
                    DECLARE @fsn int
                    SET @Cmd=''EXECUTE master..sqlbackup N''''-SQL "RESTORE DATABASE '+@database_name+' FROM ''
                    declare cur cursor for
                    SELECT DISTINCT mf.physical_device_name,mf.family_sequence_number
                    FROM ['+@server_name+'].msdb.dbo.backupset b,  
                     ['+@server_name+'].msdb.dbo.backupmediafamily mf,
                     ['+@server_name+'].msdb.dbo.backupfile bf
                    WHERE b.media_set_id = mf.media_set_id  
                     AND b.database_name = '''+@database_name+'''  
                     AND b.backup_set_id = @f_bkp_set_id
                     AND b.backup_set_id = bf.backup_set_id
                            open cur
                            fetch cur into @Phys,@fsn
                            while @@fetch_status=0
                                if @fsn=1
                                    SET @Cmd=@Cmd + ''DISK = '''''''''' + @Phys + ''''''''''''
                                    SET @Cmd=@Cmd + '',DISK = '''''''''' + @Phys + ''''''''''''
                                fetch cur into @Phys,@fsn
                            close cur
                            deallocate cur

    Thursday, April 25, 2013 1:29 AM
  • Hi Zapped1,

    Please set break point in the codes and debug them, in this way, we can find the error. Additionally, you can refer to the following document about creating a stored procedure to backup database.

    How to: Debug T-SQL Using the SQL Server Management Studio

    Allen Li
    TechNet Community Support

    Thursday, April 25, 2013 7:09 AM
  • I recommend to print out the sql string and check in SSMS.
    Thursday, April 25, 2013 7:15 AM