Sql-server – Restore database from script created by the “Generate and Publish Scripts” wizard

You could set up a simple job to backup the database to a single location and then restore it. It would look something like this

--First we back up the DB        
BACKUP DATABASE [ABC] TO  DISK = N'C:\temp\ABC.bak' WITH NOFORMAT, NOINIT,
        NAME = N'ABC-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
        GO
--Then we restore the DB
        RESTORE DATABASE [ABC_test] FROM  DISK = N'C:\temp\ABC.bak' WITH  FILE = 1,  MOVE N'ABC' TO N'C:\DBFiles\ABC_test.mdf',  
        MOVE N'ABC_log' TO N'C:\DBFiles\ABC_test_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 10
        GO

You might want to delete the file before you back it up. You could do this with powershell (my recommendation), xp_delete_file (undocumented SP), or xp_cmdshell (not sure if it is enabled on your system or not)

First you have to

• Create Login with create database permission. Note that the login cannot drop any databases.

  USE [master]
  GO
  
  CREATE LOGIN [Kin] WITH PASSWORD=N'somePassw0rd', DEFAULT_DATABASE=[master],
  DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
  GO
  use [master]
  GO
  -- Grant create any database, but not drop or alter any database
   GRANT CREATE ANY DATABASE TO [Kin]
  GO
  

• Map the login to the database that you want to have permission to alter or drop.

  -- for drop database, you just need db_owner
  USE [test_drop]
  GO
  CREATE USER [Kin] FOR LOGIN [Kin] 
   GO
  

• Add the user to the db_owner role.

    EXEC sp_addrolemember 'db_owner', 'Kin'
  

Now test it for create / alter and drop :

EXECUTE AS LOGIN = 'Kin'

create database test_drop


EXECUTE AS LOGIN = 'Kin'
use test_drop

create table test (fname char(1))


EXECUTE AS LOGIN = 'Kin'
select * from test

EXECUTE AS LOGIN = 'Kin'
alter database test_drop 
set offline

EXECUTE AS LOGIN = 'Kin'
alter database test_drop 
set online

EXECUTE AS LOGIN = 'Kin'
drop database [test_drop]

Try to drop some other db :

EXECUTE AS LOGIN = 'Kin'
alter database [test_kin] 
set online

Below is the error :

Msg 5011, Level 14, State 9, Line 2 User does not have permission to alter database 'test_kin', 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.