Script out database – tables with indexes and primary keys

Here’s a piece of code that will help you script out a database, along with it’s tables, primary keys and indexes:

 

Rename LDF and MDF

Here’s a script for renaming your database and the LDF and MDF of it.
– Change path and physical name
ALTER DATABASE AdventureWorks2012 modify FILE (NAME = ‘AdventureWorks2012 ‘, filename =
‘D:\AdventureWorks2012_NEW.mdf’);

ALTER DATABASE AdventureWorks2012 modify FILE (NAME = ‘AdventureWorks2012_log’, filename =
‘D:\AdventureWorks2012_old.ldf’);

– Change logical names
ALTER DATABASE AdventureWorks2012 modify FILE (NAME = AdventureWorks2012 , newname =
AdventureWorks2012 _NEW);

ALTER DATABASE AdventureWorks2012 modify FILE (NAME = AdventureWorks2012 , newname =
AdventureWorks2012 _NEW_log);

– take database offline
ALTER DATABASE AdventureWorks2012 SET offline WITH ROLLBACK immediate

– Rename physical files
DECLARE @command1 VARCHAR (250)

SET @command1 = ‘RENAME D:\AdventureWorks2012 .mdf AdventureWorks2012_NEW.mdf’

EXEC master..Xp_cmdshell @command1

SET @command1 = ‘RENAME D:\AdventureWorks2012 .ldf AdventureWorks2012_NEW.ldf’

EXEC master..Xp_cmdshell @command1

– bring database online
ALTER DATABASE AdventureWorks2012 SET online

– rename database
ALTER DATABASE AdventureWorks2012 modify NAME = AdventureWorks2012_NEW

 

 

Configure SQL Server Database Mail – Gmail / Yahoo/ Hotmail

What the SQL server database Mail does is to basically send out emails from the SQL Server engine to SMTP servers.

Why would you need that ? Well, let’s see: you might have long running jobs and rather than keeping on checking on them you can set an alert to send you an email when the job is complete. You might need to send out data reports from your database to certain people on a time based period, nothing simpler than that: set a job with a query that will be executed and it’s results will be sent via email to the specified recipients (the results can be displayed in a csv, xls or html file) .

First thing’s first: you need to configure the Database Mail utility. The steps below will show you just how to do that:

1. Go under Management -> Right click Database Mail and select the first option:

1

2. Then you’ll need to add an SMTP account, you can see below how do add an Gmail , Hotmail and Yahoo SMTP account:

2

3. Gmail SMTP account:

  • Email address: your Gmail address
  • Display Name: the name of the address from which you want your emails to appear from
  • Gmail SMTP server name: smtp.gmail.com
  • Gmail SMTP port: 465

3gmail

4.Yahoo SMTP account:

  • Yahoo SMTP server name: smtp.mail.yahoo.com
  • Yahoo SMTP port: 465

5yahoo

5. Hotmail SMTP account:

  • Hotmail SMTP server name: smtp.live.com
  • Hotmail SMTP port: 25

4hotmail

6. You just click OK after selecting your SMTP account and completing the rest of the details on the dialog window, as the authentication, in my case i went for the basic authentication

6

7.You then get to set the your profile as public or private, we are gonna go with public so that every user can send email from this profile without explicitly specifying the profile.

7

8. Then you’re prompted with the window where you need to configure the system parameters, we’re going to leave everything as it is, apart from the Maximum File Size, the default value is 1.000.000 bytes, which is a little under 1MB. Now if you’re planning to also send attachments via email you’ll see that the default value won’t be enough in most cases, so we’ve set our maximum size to approximately 5 MB, which should suffice in most cases. But if you’re planning to send larger amounts of data out of your server then the Database Mail utility isn’t the way to go.

8

9. After finishing our wizard you can test you setup by simply right-clicking on the Database Mail utility and selecting Send test email:

9yahoo

If you want other informations about the emails going out of your server you can always use the following scripts: http://www.sqlpentruincepatori.ro/sql-server-database-mail-querys/

Import data from Excel into SQL SERVER – simple steps

When trying to import some data from Excel into my test database I over complicated the whole affair, when I could have taken advantage of the Import wizard functionality SSMS offers.

So here are the steps to easily import data from an Excel spreadsheet to your database:

  1. Select your database, then right click and select Tasks -> Import Data…
  2. A window will pop out prompting you to select the Data Source:

2

Be careful with the ‘First row has column name’ tick box, make sure you select the appropriate choice for you.

  1. Specify where to copy the data to:

3

4. Click Next to proceed to the Specify Table Copy or Query dialog.

4

 

  1. I have selected the first option from above, so now the Select Source Tables and Views dialog window is prompted. In here we can select the specific sheet from where we wish to copy our data and the destination table (you can either create a new table or select an existing one)

5

You can click the Edit mappings button to review the column mappings if we’re copying the data to an existing table, we can also use the Preview button to view the first 100 rows of the data in the data source.

  1. After we’re ok with the settings we have chosen we press the Next button, another dialog window pops out and we can either execute the package we created or we can also save it for future runs.

6

7

 

  1. Click Next to complete the Import Wizard, and then finish to execute the SSIS package. And then you should see all your data in the tables you’ve selected.

LARGEST tables from the DB

Here’s a quick way to find out the top 20% largest table from your database.

SELECT TOP (20) PERCENT * FROM #LargestTable ORDER BY TotalSpaceKB DESC
DROP TABLE #LargestTable

Foreign Key and Primary Key dependencies

A way to establish which columns in a given database are joined via the primary and foreign key relationships.

Capacity Planning

Here is a nice little script that will tell you everything you need to know in terms of the sizes of your tables, will perform an analysis on each table for each database. Hope it helps :)

Most populated tables from the database SQL SERVER

In case you need to find a list of the most populated tables within your database here you have a very useful script:

” is not a valid login or you don’t have permission – SQL Server 2008 R2

Yesterday I spent 4 hours trying to install SQL Server on my newly installed Windows 8, and I kept receiving the same error:   ” is not a valid login or you don’t have permission.

error

At first when I started the installation of SQL Server, it did complain about compatibility issues, but I chose to ignore it as I thought it might have to do with the fact it might not have properly mounted my ISO image of the SQL Server.

And just when I was about to reinstall Windows after trying on several things I came across the solution to fix this: my computer name and the username are the same. This interferes with the Windows Authentication for SQL Server. My username on Windows 8 was Olia\Olia.

After changing my computer name to Olia-PC I’ve reinstalled  the SQL Server and it worked like a charm.