How to script SQL Server 2008 diagrams and restore them

I got a recent request of restoring diagrams from one database to another and found an awesome solution in here.

What SQL Statements Are Currently Executing?

This is a fundamentally useful script.

How to grant execute permission of all stored procedures to a user?

One recent request from our testers is that they need to execute all stored procedures on the testing database. And the simplest solution is to create a database role which is granted the execute privilege and add the testers' database users into this role.

Here it goes the script:

use [testing_db]
go
create role [testers]
go

grant exec to [testers]
go

exec sp_addrolemember N'testers', N'tester_db_user_account'
go


By running the above script, not only the execute rights of stored procedures, but also those of scalar functions are granted to the user.

Here are the two links that you might find interesting as well regarding this topic:
1 and 2.

Once you can't remote desktop your server, you can try to reset the session like this

See the original post here.

Command to open SQL Server Configuration Manager

I believe most of us already know to type "ssms" (for 2008) or "sqlwb" (for 2005) or "isqlw" (for 2000) in the "Run" command box from the start menu to open SSMS studio or Query Analyzer.

Here is one more, to open SQL Server Configuration Manager, you need to type "SQLServerManager10.msc" to get there. If you want to see the list of all the windows services, "services.msc" is the command you need.

Error: The partner transaction manager has disabled its support for remote/network transactions

I bumped into this error message when our developers created a trigger on a Windows 2003 server that updates a table in a linked server (a Windows 2008 server). The second part of the error message is: "linked server was unable to begin a distributed transaction". I put the whole error message text here purely for the sake of Google keywords and the essential issue behind is actually how to enable the distributed transaction function on a Windows server.

If you Google the title, there is a useful link from MSDN, which provides the following solution:

First verify the "Distributed Transaction Coordinator" Service is
running on both database server computer and client computers
1. Go to "Administrative Tools > Services"
2. Turn on the "Distributed Transaction Coordinator" Service if it is not running

If it is running and client application is not on the same computer as
the database server, on the computer running database server
1. Go to "Administrative Tools > Component Services"
2. On the left navigation tree, go to "Component Services > Computers
> My Computer" (you may need to double click and wait as some nodes
need time to expand)
3. Right click on "My Computer", select "Properties"
4. Select "MSDTC" tab
5. Click "Security Configuration"
6. Make sure you check "Network DTC Access", "Allow Remote Client",
"Allow Inbound/Outbound", "Enable TIP" (Some option may not be
necessary, have a try to get your configuration)
7. The service will restart
8. BUT YOU MAY NEED TO REBOOT YOUR SERVER IF IT STILL DOESN'T WORK(In my case, we didn't reboot the server)

On your client computer use the same above procedure to open the
"Security Configuration" setting, make sure you check "Network DTC
Access", "Allow Inbound/Outbound" option, restart service and computer
if necessary.

On you SQL server service manager, click "Service" dropdown, select
"Distribute Transaction Coordinator", it should be also running on
your server computer.

Basically speaking, to enable distributed transaction for linked server, besides making sure the Distributed Transaction Coordinator service is up and running, you have to enable network DTC access. And there is a very good article (for Windows server 2003) from Microsoft's knowledge base officially documenting exactly the same set of actions.

Here are the screen shots you have to encounter during the settings for Windows server 2003:

Component Services:


My Computer Properties


Security Configuration


If you tried to enable network DTC access for Windows server 2008 (or Windows vista if you are fan of it), Microsoft has already provided another article in its knowledge base for your viewing pleasure as well. The settings are almost the same as those for Windows Server 2003, the slight change lies in where you can find the "Security Configuration" dialog box; in Windows Server 2008, it is in the "Local DTC" properties instead of "My Computer" properties in the component services MMC.

One screen shot speaks more than 1000 words.

Good tutorial script for full recovery and transaction log backup

--Creating the database for the demo
create database DemoDB
go

--Make sure that the database is in full recovery model
alter database DemoDB set recovery full
go

--Making the first full backup. From now on I can use log backups
backup database DemoDB to disk = 'c:\DemoDB_1.bak' with init
go

--Creating a new object in the database
use DemoDB
go
create table DemoTable (i int)
go

--backup the log
backup log DemoDB to disk = 'c:\DemoDB_1.trn' with init
go

--Inserting a record that doesn't exist in all the backups that were taken into the table
insert into DemoTable (i) values (1)
go

--Creating a second full backup. I won't be using this backup when I'll restore the database
backup database DemoDB to disk = 'c:\DemoDB_2.bak' with init
go

insert into DemoTable (i) values (2)
go

--Creating the second log backup that includes the second record in DemoTable
backup log DemoDB to disk = 'c:\DemoDB_2.trn' with init
go

--Dropping the table (to mimic an error)
drop table DemoTable
go

--Now I'll restore the database. I'll use only the first full backup
--with the 2 log backups. This proves that I don't need to use the
--copy_only switch, if I have a full backup with all log backups that
--were taken since then even if another full backup was taken between
--the log backups
use master
go
restore database DemoDB from disk = 'C:\DemoDB_1.bak' with norecovery, replace
restore log DemoDB from disk = 'c:\DemoDB_1.trn' with norecovery
restore log DemoDB from disk = 'c:\DemoDB_2.trn' with recovery
go

Use DemoDB
go
--Notice that I have the table with 2 records
select * from DemoTable
go


--cleanup
use master
go
drop database DemoDB

How to know the logical files' names from a database backup?

To use the "move" clause in a restore script, we need first to know the logical files inside the backup. The following sql command can do the trick:

restore filelistonly
from disk = N'the backup location'

How to export from Excel to a CSV file with double quotes on each field

The default "save as CSV" option from Excel does not include any quotes on the fields, therefore a macro is needed in this case:

Sub CSVFile()

Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As Variant
FName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")

If FName <> False Then
ListSep = Application.International(xlListSeparator)
If Selection.Cells.Count > 1 Then
Set SrcRg = Selection
Else
Set SrcRg = ActiveSheet.UsedRange
End If
Open FName For Output As #1
For Each CurrRow In SrcRg.Rows
CurrTextStr = ""
For Each CurrCell In CurrRow.Cells
CurrTextStr = CurrTextStr & """" & CurrCell.Value & """" & ListSep
Next
While Right(CurrTextStr, 1) = ListSep
CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
Wend
Print #1, CurrTextStr
Next
Close #1
End If
End Sub

The original post can be retrieved here.

The two most important differences between temp table and table variable

Since this can be one of the mostly asked interview questions, I better have the answers here instead of doing another round of Googling.

1. table variable does not support transaction while temp table does
2. table variable does not support constraints while temp table does

A full expiation is here.

SourceSafe plug-in setting for Visual Studio

This might sound very stupid, but it took quite some time for me to figure out why my Visual Studio failed to connect to our SourceSafe database on which it turns out that the internet connection is switched off by default(On Server, when installing VSS, select Custom Install and make sure you select the "Server Components" or at least the HTTP Remote Access component; they are not installed in Default installation confuguration). While the Visual Studio by default connects to internet SSL connection based database, my multiple attempts failed without providing me too much of a clue since a VNC and a URL in a LAN setting do not vary from each other too much. So after checking an interesting article, the solution is plain simple as shown in the below screen shot in Visual Studio's options.

How to bring a restored database from the "restoring" state back to operational

If you are trying to find a solution to bring your database out of the "restoring" state after a restore, this is the right place you should be.

When you use restore statement the with the "with norecovery" option, after the restore, the database will be put into a 'restoring' state. To bring it back online and operational, you need the follow script:

restore database db_name with recovery

To avoid yourself from this situation, always run the restore with the "with recovery" option.

More info about recovery state after a restore operation can be found at the "Restore Database (Options Page)" at MSDN.

Script to monitor backup and restore progress

I have been recently busy with tuning the restore performance on a testing server. The original disk array was configure to be RAID5 and the restore process was super slow which cost 16 hours to restore a 300 GB database backed up from prod. I've also double-checked CPU and memory stats, none is believed to be the bottleneck. So we decided to downgrade RAID5 to RAID0 since our testing environment doesn't require fault tolerance.

Right now, I am testing the restore again to see if the RAID0 can actually bring the expected performance benefits. However, the restore script I am using was accidentally set to have the argument with "stats = 100" (should've used stats = 1) which will not report progress info until the full restore is finished. So the questions becomes how to monitor a restore process when it is running in the background.

Luckily enough, there are at least two scripts that can be found through Google to monitor the progress of a backup/restore process. The first one is hidden in a thread in SQLServerCentral's forum and the other one is here.

Needless to say, I would like to post them in this post as well.

The first script can only monitor "restore" progress:

SELECT
A.name,
B.total_elapsed_time/60000 AS [Running Time in minutes],
B.estimated_completion_time/60000 AS [Remaining in minutes],
B.percent_complete as [% of completion],
(SELECT TEXT FROM sys.dm_exec_sql_text(B.sql_handle))AS Command
FROM
MASTER.sys.sysdatabases A inner join sys.dm_exec_requests B
on A.dbid = B.database_id
WHERE
B.command LIKE '%RESTORE%'
order by B.percent_complete desc,B.total_elapsed_time/60000 desc

The second script give more general info about both "restore" and "backup".

SELECT command,

s.text,

start_time,

percent_complete,

CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '

+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '

+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,

CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '

+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '

+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,

dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

How to rename a database?

sp_renamedb is the stored proc that can be used. The problem you might encountered is the exclusive lock might not be assigned properly. The best practice is to set the db to single user mode before changing its name and back to multi-user mode afterwards.

ALTER DATABASE old_db_name
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
SP_RENAMEDB old_db_name, new_db_name
Go
ALTER DATABASE new_db_name
SET MULTI_USER
GO

How to drop all the foreign key constraints in a database

Sometimes, foreign keys become annoying in a secondary database where reporting is the main purpose while integrity is not. So dropping all the foreign keys may become a user request from time to time.

The keyword to this question is the catalog view "sys.foreignkeys". And you can go to here to read about about how to retrieve all the foreign key constraints from a database and why catalog view is preferred.

Here is the script to delete all the foreign keys from a database.

use db_name
go

declare @fk_name varchar(100)
declare @tb_name varchar(100)
declare @cmd varchar(200)

declare cur_fk cursor
for
select name as ForeignKey_Name, OBJECT_NAME(parent_object_id) as Table_Name from sys.foreign_keys
order by Table_Name


open cur_fk

fetch next from cur_fk into @fk_name, @tb_name
while @@FETCH_STATUS = 0
begin
set @cmd = 'alter table ' + @tb_name + ' drop constraint ' + @fk_name
select @cmd
exec (@cmd)
fetch next from cur_fk into @fk_name, @tb_name
end

close cur_fk
deallocate cur_fk

How to drop all the user stored procedures from a database

A very informative script is here. But since the original posted script has very annoying line numbers, I would rather re-post the same code here again. And now you can copy and paste it to SSMS very easily. Also something worth mentioning is that the INFORMATION_SCHEMA.ROUTINES view actually contains user defined functions' info, so the SQL command "Drop procedure" will fail if you have any function defined in your target database. Therefore, I changed the following script to use sys.sysobjects view to retrieve the full list of names of user stored procedures.


-- this sets up the test database

-- Drop the database if it already existt

USE master
GO

IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'testdb'
)

DROP DATABASE testdb
GO

CREATE DATABASE testdb
GO

USE testdb
GO

CREATE PROC UserStoredProcedure_Sample1
AS
SELECT 'SQL Server rocks'
GO

CREATE PROC UserStoredProcedure_Sample2
AS
SELECT 'SQL Server rocks'
GO

SET NOCOUNT ON
-- to do this we have to use EXEC instead of sp_executesql

-- sp_executesql does not accept a DROP command in the SQL String

DECLARE @UserStoredProcedure VARCHAR(100)
DECLARE @Command VARCHAR(100)


DECLARE UserStoredProcedureCursor CURSOR SCROLL STATIC READ_ONLY FOR
select name from sys.sysobjects where xtype = 'P'

/*
SELECT
SPECIFIC_NAME
FROM
INFORMATION_SCHEMA.ROUTINES
*/


OPEN UserStoredProcedureCursor


FETCH NEXT FROM UserStoredProcedureCursor
INTO @UserStoredProcedure
WHILE (@@FETCH_STATUS = 0) BEGIN
SET @Command = 'DROP PROCEDURE ' + @UserStoredProcedure
-- display; visual check

SELECT @Command
-- when you are ready to execute, uncomment below

EXEC (@Command)

FETCH NEXT FROM UserStoredProcedureCursor

INTO @UserStoredProcedure

END

CLOSE UserStoredProcedureCursor

DEALLOCATE UserStoredProcedureCursor

SET NOCOUNT OFF

Job notification fails to send emails

This may be another rookie mistake, but I believe it is worth mentioning it here. I have two servers under my administration and one uses maintenance plan to send out emails after a backup operation is done and on the other one I tried to use job notification to send out email to my account when a "move database copies" PowerShell script job is complete. Both servers' email profiles have been set up properly and test emails have been sent successfully too. However, the one with the maintenance plan does send emails successfully every night while the other one with job notification kept silent for quite a few days in a row and I was suspecting the connectivity of the smtp server it uses. But after some research, the reason actually turned out to be a missed configuration check box on the "Alert System" tab of SQL Server Agent Properties. Strangely, on the first server where the maintenance plan is located and used to send out notification emails, this check box is left empty too. So the conclusion is that maintenance plan, which in essence is an SSIS package, does not require this "Enable mail profile" option to be turned on but email-sending jobs do. Also don't forget to restart SQL Server Agent service after the setting is changed.

How to determine whether a file exists or not in PowerShell script?

This is purely for my personal note, cmdlet "test-path" is the keyword:

if (Test-Path "file_lccation")
{
write-host "the file is there"
}
else
{
Write-Host "the file is missing"
}

PowerShell script to script all tables and stored procedures in a database

The script to script all the tables is available here.

Here I made my part of scripting all the stored procedures (excluding system stored procedures) from the ubiquitous database "pubs".

#the full path of the file that you want to script the stored procs to
$scriptfile = "C:\temp\StoredProcs.sql"

#if the script file already exists, remove it
if (Test-Path $scriptfile)
{
Remove-Item -Path $scriptfile
}

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$MyScripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter")
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "localhost"
$procs = $srv.databases["pubs"].StoredProcedures
$MyScripter.Server=$srv

$MyScripter.Options.FileName = $scriptfile
#AppendTofile has to be 'true' in order that all the procs' scripts will be appended at the end
$MyScripter.Options.AppendToFile = "true"

foreach ($proc in $procs)
{
#Assuming that all non-system stored procs have proper naming convention and don't use prefixes like "sp_" or "xp_"
if ( $proc.Name.IndexOf("sp_") -eq -1 -and $proc.Name.IndexOf("xp_") -eq -1 )
{
$MyScripter.Script($proc)|out-null
}
}

SQL Server connection string format for PowerShell scripting

This might look very primitive, but since I am a newbie to PowerShell scripting, it does no harm to write them down here and reference them in a later time when needed.

For sql server authentication:
"Server=server_name or server_ip;Database=db_name;Integrated Security=False;UID=login_name;Password=password"

For windows authentication:
"Server=server_name or server_ip;Database=db_name;Integrated Security=True"

Another website which is dedicated to connection strings might also come in handy, the link is here.

But shockingly, I can't find the above PowerShell script connection strings on the website.

Where to find the staring time of a sql server database restore?

I just encountered this problem today. And composed a post on SCC:

Hello everyone

This might sound very silly due to my novice to DB administration work. Yesterday I started restoring a database with a size of 350GB from a 75GB backup on a testing server from 11pm, and this afternoon at 3pm, when I remote-desktoped the server I found the restore has already finished. I then checked the sql server log in the log file viewer but can't see anything has been logged around 11pm yesterday and there are only two records around 6am early this morning saying that "Starting up database db_name" and "The database is marked RESTORING and is in a state that does not allow recovery to run". Also, there is another log around 2:20pm today stating that "Restore is complete on database db_name. The database is now available".

So my question is whether this restore started around 11pm yesterday when I starting running the restore script or it actually happened around 6am this morning? How to determine the starting time of a restore then? Or did I totally miss something really important here? Thanks very much in advance for your advice!

regards,
Ning

Feed-backs came surprisingly quick that the msdb.dbo.restorehistory is the table I need to look at to retrieve the starting time info for all the restore actions against all the databases in the instance. But it still seems strange to me that SQL Server log does not record the staring time of a restore action.

Change instance name of SQL Server

It turns out that after you've changed the Windows Server's name, the underlying SQL Server's instance name is not changed accordingly, although you can see an instance name same with the Windows Server's is displayed in the SSMS.

Everything else might work fine until you want to set up replication/distribution on this server. An error message like "SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, ‘old_name’. (Replication.Utilities)" is popped right into your eyes.

To see the old instance name, you can use "sp_helpserver" or "select @@servername" and to change the instance name once and for all, the following script has to be executed:

sp_dropserver 'old_name'
go
sp_addserver 'new_name','local'
go

A detailed and better formatted how-to article can be retrieved from here.

Remote desktop resolution problem

Here is the thing, I use command line short cuts (%windir%\system32\mstsc.exe /v: ) on my desktop to connect to our multiple database server environment and for two days a week, I go to my company and have my laptop docked to a much bigger monitor at the office. So from the bigger screen, the remote session's resolution becomes (automatically) much bigger than my 12 inch laptop's but displays beautifully on the 22 inch LCD screen. But when I come back and start working from home, the RDP session's resolution somehow "remembers" its latest setting and becomes more than my small screen can handle and I have to scroll up and down, from left to right to locate the things I have to work on.

A little bit of research shows that one of mstsc.exe's command line options gives a default full screen setting by using "/f" and I changed all my short cuts on my desktop according to this, and they are all working fine now. So the best practice to use mstsc command line is always put the "/f" argument at the end to ensure the remote resolution is in sync with your local ones in a full screen mode.

IIS logging problem

Although my official title is DBA, but at a small start-up, an "IT dude" is supposed to be capable of solving all kinds of problems which may be way beyond the database realm. And to please our big boss, I happily accepted a task to investigate why one of our IIS websites fails to generate any logging records in the log file directory. What entails is a significant amount of hours which only leads to a simple and stupid overlook by a previous "IT dude" who actually set up the whole website.

The following screen shot from tab "Web Site" on the IIS site properties is a must-go place for checking all the logging settings for IIS and it surely looks perfect on our server.


















However, the other missing check box on the "Home Directory" tab turned out to be the ultimate cause for this site failing to deliver any log files.

Two tricks discovered on Dell's Latitude E4300 laptop

The first one is pretty straight forward and probably more Dell-related. I just found out that you can turn off battery charging by pressing Fn+F2 which is an awesome feature because I usually prefer to have my battery on all the time in case there is a power outage, but at the same time, I don't wanna make too much over-charging which might cause a shortened battery life. So having the option of stopping charging the battery is definitely something nice to have. Big applause to Dell's engineers.

