Backup All Databases, SQL Server

Using the below script we can take all databases backup with one command!!

You can check my other post BACKUP STATUS to know the current DB backup status.

exec sp_msforeachdb ' use [?]; IF ''?'' not in ( ''master'',''tempdb'',''model'',''msdb'',''DBAdmin'') BEGIN        use [master]        Print ''?''        ALTER DATABASE [?] SET READ_ONLY WITH ROLLBACK IMMEDIATE        Print ''Marked -- READ_ONLY''        BACKUP DATABASE [?]        to disk = ''<Disk Location/Path>\?_Backup_FULL_20190924.bak''        with COMPRESSION        Print ''Backup Done''        ALTER DATABASE [?] SET MULTI_USER WITH NO_WAIT END ELSE BEGIN        Print DB_NAME()+ '' -- IGNORED'' END'

Thanks.

Grant Solarwinds to access and monitor DB performance

If we use Solarwinds DPA to monitor the DB health and performance and want to get notified about the data file space and long-running sql queries, then first of all we have to grant access on all dbs to SW by below script..


---------------- Solarwinds DPA grant access script -------------
-- Change the DOMAINNAME\User

USE master GO IF NOT EXISTS(SELECT * FROM sys.syslogins WHERE name = 'MED\swadmin') BEGIN           print 'CREATE LOGIN'        CREATE LOGIN "MED\swadmin" FROM WINDOWS END GO GRANT VIEW SERVER STATE TO "MED\swadmin" GO GRANT VIEW ANY DEFINITION TO "MED\swadmin" GO print 'sp_adduser' EXEC sp_adduser @loginame = 'MED\swadmin', @name_in_db = 'MED\swadmin' GO GRANT EXECUTE ON xp_readerrorlog TO "MED\swadmin" GO USE msdb GO print 'sp_adduser' EXEC sp_adduser @loginame = 'MED\swadmin', @name_in_db = 'MED\swadmin' GO EXEC sp_addrolemember N'db_datareader', N'MED\swadmin' GO print 'Loop Start' go EXEC sp_MSforeachdb 'USE [?]; EXEC sp_adduser @loginame  = ''MED\swadmin''' GO

Backup SQL Server DB Objects (user defined) as script

using below tool, you can backup a database, with all user defined objects, as a script and save it to the file.... without any third party/extra tool.

NOTE: download the ZIP file "DBScriptGenerator.zip"

After download, extract the zip and place it anywhere you want.
in my case, location is -- "C:\DBScriptGenerator\"

this zip file contains an EXE file named "DBScriptGenerator.exe", along with the other DLL files.

now we have to call this executable from anywhere we can. of course, we need to pass all the parameters.
in my case, I am calling from CMD...

Format :
DBScriptGenerator.exe "DatabaseServerName" "DatabaseName" "DatabaseUser" "DatabasePassword" "FullPathToSaveWithFileName"

Example:
DBScriptGenerator.exe "PRODSV" "EMPMAS" "UADMIN1" "u@Dwin!" "C:\DBScriptGenerator\EMPMAS.sql"

NOTE:::
for Advanced users only ...

There is a DLL file, DBScriptGenerator.dll, available to use programmatically. we can use this DLL from C# classes and/or SSIS scripts also.

Object List: Table, Procedure, Function, View, Trigger, Schema, assembly .. all user objects