SQL Tips – Back up and restore

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