Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
share|improve this question
add comment

2 Answers 2

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

share|improve this answer
    
Yes, was that wrong? –  ujjain May 25 '11 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 '11 at 7:04
    
@chronoz I know it sounds a bit confusing, but NORECOVERY indicates there may be more steps (logs, differentials) required. –  Stan the DBA May 27 '11 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 '11 at 0:06
add comment

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;
share|improve this answer
    
Thanks! I have edited my initial post! –  ujjain May 25 '11 at 21:00
    
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. –  Sankar Reddy May 25 '11 at 21:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.