MSSQL Tips: Tip #37 – Template to Create SQL Server Synonyms with checks

Featured

When a SQL Server synonym is created with T-SQL the base object’s existence is checked only at run time. SQL Server Management Studio (SSMS) has some built-in checks that are performed during synonym creation whereas T-SQL scripts can be written several different ways and do not have any checks by default. In this tip I explain what is the best way to create synonyms and why.

Please read the latest MSSQL Tip: “Template to Create SQL Server Synonyms with checks“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

Share

MSSQL Tips: Tip #33 – SQL Server Master Data Services (MDS) custom Delete Stored Procedure

By default entity members in Master Data Services (MDS) are not deleted permanently. They are “soft” deleted (deactivated) and available for recovery from a transaction history.

In this tip we have listed several methods for deleting MDS members and we have provided a script for the custom “delete” stored procedure.

Please read my latest MSSQL Tip: “SQL Server Master Data Services (MDS) custom Delete Stored Procedure“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

Share

MSSQL Tips: Tip #29 – SQL Server Database Decommissioning Check List

Featured

Different companies have various requirements and processes for database or database server decommissioning. In this tip we have provided steps that will help you decommission a database and make sure you have a good back-out plan (for example, in case there is a need to bring the database back online). We have also provided scripts that will help you identify dependencies in case there is a need to migrate the database instead of decommissioning.

You may already have a process documented, but this list will help you review most of the dependencies and make sure you are prepared for either a migration or decommissioning.

Please read my latest MSSQL Tip: “SQL Server Database Decommissioning Check List“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

Share

MSSQL Tips: Tip #24 – SQL Server 2016 Query Store Queries

What are some practical applications of using the Query Store? What queries can be run against the Query Store? What sort of questions can I get answered?

Please read my latest MSSQL Tip: “SQL Server 2016 Query Store Queries“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

Share

MSSQL Tips: Tip #22 – Compare SQL permissions using SQL Server Data Tools

Featured

In this new tip you will find out how to use Visual Studio and SQL Server Data Tools to compare a database permissions and to copy permissions (including users and logins).

Please read the latest MSSQL Tip: “Compare SQL permissions using SQL Server Data Tools“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

Share

MSSQL Tips: Tip #11 – Script to get CPU and Cores for SQL Server 2012 Licensing

This MSSQLTips tip will help you to audit your SQL Server licensing with PowerShell script.

Please read the latest MSSQL Tip: “Script to get CPU and Cores for SQL Server 2012 Licensing“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

Share

MSSQL Tips: Tip #10 – Different Ways to Find SQL Server Object Dependencies

In my 10th MSSQLTips tip you will learn about different ways to display objects dependencies and you will learn how to use provided scripts in your day-to-day work.

Please read the latest MSSQL Tip: “Different Ways to Find SQL Server Object Dependencies“.

Check out all of my tips here: http://www.mssqltips.com/sqlserverauthor/94/svetlana-golovko/.

Share

Q&A – SQL Server Developer: How to search for the text in stored procedures

SQL Server provides different metadata views that can help developers in their work, but sometimes developers are not up to date with the latest system tables and views changes.

In this Q&A we provide the query to search for the specific text in the stored procedures, views or functions. This is the easiest way to search for the string in objects definitions:

SELECT OBJECT_NAME(OBJECT_ID) FROM sys.sql_modules 
WHERE [definition] LIKE '%my_search_string%'

Another way is using syscomments system table which was used in older versions of SQL Server:

SELECT OBJECT_NAME(ID) FROM sys.syscomments 
WHERE [text] LIKE '%my_search_string%'

Also, you can use OBJECT_DEFINITION metadata function:

SELECT name, type_desc FROM sys.all_objects 
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%my_search_string%'

Share

Backup-Copy-Restore with one script using SQLCMD mode option in SSMS

One of the most common DBAs tasks is databases backup and restore. We do this to test the backups, to refresh test databases, to migrate the databases etc. For some of the tasks we have scripts, for others we need to create scripts on the fly.

Here I would like to share with you a simple script that you will need to run in SQL Server Management Studio’s (SSMS) SQLCMD mode. This script will allow you to create the backup, copy the backup file and restore it on the different server – all from a single SSMS window.

Start new query in SSMS and under “Query” menu select “SQLCMD Mode”:

3-7-2013_1

Run this script (replace host name and SQL Server Instance name for the source and destination variables):

Script

Note, that I commented out the “MOVE” option. Remember to add it to the script if your destination database files’ locations are different from the source.

Get the script from the SkyDrive here.

Share