Here are some SQL bits that I user over and over again
If you try to manually restore the MS SQL server database via console app, it might say, ‘exclusive access could not be obtained’
This is probably there are some connections which have been left hanging or they are actually trying to do some stuff with the database.
I use this script to kill all un-wanted connections
use master declare @vcdbname varchar(50) Set @vcdbname = 'DatabaseName' set nocount on declare Users cursor for select spid from master..sysprocesses where db_name(dbid) = @vcdbname declare @spid int, @str varchar(255) open users fetch next from users into @spid while @@fetch_status <> -1 begin if @@fetch_status = 0 begin set @str = 'kill ' + convert(varchar, @spid) exec (@str) end fetch next from users into @spid end deallocate users
Just change the name to your own database name and run the script
Imagine you have to backup and restore 5-10 database on adhoc bases, going through console app is very pain full and takes lot of time
I normally use scripts and run them in a go which back them up and restore them in a flash
For backups
DECLARE @BackupLocation AS NVARCHAR(250); DECLARE @BackupDate AS NVARCHAR(8); DECLARE @BackUpName AS NVARCHAR(350); SET @BackupLocation = 'E:\SQLBackup\'; SET @BackUpDate = CONVERT(NVARCHAR(8), GETDATE(), 112) -- replace MyDatabase with your database name SET @BackUpName = @BackupLocation + 'MyDatabase_' + @BackUpDate + '.Bak'; USE master; BACKUP DATABASE MyDatabase TO DISK = @BackUpName;
To resotre simply use the following script
USE [master]; ALTER DATABASE MyDatabaseNew SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE MyDatabaseNew SET SINGLE_USER; RESTORE DATABASE MyDatabaseNew FROM DISK = 'E:\SQLBackup\<you-file-name>.Bak' WITH REPLACE; ALTER DATABASE MyDatabaseNew SET MULTI_USER; GO
I would normally create these scripts for each of the databases and when required just run them in SQL