Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    47

    Unanswered: how to make a sql script that will restore by backup database ?

    i want to make a sql script that will restore by backup database file in ms sql server when executed
    how to do that ?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    SELECT @@SERVERNAME
    
    -- ***
    -- *** Make Sure the Server Name is 
    -- ***
    
    
    -- Find the Database file to Restore.  Make sure it's on the Server
    EXEC master..xp_cmdshell 'dir  \\servername\instance\path\*.*'
    
    
    -- Look at the internal file information
    RESTORE FILELISTONLY 
     	FROM DISK = N'\\servername\instance\path\database.BAK' 	
    GO
    
    -- If the DB Exists, Kick everyone out
    
    ALTER DATABASE [database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    
    -- RESTORE The database...this will overlay an existing one, OR Create a new one if not in existence
    -- Get the Logical file names from filelistonly
    
    RESTORE DATABASE [database]
    FROM DISK = N'\\servername\instance\path\database.BAK' 	
        WITH MOVE 'logical data file'	TO '\\servername\instance\datafilepath\database.MDF'
            ,MOVE 'logical log file'	TO '\\servername\instance\datafilepath\database_log.LDF'
    	, REPLACE
    GO
    
    ALTER DATABASE [database] SET READ_WRITE
    ALTER DATABASE [database] SET MULTI_USER
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2011
    Posts
    47
    please explain me where i have asked you couple of question
    thanks for the script buddy.

    Code:
    SELECT @@SERVERNAME //here i should write my database name or server name ?
    
    
    -- ***
    -- *** Make Sure the Server Name is 
    -- ***
    
    
    -- Find the Database file to Restore.  Make sure it's on the Server
    EXEC master..xp_cmdshell 'dir  \\servername\instance\path\*.*'  //what is happening here 
    
    
    -- Look at the internal file information
    RESTORE FILELISTONLY 
     	FROM DISK = N'\\servername\instance\path\database.BAK' 	   
    GO
    
    -- If the DB Exists, Kick everyone out
    
    ALTER DATABASE [database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    
    -- RESTORE The database...this will overlay an existing one, OR Create a new one if not in existence
    -- Get the Logical file names from filelistonly
    
    RESTORE DATABASE [database]
    FROM DISK = N'\\servername\instance\path\database.BAK' 	
        WITH MOVE 'logical data file'	TO '\\servername\instance\datafilepath\database.MDF'
            ,MOVE 'logical log file'	TO '\\servername\instance\datafilepath\database_log.LDF' 
    // i didnt find any thing like this , i mean where this .ldf file is stored in ms sql folder 
    	, REPLACE
    GO
    
    ALTER DATABASE [database] SET READ_WRITE
    ALTER DATABASE [database] SET MULTI_USER
    GO

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You shouldn't have put the questions in the code...I barely saw them

    //what is happening here

    SELECT @@SERVERNAME, if you execute it, just tells you the name of the server

    I do that so my staff makes sure they are on the right server before they continue..I actually made it a little more bullet proof with an IF Condition for the script

    //what is happening here

    That is an xp_cmdshell (DOS) command that locates the dump and makes sure it's on the server before they start tyhe restore. If it ain't on the server, get it there

    // i didnt find any thing like this , i mean where this .ldf file is stored in ms sql folder


    This is where YOU want to put the databases that will be created during the restore...they MUST (or should) be on the server

    That's it?

    Should be a walk in the park

    Look up RESTORE in Books online

    You might also want to look up VERIFYONLY
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And Geez..I thought I documented it well with my comments
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •