0

I have backed up a database at my old server and restored it at the new server. I am totally new to MSSQL 2005 Server Express, so it took me quite a while. I spent 30 minutes to find out back-ups are stored at the Server, not at the MSSQL 2005 Express Studio desktop.

Having succesfully restored, it still says "restoring..." in Express Studio after 2 hours. The database size is only 4,8 MB so I figured it should be long done by now.

I ran the restore command with "no recovery". The SQL command given by Sankar's gives the following output:

TEID-UGNCHQ\SQLEXP_VIM;NetPerfMon;BACKUP DATABASE [NetPerfMon] TO  DISK = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\NetPerfMon.bak' WITH  RETAINDAYS = 1, NOFORMAT, INIT,  NAME = N'NetPerfMon-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;2011-05-25 18:16:04.527;1;TEID-UGNCHQ;Administrator;TEID-UGNCHQ;Microsoft SQL Server Management Studio Express;TEID-UGNCHQ\Administrator
TEID-UGNCHQ\SQLEXP_VIM;VeeamBackup;RESTORE DATABASE [VeeamBackup] FROM  DISK = N'C:\Documents and Settings\Administrator\My Documents\Downloads\VeeamBackup.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10;2011-05-25 18:47:32.843;1;TEID-UGNCHQ;Administrator;TEID-UGNCHQ;Microsoft SQL Server Management Studio Express;TEID-UGNCHQ\Administrator
TEID-UGNCHQ\SQLEXP_VIM;VeeamBackup;RESTORE DATABASE [VeeamBackup] FROM  DISK = N'C:\Documents and Settings\Administrator\My Documents\Downloads\VeeamBackup.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10;2011-05-25 18:47:53.453;1;TEID-UGNCHQ;Administrator;TEID-UGNCHQ;Microsoft SQL Server Management Studio Express;TEID-UGNCHQ\Administrator
TEID-UGNCHQ\SQLEXP_VIM;VeeamBackup;RESTORE DATABASE [VeeamBackup] FROM  DISK = N'C:\Documents and Settings\Administrator\My Documents\Downloads\VeeamBackup.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10;2011-05-25 18:48:14.627;1;TEID-UGNCHQ;Administrator;TEID-UGNCHQ;Microsoft SQL Server Management Studio Express;TEID-UGNCHQ\Administrator
TEID-UGNCHQ\SQLEXP_VIM;VeeamBackup;RESTORE DATABASE [VeeamBackup] FROM  DISK = N'C:\Documents and Settings\Administrator\My Documents\Downloads\VeeamBackup.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10;2011-05-25 19:01:19.970;1;TEID-UGNCHQ;Administrator;TEID-UGNCHQ;Microsoft SQL Server Management Studio Express;TEID-UGNCHQ\Administrator
TEID-UGNCHQ\SQLEXP_VIM;VeeamBackup;RESTORE DATABASE [VeeamBackup] FROM  DISK = N'C:\Documents and Settings\Administrator\My Documents\Downloads\VeeamBackup.bak' WITH  FILE = 1,  MOVE N'VeeamBackup' TO N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\VeeamBackup.mdf',  MOVE N'VeeamBackup_log' TO N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\VeeamBackup_log.LDF',  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10;2011-05-25 19:09:58.370;1;TEID-UGNCHQ;Administrator;TEID-UGNCHQ;Microsoft SQL Server Management Studio Express;TEID-UGNCHQ\Administrator

2 Answers 2

1

When you restored the database did you check the NORECOVERY checkbox (or use the NORECOVERY keyword)?

4
  • Yes, was that wrong?
    – ujjain
    May 25, 2011 at 21:23
  • The final step of the restoration process is to run the recovery step. If this isn't done, the database will stay in the "Restoring..." state, expecting to get a transaction log backup applied to it. You can run "RESTORE DATABASE VeeamBackup WITH RECOVERY" to only run the recovery step.
    – Jim McLeod
    May 27, 2011 at 7:04
  • @chronoz I know it sounds a bit confusing, but NORECOVERY indicates there may be more steps (logs, differentials) required. May 27, 2011 at 20:20
  • The NORECOVERY tells the SQL Server that the database should not run the recovery process so that the more logs can be restored (or a differential). The recovery process brings the database online, stamps a new LSN (Log Sequence Number) and puts the database into read/write mode. Until recovery is run the database is left in the restoring state.
    – mrdenny
    Jun 15, 2011 at 0:06
0

If you are NOT sure how you ran the RESTORE command you used earlier, then you can look that up using the default trace using the below query. HTH.

DECLARE @filename VARCHAR(255)
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\',REVERSE(path))+1) + '\Log.trc' 
FROM sys.traces  
WHERE is_default = 1; 

--Check all the databases that are backed up and restored and their success/failure state.
SELECT
     gt.ServerName
     , gt.DatabaseName
     , gt.TextData
     , gt.StartTime
     , gt.Success
     , gt.HostName
     , gt.NTUserName
    , gt.NTDomainName
     , gt.ApplicationName
     , gt.LoginName
FROM [fn_trace_gettable](@filename, DEFAULT) gt
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id
LEFT OUTER JOIN sys.databases d ON gt.DatabaseName = d.name
WHERE EventClass = 115 --'Audit Backup/Restore Event'
ORDER BY StartTime;
1
  • That's the problem @MrDenny mentioned. Since teh db is only 4, 8 MB drop them (In recovery databases) and re-create without using the NORECOVERY keyword this time. May 25, 2011 at 21:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .