Tuesday, July 22, 2014
 
 

You Must be Logged in to be able to Download Scripts / Tools from this site. If you do not have an Account, please Register (Free) yourself and then Login.

SQL Server Automation Scripts & Tools
Search
Permalink
BACKUP ALL DATABASES IN SQL SERVER
Thumbnail

This is a SQL script of get all Databases Backup in particular folder.
backup file name will be "DataBaseName_{Month}_{Date}_{Year}.bak"

Notes :

1) Current user have full access of backup database.
2) Remote drive setup is extra step you have to perform in sql server in order to backup your dbs to remote drive. 
3) you have to chnage you sql server accont to a network account and add that user to have full access to the network drive you are backing up to.



File size 3 K
Downloads 4
Date Fri 05/30/2014 @ 12:29
Author krushnakant ladani
EMail k.ladani21@gmail.com
RATING:   COMMENTS (0)  

Permalink
Get a report on Orphan user accounts in SQL Server
Thumbnail
This script helps you find orphan accounts from all the databases
of your local SQL Server. It scrolls thru all the databases using  an undocumented command system stored procedure called SP_MSForEachDB.

Saves time in having to go thru each of the databases and typing in commands to identify orphans commands.


File size 1 K
Downloads 257
Date Sat 01/29/2011 @ 02:58
Author Saleem Hakani
EMail SaleemHakani@hotmail.com
RATING:   COMMENTS (4)  

Permalink
Find out Server Role information from all Logins
Thumbnail
This script will provide you with a a list of SQL Server logins along with the server roles they belong to. The output is generated in a grid format so that it is easy to read.

This script can be helpful when you have to find out which login belonged to which server roles in SQL Server.


File size 2 K
Downloads 214
Date Sat 01/29/2011 @ 02:53
Author Saleem Hakani
EMail SaleemHakani@hotmail.com
RATING:   COMMENTS (2)  

Permalink
Find out what's happening in TempDB database
Thumbnail

This script shows you what's happening behind the scenes of TempDB system database.

It priovides the following information:

Database Name, SQL Statement, Start_OffSet, Execution Count, Total Physical Reads, Total Logical Writes, etc.

Very helpful when troubleshooting queries



File size 3 K
Downloads 351
Date Sat 01/29/2011 @ 02:49
Author Saleem Hakani
EMail SaleemHakani@hotmail.com
RATING:   COMMENTS (0)  

Permalink
How to get drive space information using T-SQL
Thumbnail

There are times when DBA will need to find the information about disk drives from within SQL Server. It is easy to just open up Windows Explorer and look at my computer to get the drive space information but getting that same information from SQL Server is little challenging. This script will help you obtain drive space information (snapshot of My Computer) from SQL Server.

When executed, the output of this script will consists of:
1) Drive Letter
2) Total Size in MB
3) Used Space in MB
4) Free Space in MB
5) Total Percent of Free disk space
6) Total percent of disk being used
7) Total space occupied by SQL Server on that drive
8) Current date and time when this information was generated (Good for maintaining history)

Note: If you are using this script on SQL Server 2005 or SQL Server 2008, you must enable "OLE Automation" feature



File size 3 K
Downloads 266
Date Sat 01/29/2011 @ 02:41
Author Saleem Hakani
EMail SaleemHakani@hotmail.com
RATING:   COMMENTS (3)  

Permalink
Get the details of all your databases (DB Report)
Thumbnail

There are times when you would want to know how your databases are configured and setup across various drives. Imagine if you are having to rebuild the servers and you are restoring a file backup of all SQL Server data/log files and if you don't know where each of the data or log files were residing then that could cause the databases to go into suspect mode or will not start the database. In situations like that, it's always a good idea to generate a report of your database configuration and store it in a safe place should the server crash and you have to rebuild SQL Server from the backup files.

SQL_DBReport script allows you to view filegroup related information for any or all databases.

It returns the following information:
1) Database Name
2) Logical names of all the files (Data & Log (mdf, ldf & ndf))
3) Size of each files in mega bytes (MB)
4) Drive letter on which each of the files are residing
5) Type of file/filegroup (Primary, Secondary, Log, etc)
6) Location (full path) of each of those files

Syntax: SQL_DBReport <@DatabaseName>
Example: To view DB report for a specific database (ex: OpsDB database)
Exec SQL_DBReport 'OpsDB'

Example: To view DB report for all the databases residing on the local SQL Server
Exec SQL_DBReport '*'

Example: To obtain help
Exec SQL_DBReport 'HELP'



File size 3 K
Downloads 244
Date Sat 01/29/2011 @ 02:34
Author Saleem Hakani
EMail SaleemHakani@hotmail.com
RATING:   COMMENTS (0)  

Permalink
Get the facts about your database backups
Thumbnail

This script will uncover the details of your database backups.

It provides you with the following information:

  • Database Name
  • Backup Type
  • Uncompressed size in MB (You can also get compressed size by slightly changing this procedure)
  • Last Backup Date when the backup was performed on the database
  • Duration (How much time it took to perform backup on this database)
  • Megs per minute (Shows you how many mega bytes did it backup per minute)
  • Backup file location (Location where the backup files are residing)


File size 2 K
Downloads 142
Date Sat 01/29/2011 @ 02:22
Author Saleem Hakani
EMail SaleemHakani@hotmail.com
RATING:   COMMENTS (0)  

Permalink
Script to find out SQL Server data and log usage from all drives
Thumbnail
If your databases are spread across various drives on the server, how would you find out how much disk space is each of the datafile or the log file is using?

This script will give you a quick snapshot of how much disk space is being used by SQL Server data and log files from all the disks of your SQL Server. This script has been very helpful in monitoring data and log growth and also for disk capacity planning.


File size 2 K
Downloads 144
Date Sat 01/29/2011 @ 02:16
Author Saleem Hakani
EMail SaleemHakani@hotmail.com
RATING:   COMMENTS (0)  

Permalink
Script to perform full database backups
Thumbnail
This script allows you to perform full database backups on both users and system databases and will skip any database on the server that is participating as a mirror copy.

Great way to automate database backups.


File size 1 K
Downloads 102
Date Sat 01/29/2011 @ 02:10
Author Saleem Hakani
EMail SaleemHakani@hotmail.com
RATING:   COMMENTS (1)  

Permalink
Script to find out wheather you must Defrag or Reindex
Thumbnail

There are times when you will need to find out if you should simply defrag an index or rebuild an index.This script will help you make the right decision based on the volume of data fragmentation for your tables. This script allows you to obtain fragmentation information from tables and indexes of your local SQL Server.'

It also provides you with recommendation based on the amount of fragmentation detected in tables/indexes. It also has a feature that lets you generate a script for rebuilding or re-organizing all the indexes.

This script has been used for many properties across Microsoft and has been extremely helpful. Give it a try today...

This script allows you to quickly get the facts on how much percentage of fragmentation you have in tables/indexes for a single database or for all the databases. This tool even takes away the pain of reading the cryptic values returned by DMV's to identify the amount of fragmentation you have on your SQL Server system. Not just that but it even tells you what action you can take to fix the problem.



File size 8 K
Downloads 225
Date Sat 01/29/2011 @ 02:00
Author Saleem Hakani
EMail SaleemHakani@hotmail.com
RATING:   COMMENTS (3)  

< BACK  1 of 4  NEXT >
HOME   |   Article Library   |   Scripts & Tools   |   Training Videos   |   Job Board   |   Resources
Copyright 2010 by SQLCOMMUNITY.COM