invisible database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

invisible database

Author

Topic

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2012-08-15 : 16:25:44

Hi,

I was trying a database restore and it threw out Page errors.

I had to restart the restore. But the database was in “Restoring..” state. I right clicked and deleted it on Mgmt Studio and it was gone.
It doesn’t show in the SQL management studio.
I thought it was ok and I tried the restore


restore database DBNAME 
from disk='\\landbbackup1\e$\MonthEndBackups\DBNAME_20120810.BAK' 
with replace, recovery, stats=5, maxtransfersize=1048576

I received this:


Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Then try dropping using the command line.


drop database DBNAME

Got this:


Msg 3702, Level 16, State 4, Line 1
Cannot drop database "DBNAME" because it is currently in use.

When I try to list all the databases in the insance, it doesn’t show this database at all


SELECT NAME FROM sys.sysdatabases
where name not in ('master', 'tempdb', 'model','msdb')

So, I tried to take it offline:


USE master
ALTER DATABASE [DBNAME] SET OFFLINE WITH ROLLBACK IMMEDIATE

No avail.


Msg 5011, Level 14, State 6, Line 2
User does not have permission to alter database 'DBNAME', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.

How do I carry on the restore?

Thanks a lot for your time..

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-16 : 04:21:28

Try this.
Create a small database. Back it up. restore that backup over the rogue database. Drop the database.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

prett
Posting Yak Master

212 Posts

Posted - 2012-08-16 : 04:23:31

This is a permissions issue. First ensure that the service account has access to source share for the backup file.
Also check this forum post: http://www.sqlservercentral.com/Forums/Topic771702-24-1.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources