MSSQL

 

Recover a lost SA password

https://www.sqlshack.com/recover-lost-sa-password/

 

Use [dbname]
go
sp_change_users_login 'update_one', 'username', 'username'

 

sp_change_users

declare @command varchar(5000)='use ?;
exec sp_change_users_login "update_one", "wsadmin", "wsadmin"'
exec sp_msforeachdb @command

 

 

#close sessions on specific db, alter multiuser 

USE master
GO

DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses 
WHERE dbid = db_id('dbname')

EXEC(@kill);



GO
USE master 
ALTER DATABASE dbname SET multi_user WITH ROLLBACK IMMEDIATE

 

#db file size

with fs 
as (
select database_id, type, round(size * 8.0 / 1024,2) size from sys.master_files
)
select 
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db
where name not in ('db1','db2')
order by LogFileSizeMB desc;


select DB_NAME(database_id)dbname,name,round(size * 8.0 /1024,2) size,type_desc,physical_name
from sys.master_files where type=1
order by size desc;

 

#auto backup

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_dbbackup]    Script Date: 08/18/2021 15:43:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        sam wang
-- Create date: 2021/08/18
-- Description:    backup databases
-- =============================================
ALTER PROCEDURE [dbo].[sp_dbbackup] (@bakpath varchar(256) = 'I:\DatabaseBackupAuto\')
AS
BEGIN

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
 
-- specify database backup directory
SET @path = @bakpath  
 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
 
DECLARE db_cursor CURSOR READ_ONLY FOR  
SELECT name 
FROM master.sys.databases 
WHERE name  IN ('db1','db2')  -- exclude other databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
   SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
   BACKUP DATABASE @name TO DISK = @fileName   with compression
 
   FETCH NEXT FROM db_cursor INTO @name   
END   
 
CLOSE db_cursor   
DEALLOCATE db_cursor


END

 

#clear expired bak

ForFiles /p "I:\DatabaseBackupAuto\ " /s /d -3 /c "cmd /c del @file"

 

 

#shrink DB

myStr = """
db1
db2
db3
"""

mylist = myStr.split()
print(mylist)

for k in mylist:
    # set simple mode
    print('USE [master]\nGO\nALTER DATABASE [%s] SET RECOVERY SIMPLE WITH NO_WAIT\nGO' % k)

    # shrinkFile
    # print('USE [%s]\nGO\nDBCC SHRINKFILE (N\'%s\' , 50, TRUNCATEONLY)\nGO' % (k, v))

    # shrink DB
    print('USE [%s]\nGO\nDBCC SHRINKDATABASE(N\'%s\' )\nGO' % (k, k))

    # set Full mode
    print('USE [master]\nGO\nALTER DATABASE [%s] SET RECOVERY FULL WITH NO_WAIT\nGO\n\n' % k)

 

#shrink log

sql="""
select DB_NAME(database_id)dbname,name,round(size * 8.0 /1024,2) size,type_desc,physical_name
from sys.master_files where type=1
order by size desc;
"""

myStr = """
db1\tdb1_log
db2\tdb2_log
"""

mylist = myStr.split('\n')
print(mylist)

for str in mylist:
    if str == '':
        continue
    k = str.split('\t')[0]
    v = str.split('\t')[1]
    # set simple mode
    print('USE [master]\nGO\nALTER DATABASE [%s] SET RECOVERY SIMPLE WITH NO_WAIT\nGO' % k)
    # shrinkFile
    print('USE [%s]\nGO\nDBCC SHRINKFILE (N\'%s\' , 50, TRUNCATEONLY)\nGO' % (k, v))
    # set Full mode
    print('USE [master]\nGO\nALTER DATABASE [%s] SET RECOVERY FULL WITH NO_WAIT\nGO\n\n' % k)