alter DB before redgate restore

marrow

Dear all,

I don't know why I can do only this command alone without any error message:

alter database <dbname> set single_user with rollback immediate
drop database <dbname>

But when I add the redgate restore command after that, it should be message like:

user account do not have permission to alter database, user database can't be set in single user mode.

At the end, the DB can be store succesfully, any idea why so strange?

DBA100

ChrisAVWood

I think a lot here depends on the permission that the SQL login or Windows Account running the commands has.

I had to develop T-SQL scripts to backup and restore DB's that analysts use from SSMS when logged in using SQL logins. To do a differential restore I needed to make the SQL login have dbcreator server permission.

You also need to be aware of Red Gate restores work best when the database definition exists in master. In that case I would not drop the DB but just restore it with the REPLACE option.

HTH

Chris

Eddie D

Hi Marrow & Chris

When using the SQL Backup Extended Stored Procedure to perform a restore (or a backup) two connections are required to the restoring. So when you place the database to be restored into single user mode, you prevent SQL Backup from making the two connections it requires. Hence the error that Marrow is experiencing.

If you wish to disconnect users that are connected to database prior to the restore occurring, make use of the DISCONNECT_EXISTING keyword if creating a restore script or enable the option in step 2 of the restore wizard "Kill any existing connections to the database".

An example restore script using the DISCONNECT_EXISTING keyword is as follows:

--restore AdventureWorks database
EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [AdventureWorks] FROM DISK = ''D:\Backup\AdventureWorks\FULL_EDDIE_AdventureWorks_20100401_140715.sqb'' WITH RECOVERY, DISCONNECT_EXISTING, REPLACE"'

Another alternative if you use to place the database into single user mode prior to the restore, is to use the SQL Backup Command Line to perform the restore, which will only make a single connection to the database. Below is example syntax for the command line:

SQLBackupC.exe -SQL "RESTORE DATABASE [AdventureWorks] FROM DISK = ''D:\Backup\AdventureWorks\FULL_EDDIE_AdventureWorks_20100401_140715.sqb'' WITH RECOVERY, REPLACE"

The fact that when using the extended stored procedure requires two connections to the database causes a problem when you need to restore the master database, as the master database is normally placed into single user mode. Therefore when restoring the master database you will need to use the SQL Backup Command line to perform the restore. Further advice on restoring the master using SQL Backup can be found in this knowledge base article, using this LINK.

I hope the above helps.

Many Thanks
Eddie