Problems backing up SQL Server 2005 DB

try following script for backup and restore, if you don't have enough space in C: drive, change drive letter.

BACKUP DATABASE AdventureWorks 
TO DISK = 'C:\AdventureWorks.BAK'
GO
 
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\AdventureWorks.BAK'
GO

Select allOpen in new window

more over, I would like you to have a look at following URL for backup and restore issue

https://www.experts-exchange.com/questions/23277887/Restore-Database-Using-SQL-Server-Script.html

I attempted the Backup and Restore scripts.
The Backup worked.
For the Restore I got

Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "ResourceManager" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

have you tried to back up and restore on the same server?

have a look at following URL and try to fix that way.

http://blog.sqlauthority.com/2007/04/30/sql-server-fix-error-msg-3159-level-16-state-1-line-1-msg-3013-level-16-state-1-line-1/

if it doesn't work even after following the steps in above URL. Please try to run dbcc checkdb command to check any error.

try this code snippet I got from above link.

ALTER DATABASE AdventureWorks
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
RESTORE DATABASE AdventureWorks
FROM DISK = C\:BackupAdventureworks.bak
WITH MOVE AdventureWorks_Data TO C:\Data\datafile.mdf,
MOVE AdventureWorks_Log TO C:\Data\logfile.ldf,
REPLACE

Select allOpen in new window

I ran
ALTER DATABASE TestDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
RESTORE DATABASE TestDB
FROM DISK = 'C:\TestDB.bak'
WITH MOVE 'TestDB_Data' TO 'C:\Data\TestDBdatafile.mdf',
MOVE 'TestDB_Log' TO 'C:\Data\TestDBlogfile.ldf',
REPLACE
and I got the error message;

Msg 3234, Level 16, State 2, Line 7
Logical file 'TestDB_Data' is not part of database 'TestDB'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.

check the log file name and data file name as you have used wrong data file name.

i guess your datafile name is TestDBdatafile instead of TestDB_Data
and your log file name is TestDBlogfile instead of TestDB_Log

When I do my backup I type;
BACKUP DATABASE TestDB
TO DISK = 'C:\TestDB.BAK'
GO

This does not specify a datafile name or a log file name. When I look on the file system, these files do not exist.

I cannot run that select statement.
The databse is in single user mode and is currently being used.

When I type sp_who2 to find out what SPID to kill, I cannot identify one.

I reckon the previous script I run has caused this. Thankfully the DB is just a dummy one, but for now I cannot even delete it to start again.

all the commands I have provided above are not harmful, anyway, can you please try to run dbcc checkdb command?

I get this message;
Msg 924, Level 14, State 1, Line 6
Database 'TestDB' is already open and can only have one user at a time.

does your DB shows "suspect" status in SSMS?

A google search revealed that to answer the question you ask, I have to run
SELECT DATABASEPROPERTYEX('TestDB', 'Status')
DatabaseStatus_DATABASEPROPERTYEX
GO
SELECT state_desc DatabaseStatus_sysDatabase
FROM sys.databases
WHERE name = 'TestDB'
GO

The response I got was "ONLINE"

Well, in that case there shouldn't be any problem, are you able to access any tables in your DB? try doing restart MS SQL service from control panel->administrative tool->Services

Also observer, after restart your TestDB is still 'Online' or become 'Suspect'

I restarted MS SQL and was able access the tables on the DB.
I run
select * from sys.database_files

ALTER DATABASE TestDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
RESTORE DATABASE TestDB
FROM DISK = 'C:\TestDB.bak'
WITH MOVE 'TestDB' TO 'C:\Data\TestDBdatafile.mdf',
MOVE 'TestDB_log' TO 'C:\Data\TestDBlogfile.ldf',
REPLACE

The select statement gave me the names for the restore, and the restore worked this time.

Definite progress and thank you for persevering with me.

All that is left is to change the DB back to multiuser. Please let me know how to do that.

Is the solution to the Restore issue to run a script that performs all the operations that we have been using? It seems odd to have to do it this way.

you can change your db to multi user by following script.

ALTER DATABASE [TestDB] SET MULTI_USER WITH NO_WAIT

Select allOpen in new window

Thank you for that.
So for me to run a restore I have to run a script that does the following;

ALTER DATABASE TestDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
RESTORE DATABASE TestDB
FROM DISK = 'C:\TestDB.bak'
WITH MOVE 'TestDB' TO 'C:\Data\TestDBdatafile.mdf',
MOVE 'TestDB_log' TO 'C:\Data\TestDBlogfile.ldf',
REPLACE

ALTER DATABASE [TestDB] SET MULTI_USER WITH NO_WAIT

Now that is certainly a solution. I certainly appreciate your help as I would never have come up with this solution.

But I am surprised I have to do this. I there no other way to configure the DB so that I can simply use the right-click commands in SSMS?

I am script guy so always like to do stuff by that only but there is a way to do all these things by SSMS but that is something I can't explain here in one post, I may post one article at my blog at http://sqlhub.com 

An unexpected solution, appreciate you finding it.