none
T-SQL

    Question

  • 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

    Thanks.

    Wednesday, April 24, 2013 6:55 PM

Answers

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: www.sqlserver2005forum.blogspot.com

    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.

        SET NOCOUNT ON
        
        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
                            begin
                                if @fsn=1
                                    SET @Cmd=@Cmd + ''DISK = '''''''''' + @Phys + ''''''''''''
                                else
                                    SET @Cmd=@Cmd + '',DISK = '''''''''' + @Phys + ''''''''''''
                                fetch cur into @Phys,@fsn
                            end
                            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
    http://msdn.microsoft.com/en-us/library/ms241871(v=vs.80).aspx

    http://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/


    Allen Li
    TechNet Community Support

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