Compendio de scripts para DBA MS-SQL

CREATE DATABASE YourDatabase;

GO

RESTORE BACKUP

USE master

GO

RESTORE DATABASE YourDatabase;

FROM DISK = ‘F:\\backup.bak’

WITH FILE = 1,

MOVE ‘fichero_db’ TO ‘D:\\db.mdf’,

MOVE ‘fichero_log’ TO ‘E:\\log.ldf’,

NOUNLOAD, STATS = 5

GO

BORRAR BASE DE DATOS

USE master

GO

ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

USE master

GO

DROP DATABASE YourDatabase

GO

RENOMBRAR BASE DE DATOS

USE master;

GO

ALTER DATABASE MyTestDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

ALTER DATABASE MyTestDatabase MODIFY NAME = MyTestDatabaseCopy;

GO

ALTER DATABASE MyTestDatabaseCopy SET MULTI_USER;

GO

---

VER PROCESOS CORRIENDO DE UNA DB

SELECT sd.name, sp.spid, sp.login_time, sp.loginame from sysprocesses sp inner join sysdatabases sd on sp.dbid = sp.dbid

where sd.name = ‘MyDB’

Referencias:

https://myadventuresincoding.wordpress.com/2014/03/06/sql-server-alter-database-in-single-user-mode-to-multi-user-mode

https://stackoverflow.com/questions/7197574/script-to-kill-all-connections-to-a-database-more-than-restricted-user-rollback

---

MATAR PROCESOS EN EJECUCIÓN (de una base de datos cuando no puede borrarse la db, ver script anterior)

Opción 1:

Right click on database engine, click on activity monitor and see which process is having connection.

Kill that particular user and execute the query Immediately.

Opción 2 (Recomendada):

For MS SQL Server 2012 and above:

USE master;

DECLARE @kill varchar(8000) = ”;

SELECT @kill = @kill + ‘kill ‘ + CONVERT(varchar(5), session_id) + ‘;’

FROM sys.dm_exec_sessions

WHERE database_id = db_id(‘yourDbName’)

EXEC(@kill);

Ponemos Offline la db para realizar las operaciones pertinentes:

USE master

ALTER DATABASE <yourDbName>

SET OFFLINE WITH ROLLBACK IMMEDIATE

For MS SQL Server 2000, 2005, 2008:

USE master;

DECLARE @kill varchar(8000); SET @kill = ‘ ‘;

SELECT @kill = @kill + ‘kill ‘ + CONVERT(varchar(5), spid) + ‘;’

FROM master.sysprocesses

WHERE dbid = db_id(‘yourDbName’)

EXEC(@kill);

---

Referencias:

https://stackoverflow.com/questions/7197574/script-to-kill-all-connections-to-a-database-more-than-restricted-user-rollback

---

TRUNCAR ARCHIVO LOG

SELECT * FROM sys.database_files;

DBCC SHRINKFILE(‘Dbname_Log’);

ALTER DATABASE Dbname;

SET RECOVERY SIMPLE;

GO

— Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (‘Dbname_Log’, 1);

GO

— Reset the database recovery model.

ALTER DATABASE Dbname

SET RECOVERY FULL;

GO

---

Referencias:

https://docs.microsoft.com/es-es/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver15

---

CAMBIAR UBICACION DE ARCHIVOS DE DATOS LOG Y DATOS

ALTER DATABASE NombreBaseDeDatos

MODIFY FILE (NAME = NombreDeDatos, FILENAME = ‘E:\ew_location\\Data.mdf’);

GO

ALTER DATABASE NombreBaseDeDatos

MODIFY FILE ( NAME = NombreDeLog, FILENAME = ‘E:\ew_location\\Log.ldf’);

GO ALTER DATABASE NombreBaseDeDatos SET OFFLINE;

GO

Ahora movemos el archivo físicamente cortando y pegando. Y volvemos a poner online la db.

ALTER DATABASE NombreBaseDeDatos SET ONLINE;

GO

---

BORRAR USUARIO DE BASE DE DATOS

USE nombre_db

GO

ALTER authorization

ON schema::db_owner

TO dbo

GO

DROP user nombre_user

GO

Nota: Asegurarse de tener seleccionada la DD.BB. correcta (desplegable de arriba a la izquierda).

---

VER UBICACION FÍSICA DE LOS FICHEROS DE DB

SELECT name, physical_name AS NewLocation, state_desc AS OnlineStatus FROM sys.master_files

WHERE database_id = DB_ID(‘NombreBaseDeDatos’)

GO

PASAR DDBB A SINGLE USER

ALTER DATABASE MyDB

SET SINGLE_USER with rollback inmediate

GO

PASAR DDBB A MULTI USUARIO

ALTER DATABASE MyDB

SET MULTI_USER with rollback inmediate

GO

---

Tutorial para crear job y poder truncar de manera programada el archivo de log:

https://www.c-sharpcorner.com/article/daily-shrink-database-log-automatically-using-jobs-in-sql-server