Skip to content

Instantly share code, notes, and snippets.

Avatar

Daniel Schroeder deadlydog

View GitHub Profile
@deadlydog
deadlydog / CopySqlDatabase.sql
Last active Aug 29, 2015
Backup and restore (copy) sql database to specific drive based on environment
View CopySqlDatabase.sql
-- This script copies a database. The paths in this script are designed to run on IQ-RGVSQL009.
-- You need to have SQLCMD mode enabled to run this script (Query menu -> SQLCMD Mode).
:setvar DbToBackup "SeedCore" -- The database to copy from.
:setvar DbToRestore "Developer_DanS" -- The database to create/overwrite.
:setvar Environment "" -- Should be either "Test", "Automation", or an empty string (for Dev). Corresponds to the Sql Server Instance the DB is on.
:setvar TruncateDevDatabases "True" -- Leave this as 'True' to not keep full logs for the DbToRestore on the Dev Sql Server Instance.
:setvar PutAutomationDbOnSlowDrive "True" -- Leave this as 'True' to restore the DbToRestore to the Slow hard drive on the Automation Sql Server Instance.
USE [master]
@deadlydog
deadlydog / SetUserPermissionsOnAllSqlDatabases.sql
Last active Aug 29, 2015
Loop through every SQL Server database on server, create user if they don't exist, and set that user's permissions
View SetUserPermissionsOnAllSqlDatabases.sql
exec master.dbo.sp_foreachdb
'USE ?
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''Domain\UserOrGroup'')
BEGIN
CREATE USER [Domain\UserOrGroup] FROM LOGIN [Domain\UserOrGroup]
END
exec sp_addrolemember ''db_backupoperator'', ''IQMETRIXHO\Development'';
'
GO