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

navedjobs · #1 (**permalink**) 01-11-12, 14:19

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

Brett Kaiser · #2 (**permalink**) 01-11-12, 14:22

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

navedjobs · #3 (**permalink**) 01-12-12, 06:25

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

Brett Kaiser · #4 (**permalink**) 01-12-12, 11:20

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 Kaiser · #5 (**permalink**) 01-12-12, 11:21

And Geez..I thought I documented it well with my comments

Internet Services

Web Development

Digital Marketing

Consumer Tech