USE [dba]
GO

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

quote:

---

Originally posted by X002548

don't know..that's how I always do it

USE [dba]
GO

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

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

---

quote:

---

Originally posted by skybvi

I am gettting restoring errors(restore job failing) :-

Exclusive access could not be obtained beacuse the database is in use.

I ran :-

alter database dbname
set single_user
with rollback immediate

alter database dbname set multi_user

Then again tried restoring manually and then again the same error.


Regards,
Sushant
DBA
Virgin Islands(U.K)

---