Home › Notes › Windows › SQLServer

SQLServer

SQLServer

BEGIN TRANSACTION
-- add edits here
COMMIT TRANSACTION
GO

SET IDENTITY_INSERT dbo.Table ON;
-- insert with id
SET IDENTITY_INSERT dbo.Table OFF;
  • Tools
    sp_who;
sp_who2;
sp_help 'table-name';
  • Backup/Restore
    • Backup
      • To Disk
        BACKUP DATABASE [xx] 
TO DISK = N'/data/backups/xx-20201222-11-45-52.bak' WITH NOFORMAT, NOINIT,  
NAME = N'xx_v1-20201222-11-45-52', NOSKIP, REWIND, NOUNLOAD,  STATS = 10

declare @backupSetId as int

select @backupSetId = position from msdb..backupset where database_name=N'xx_Default_STAGE_v1' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'xx_Default_STAGE_v1' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''xx_Default_STAGE_v1'' not found.', 16, 1) end

RESTORE VERIFYONLY FROM  DISK = N'/data/backups/xx_Default_STAGE_v1-20201222-11-45-52.bak' WITH  FILE = @backupSetId,  NOUNLOAD
      • Script
        #!/bin/bash
. .env
set -vx
TS=$(date '+%Y-%m-%d-%H%M%S-')
MSSQL_DATABASE=$1
docker-compose exec ss /opt/mssql-tools/bin/sqlcmd \
   -S localhost -U SA -P $MSSQL_SA_PASSWORD \
   -Q "BACKUP DATABASE [$MSSQL_DATABASE] TO DISK = N'/data/backups/${TS}$MSSQL_DATABASE.bak' WITH NOFORMAT, NOINIT, NAME = '$MSSQL_DATABASE', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

sudo chmod a+r ss-backups/*bak
aws s3 sync ss-backups/ s3://xx-dev-backups/x-dev/
    • Restore
      • From S3
        exec msdb.dbo.rds_restore_database 	
@restore_db_name='test-restore', 
@s3_arn_to_restore_from='arn:aws:s3:::xx-backups/di-data-backups/2020-12-21-di-data.bak',
@type='FULL';
      • From Disk
        # in client
USE [master]
RESTORE DATABASE [xx]
FROM  DISK = N'/data/backups/2021-09-27-xx-full.bak' WITH  FILE = 1,
MOVE N'x_Default_STAGE_v1' TO N'/var/opt/mssql/data/x_Default_Stage_v1.mdf',  
MOVE N'x_Default_STAGE_v1_log' TO N'/var/opt/mssql/data/x_Default_State_v1_log.ldf',  
NOUNLOAD,  STATS = 5
# don't always need to MOVE commands

# Ran this inside container 2025-01-07
#!/bin/bash
FNAME=x_Default_PROD_v2-final.bak
DBNAME=x_Default_PROD_v2
/opt/mssql-tools18/bin/sqlcmd \
  -S localhost -U SA -P $MSSQL_SA_PASSWORD -C \
	-Q "
RESTORE DATABASE [${DBNAME}]
FROM  DISK = N'/data/backups/${FNAME}' WITH REPLACE, FILE = 1,
MOVE N'xx_Default_PROD_v1' TO N'/var/opt/mssql/data/${DBNAME}.mdf',
MOVE N'xx_Default_PROD_v1_log' TO N'/var/opt/mssql/data/${DBNAME}_log.ldf'
"
  • Importing from file
    • Add GO every 1000 lines 
    # file with inserts
cat MilkSamplesSpec.sql \
|sed -e 's/x_Default_Stage_v1.//' \
|ruby -e 'ARGF.each_with_index do |line, n| puts line; puts "GO" if(n % 1000) == 0; end' \
> MilkSamplesSpecific-1000.sql

# --- this not needed
# top
SET IDENTITY_INSERT [dbo].[TableName] ON

# bottom
SET IDENTITY_INSERT [dbo].[TableName] OFF
  • Rename Database
    • Rename db but not files 
    USE master
ALTER DATABASE x_Default_Stage_v1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE x_Default_Stage_v1 MODIFY NAME = x_Default_DEMO_v3
ALTER DATABASE x_Default_DEMO_v3 SET ONLINE
ALTER DATABASE x_Default_DEMO_v3 SET SINGLE_USER
ALTER DATABASE x_Default_DEMO_v3 SET MULTI_USER
    USE master
ALTER DATABASE x_Default_DEMO_v3 SET OFFLINE
-- rename files on the filesystem /var/opt/mssql/data/
-- Rename the log files
ALTER DATABASE x_Default_DEMO_v3 MODIFY FILE (Name='x_Default_DEMO_v3', FILENAME='/var/opt/mssql/data/x_Default_DEMO_v3.mdf')
ALTER DATABASE x_Default_DEMO_v3 MODIFY FILE (Name='x_Default_DEMO_v3_log', FILENAME='/var/opt/mssql/data/x_Default_DEMO_v3_log.ldf')
-- Change database in to ONLINE mode.
ALTER DATABASE x_Default_DEMO_v3 SET ONLINE
ALTER DATABASE x_Default_DEMO_v3 SET MULTI_USER
  • Drop if exists
    IF  EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[QncAuditAssignments]') 
AND type in (N'U'))
  • Connected Users 
    -- Diconnect them with...
-- ALTER DATABASE dbname SET OFFLINE
USE master
SELECT
    DB_NAME(dbid) as DBName,
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE
    dbid > 0
GROUP BY
    dbid, loginame
;
  • FluentMigrations
    dotnet tool install -g FluentMigrator.DotNet.Cli
dotnet-fm list migrations --processor SqlServer2016 -a bin/Debug/netcoreapp3.1/xx.Web.dll
Client