-
Title
How to restore full backup with move, standby or loading via script. -
Description
How to restore full backup with move, standby or loading via script.
-
Cause
Database requires a Non-operational (Loading) or Standby (Read Only) status after restore.
-
Resolution
SQL2005
exec master.dbo.xp_restore_database @database = 'Northwind', @filename = 'D:\BACKUP\NW200804251418LiteSpeed_Full.BKP', @filenumber = 1, @with = 'STANDBY = ''D:\undo\undo.txt''', @with = 'NOUNLOAD', @with = 'STATS = 10', @with = 'REPLACE', @with = 'MOVE ''Northwind'' TO ''C:\Program Files\Microsoft SQL Server\MSSQL\data\Northwind_Data.mdf''', @with = 'MOVE ''Northwind_log'' TO ''C:\Program Files\Microsoft SQL Server\MSSQL\DATA\Northwind_Log.ldf'''
SQL2000
exec master.dbo.xp_restore_database @database = 'Northwind', @filename = 'C:\Backup\Northwind200805270929LiteSpeed_Full.BKP', @filenumber = 1, @with = 'STANDBY = ''C:\Backup\undo\undo.txt''', @with = 'NOUNLOAD', @with = 'STATS = 10', @with = 'REPLACE'The parameter @with = N'NORECOVERY' will put the database into LOADING mode, also able to restore additional Differential or T-Log files. (Undo file is not required).
exec master.dbo.xp_restore_database @database = 'Northwind', @filename = 'C:\Backup\Northwind200805270929LiteSpeed_Full.BKP', @filenumber = 1, @with = N'NORECOVERY' @with = 'NOUNLOAD', @with = 'STATS = 10', @with = 'REPLACE' -
Additional Information
Note: If free disk space is exhausted on the drive containing the specified undo file name, theLiteSpeedrestore operation stops.
Also, see LiteSpeed Help file for additonal examples.