The second one is how to type in special signs for example the pound sign £ through a US-layout keyboard. The solution is pretty simple although I've spent quite a load of time Googling for it. First of all, turn on the num lock of the numeric keypad by press Fn+F4 (at least on Dell's Latitude E4300, you can find other key combinations on products from other manufacturers); after this, the num lock blue indicator will be lit up on the upper verge of the keyboard. And then, you can type the key stroke combination Alt+Fn+mjol (for the pound sign) which can be looked up in the "Programs->Accessories->System Tools->Character Map"; combination "mjol" from the normal keyboard is the same as "0163" from the numeric keypad. The second trick comes in handy especially when you have to type such a rarely used character in the password field of windows log-on screen where normal copy and paste does not work.

How to Remove the Windows Genuine Advantage (WGA) Notification Installation Wizard

I am not against Microsoft at all, actually I am pretty much a big fan of MS's products, e.g. SQL Server. But when Windows tries to annoy me with the WGA notification installation wizard, I have to strike back a bit. Here is the link to the video instructions on youtube.

http://www.youtube.com/watch?v=-OaC_9GStUo

Note: this is only for disabling the installation wizard, if you have already installed the WGA notification on your system, then please Google for other solutions.

How to script the schema and/or data from database objects?

Recently, I have been facing an requirement to replicate the same data structure from production to a testing environment in a VM machine. And the data in the production db is too large to be hosed in the VM and only the schema is of more interest for my assignment. Therefore, I did some research into how to actually replicate a database schema from one place to another, which put it another can be referred to as "publishing a database".

A complete answers lies here, but for now I've only tested Microsoft's official "database publishing wizard". Strangely, this wizard does not officially support SQL Server 2008 version yet.

How to upgrade an expired evaluation edition of SQL Server 2008

I recently tried to upgrade SQL Server 2008's evaluation edition to Developer Edition. Since it has been an expired copy, the upgrade although finished with success, the SSMS still kept popping up expiration message. I later did some research on Google and SQLServerCentral, just to find out that no edition upgrade actually upgrade the SSMS and there is some registry modification need to be done for a workaround. This article from Jonathan Kehayias gives a better step-by-step guide to change the registry and after the change is made, you can use the "Edition Upgrade" function from the "Maintenance" page that you can access from the SQL Server Installation Center. One thing to bear in mind is that you have to change the registry first and then run the Edition Upgrade. The sequence can not be reversed.

Two of the most important screen shots are as follows:

How to get Windows to manage wireless network

This is a strange issue that I met recently on my company's laptop that every time I wanna configure in the way that Windows is used to manage the wireless network, the tab "Wireless Networks" on the "Wireless Network Connection Properties" window simply disappears; even after several reboots, it refuses to reveal itself.



















A quick Google search redirects to a forum post and the solution is pretty simple too: just start the Wireless Zero Configuration service either manually or reset it as automatic.

"cannot drop database because it is currently in use"

A common mistake is usually made when executing the "drop database db_name" statement is forgetting to terminate the current session connected to the target database. A quick solution is to switch to master db and then drop it.

use master
drop database db_name

Most of the time, this can succeed. If the same error message still pops up, disconnect query analyzer or management studio from the SQL Server and reconnect to it and run the above t-sql command again.

Start from being a novice at SQL

This is the first post on this blog which will document the growth of my technological knowledge regarding MS Sql Server database management system. Since I don't have either the edge or the age advantage anymore, hopefully the road from a novice to a true professional can be shortened by my dedicated efforts. We'll see in the next couple of months how this blog is maintained and updated. Anyhow, I am still a technical person who is determined to shine no matter what I am made of. If anyone ever bumps into this blog while trying to google a technical solution to SQL problems, he/she is welcome to leave a footprint here which will be highly appreciated.