Re: OT: SQL2K Restore Database from .bak file to another database

[Mark Murphy]

Try scripting this restore using the RESTORE DATABASE command...it's makes your life easier going forward.  Here is some sample code that drops all current sessions and restores the database.  For the filename problem, use the WITH MOVE clause to change the logical name to a new physical path name.

HTH
-Mark

/* Restore database script */
DECLARE @backUpFile VARCHAR(2000)
DECLARE @database VARCHAR(2000)
-- SET THIS PARAMETER POINT TO THE BACKUP FILE      
SET @backupFile = 'E:\blah.bak'
-- SET THIS PARAMETER TO THE DATABASE THAT IT WILL BE RESTORED
SET @database = 'foobar'

-- KILL RUNNING PROCESSES AND RESTORE THE DATABASE
DECLARE @currentprocess INT, @SQL VARCHAR(100)
DECLARE processcursor CURSOR FOR SELECT spid FROM sysprocesses WHERE dbid = (SELECT dbid FROM sysdatabases WHERE name = @database)
OPEN processcursor
FETCH NEXT FROM processcursor INTO @currentprocess
WHILE (@@FETCH_STATUS = 0)
BEGIN
Print ('Killing Process ' + CAST(@currentprocess AS VARCHAR) + '...')
SET @SQL = 'Kill ' + CAST(@currentprocess AS VARCHAR)
Exec (@SQL)
Print ('Killed Process ' + CAST(@currentprocess AS VARCHAR) + '.')
FETCH NEXT FROM processcursor INTO @currentprocess
END
CLOSE processcursor
DEALLOCATE processcursor

RESTORE DATABASE @database FROM DISK = @backUpFile WITH REPLACE

PRINT('STATUS: Restored ' + @database)

---

[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]

---