Script SQL Server
Personal Logshipping - Database replication for Reports and Contingency - Loading balance Performance
I had decided to build a manual Logshipping instead of using the built-in MS LogShipping for my better control in terms of current connection controls.
--// Create tbLogShippingControl - I built a table to control the...
Migration - Backup database with Read-Only and OffLine
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @strSQL NVARCHAR(200) -- used for file...
Migration - Check the remaining time to complete the backup - Progress Status
SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed...
Migration - Simple Restore Sample
--// Remove the Users Connections and detach the database
Exec DBA..sp_DBA_KillDB DB
restore filelistonly from disk = '\\backupServer\DB_20181019.BAK'
go
restore database DB from disk = '\\backupServer\DB_20181019.BAK'
with
move...
Migration - Makes unavailable All or Selected the databases (Read-Only / OffLine)
DECLARE @name VARCHAR(50) -- database name
DECLARE @strSQL NVARCHAR(200) -- used for file name
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE (name like 'Fin%'
or
...
Kill all the connections from a specific database
USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_DBA_KillDB] (
@DatabaseName varchar(60)
)
As
DECLARE @dbid tinyint
DECLARE @spid smallint
DECLARE @exec_str varchar(10)
...
List the last database restored on the server
WITH LastRestores AS
(
SELECT
DatabaseName = [d].[name] ,
[d].[create_date] ,
[d].[compatibility_level] ,
[d].[collation_name] ,
r.*,
RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY...
Different ways to make a table read only in a SQL Server database
--// Simple Read-Only
====================================================================
ALTER DATABASE [MyDB] ADD FILEGROUP [READ_ONLY_TBLS]
--// Using...
Execute Dynamic SQL commands in SQL Server
--// Execute ====================================================================
DECLARE @sqlCommand varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'CustomerID, ContactName, City'
SET @city = '''London'''
SET @sqlCommand = 'SELECT ' + @columnList + '...
T-SQL Script to Take Database Offline – Take Database Online
-- Create Test DB
CREATE DATABASE [myDB]...