Restoring database from script

Question

I have created a small scrip to restore databases from backups. Once the script is run it displays

RESTORE DATABASE successfully processed 28818 pages in 1.568 seconds (143.584 MB/sec).

I have more code to alter the database, alter a few views and sp too but I am getting the following error; User does not have permission to alter database 'GreyGoo', the database does not exist, or the database is not in a state that allows access checks.

I have noticed too that I cannot see the database in the object explorer

this is what I use to restore the DB from a backup

1. if DB exists set to a single user
2. if DB exist drop database
3. Ran the below script

RESTORE DATABASE GreyGoo FROM DISK = 'C:\Bkp\GreyGoo_backup_2020_03_02_180002_5403592.bak'
WITH 
MOVE 'GreyGoo' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\GreyGoo.mdf',
MOVE 'GreyGoo_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\GreyGoo.ldf', 
REPLACE;

4. set DB to multi-users and other properties

So what is the problem and how can I solve it, I am currently testing my code on SQL Server 2008

Thanks

Answer 1

During your restore process, you will need to ensure your

1. login / permissions are correct.
2. if another previous database with same name is not there, if so delete it. Perhaps from a previous attempt
3. You can also turn off recovery option like below, recovery is default
4. RESTORE FILELISTONLY

---

Sample -- MSDN

USE master;  
GO  
-- First determine the number and names of the files in the backup.  
-- AdventureWorks2012_Backup is the name of the backup device.  
RESTORE FILELISTONLY  
   FROM AdventureWorks2012_Backup;  
-- Restore the files for MyAdvWorks.  
RESTORE DATABASE MyAdvWorks  
   FROM AdventureWorks2012_Backup  
   WITH RECOVERY,  
   MOVE 'AdventureWorks2012_Data' TO 'D:\MyData\MyAdvWorks_Data.mdf',   
   MOVE 'AdventureWorks2012_Log' TO 'F:\MyLog\MyAdvWorks_Log.ldf';  
GO