Friday, August 31, 2012

Create table from stage table on the fly

If you have a script that does nightly drop and recreate of tables, your drop query should look something like this:
IF OBJECT_ID('dbo.StageTable', 'U') IS NOT NULL
  DROP TABLE dbo.StageTable;
What if you have to create a bunch of destination tables from stage table on the fly? You can use:
IF OBJECT_ID('dbo.DestinationTable') IS NULL
  SELECT * INTO dbo.DestinationTable FROM dbo.StageTable WHERE 1=0
The above creates the table structure with no data in it and you can then do your inserts as necessary. This is particularly useful when you have to wade through thousands of tables in a database using the GUI, select desired table and generate a create script for it.

Monday, August 27, 2012

Remote database backups and restores using SQLCMD

One of the clients I work for has a lot of servers in countries like Somalia, Kenya, Afghanistan with what we call 'field databases' on them. The users often request for a . Though these servers are on the same vpn, it is often very painful to RDP to the remote server, take a backup, compress and upload to FTP (or copy to a network share). On a busy day, we usually have about 10 such database copy requests involving ten different servers. Automation is the way to go right? So we came up with the below script which uses linked server and OPENQUERY to make database copying process a bit easier.

declare @dbname nvarchar(30)
set @dbname = 'DATABASENAME'
declare @servername nvarchar(50)
set @servername = 'FIELDSERVER'
declare @SrcBackupLocaction nvarchar(100)
set @SrcBackupLocaction = '\\' + @servername+ '\backups\'
declare @DestBackupLocaction nvarchar(100)
set @DestBackupLocaction = '\\HOMESERVER\BackupsFromField\'
print @DestBackupLocaction 
declare @backupFileName nvarchar(60)
declare @backupStmt nvarchar(500)
declare @compactStmt nvarchar(500)
declare @UncompactStmt nvarchar(500)
declare @copyStmt nvarchar(500)
--declare @destFile nvarchar(100)
declare @backupNameFullPath nvarchar(500)
declare @mdf nvarchar(500)
declare @ldf nvarchar(500)
declare @dblogName nvarchar(60)

--Declare @RestoreDBName nvarchar(30)
select @backupFileName = @dbname + '_'+ (Select replace(CONVERT(varchar(10),t,10),'-','') as t from openquery(FIELDSERVER, 'select getdate() as t'))+'_'+ @servername
set @backupStmt = 'BACKUP DATABASE ' + @dbname+ ' to disk=N''' + @SrcBackupLocaction + @backupFileName + '.bak'''
print @backupStmt 
print 'Backing up database to ' + @backupFileName

print @SrcBackupLocaction
print @DestBackupLocaction

set @backupNameFullPath = N'E:\Backups\BackupsFromField\'+ @backupFileName +'.bak'
set @mdf = N'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\' + @backupFileName + '.mdf'
set @ldf = N'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\' + @backupFileName + '_1.LDF'
set @dblogName = @dbname + '_log'

print @backupNameFullPath 
print @mdf
print @ldf
print @dblogName

--Backup database
 exec FIELDSERVER.master.dbo.sp_executesql @backupStmt
print 'database BackUp completed '
print '--------------------------'

--Compact the backed up database
--set @compactStmt = N'Compact /C '+ @SrcBackupLocaction + @backupfilename + '.bak'
set @compactStmt = N'"E:\Program Files\7-Zip\7z.exe" a -tzip '+ @SrcBackupLocaction + @backupfilename + '.zip ' + @SrcBackupLocaction + @backupfilename + '.bak -mx5'
print @compactStmt 
 Exec FIELDSERVER.master.dbo.xp_cmdshell @compactStmt

print 'database compressed successfully'
print '--------------------------'

--Copy compacted backup file
set @copyStmt = N'Copy '+ @SrcBackupLocaction + @backupfilename + '.zip ' + @DestBackupLocaction + ' /V /Y /Z'
print @copyStmt 
 Exec FIELDSERVER.master.dbo.xp_cmdshell @copyStmt

print 'Copied Compressed file copied successfully'
print '--------------------------'

--UnCompact the copied zip file
--set @compactStmt = N'Compact /C '+ @SrcBackupLocaction + @backupfilename + '.bak'
set @UncompactStmt = N'"E:\Program Files\7-Zip\7z.exe" e -y -o' + @DestBackupLocaction + ' '+ @DestBackupLocaction + @backupfilename + '.zip'
print @UncompactStmt 
 Exec dbo.xp_cmdshell @UncompactStmt

print 'Unzip the file successfully'
print '--------------------------'

RESTORE DATABASE @backupfilename FROM  DISK = @backupNameFullPath
WITH  FILE = 1,  
MOVE 'DataFile' TO @mdf,  
MOVE 'LogFile' TO @ldf,  NOUNLOAD,  STATS = 10

print 'Database ' + @dbname + ' created successfully'
print '-------End of Procedure-------------------'


Nice isn't it? But there is a catch, you need to add the field servers as linked servers on HomeServer, RDP to the field server, open management studio, connect to the HomeServer and then execute this script to achieve the desired result. If for some reason, the network connection times out while the compressed backup is being copied, we would have to go back to the manual mode to complete the rest of the steps(some of these servers are on 256 kbps internet connections and are often fickle). What if a user needs a database urgently? What if you can't remote into the field server at all? Then I found SQLCMD. Here is what I do these days - Log on to the HomeServer (which is a decent server with better resources and which is located in US) Open command prompt and connect to the remote server using SQLCMD

You can get the help about SQLCMD commands by using sqlcmd /? in command prompt. Since we use Windows authentication and I am a domain admin, I just specify the server name to connect to and I am in. Then issue the backup command. All of our field servers are alike in drive and folder structure, so all I need is the name of the database to copy. If there are long and funky names you can choose from:

SELECT name FROM sysdatabases

Then, issue the backups command:

BACKUP DATABASE [My_Field_Database] TO  DISK = N'E:\MyFieldServer\BackupPath\MyFiledDB_082712.bak' 

We use standard edition on field servers so you won't see COMPRESSION in the backup query :)

 I know that 7zip is installed on all the field servers and I know the path. So I will now use sqlcmd and xp_cmdshell to compress the backup so that it is easier for me to copy to HomeServer: I know xp_cmdshell is a security threat etc - so lets enable it, use it and then disable it. No harm done.
exec sp_configure 'show advanced options', 1
exec sp_configure 'xp_cmdshell', 1

--Lets now compress that backup using xp_cmdshell
exec xp_cmdshell '"E:\Program Files\7-Zip\7z.exe" a -tzip E:\YourFieldBackups\FileName.7z E:\YourFieldBackups\BackupName.bak -mx5'

-- Okay, we are done. Lets disable the cmdshell dude

exec sp_configure 'show advanced options', 1
exec sp_configure 'xp_cmdshell', 0
The rest is easy. You know where the compressed backup file is. You can now use xcopy to move the file to HomeServer
xcopy \\remoteserver\bacups\compressedfile.7z e:\backups\
I like this method a lot! I just need to put these steps together in a script and add some error handling etc. Thats a task for the near future!

Saturday, August 25, 2012

Drop tables

I was reading Itzik Ben-Gan's T-SQL Fundamentals book and I learned something that I didn't know so far. You can drop multiple tables in a single drop query. I know, its stupid. I am monument of stupidity right now!
drop table dbo.Orders, dbo.Employees

Friday, August 24, 2012


For relational databases, the NULL is the unknown or it can simply mean that the value is absent. Just like in real world you can't add something to an unknown quantity and make it a known one in the relational world. So NULL is like the evil queen from fairy tales that turns every known value to unknown if not properly handled. Lets see
SELECT 'binyoga' + NULL;
Results a NULL. It seems this is the reason that some developers don't like allowing NULLs in their databases. Anyways, we all know that an unknown is not equal to another unknown, right? Even if it is, it is still unknown. SQL Server knows that too.
 SELECT 'Whoa, I cracked the unknown'
 SELECT 'Nope. Unknown is not equal to unknown :(' 
So how do you deal with this? Enter IS NULL.
 SELECT 'Yes. It is NULL. You don''t need my help to figure'
 SELECT 'No, the world is ending. NULL is NOT NULL anymore'
You can use this guy to find out all the people that doesn't have Mr/Mrs titles in AdventureWorks database.
SELECT LastName + ',' + FirstName AS Name
FROM Person.Person
-- I wonder why 18K people have no titles!
Okay, so you push data to reports people and they don't like NULLs. How do you handle that? Enter ISNULL() function. More about that later. Trivial and basic stuff - yes. But blogging about it is a way to think again about it. Where did I get this info from? Like I said, I have been reading SQL Server Bible by Paul Nielsen and my next few posts are likely to be influenced by the book. Its an aswesome book, btw!

Thursday, August 23, 2012

Why can't I drop this table?

Cannot DROP TABLE 'dbo.PleaseDropMe' because it is being referenced by object '_dta_mv_25'.
Looking at the _dta_ prefix, it looked like a view created from database engine tuning advisor. The view was created with SCHEMABINDING . When a view is created with SCHEMABINDING specified, the underlying tables can not be modified in a way that would break the view. Explanation here. So in this case I first had to drop the view and then the table

 This is a nice feature if you would like to restrict some of your important tables from being 'accidentally' dropped. Lets test it:

IF OBJECT_ID('dbo.DropTest', 'U') IS NOT NULL
DROP TABLE dbo.DropTest;

CREATE TABLE dbo.DropTest (
 c1 int primary key,
 c2 char(2)

-- Lets create a view

SELECT c1, c2 FROM dbo.DropTest

--Lets try and drop the table

DROP TABLE dbo.DropTest

You'll see the error

Msg 3729, Level 16, State 1, Line 1 Cannot DROP TABLE 'dbo.DropTest' because it is being referenced by object 'DropTestView'.

Sunday, August 19, 2012

SQL Server Unattended installations

I was aware of the configuration.ini file that gets created during the normal GUI installation of SQL Server. This configuration file can be changed and reused to perform command line/remote installations. But in order to generate that initial file, you have to go through the GUI steps initially. Thats what I used to think. I have been reading SQL Server Bible by Paul Nielsen (Its a great book, btw. I wish I read it a couple of years ago) and I came across an interesting piece of info about unattended installations - Microsoft ships a template.ini file that you can use to kick off remote installations in the installation dvd. 

There is a long (very long!) article on BOL about command line and unattended installs:

There are two ways to go about it

You run the setup.exe file from command prompt specifying the components, passwords you want to install, passwords etc:

Start /wait <CD or DVD Drive>\servers\setup.exe /qb INSTANCENAME=<InstanceName> ADDLOCAL=All PIDKEY=<pidkey value with no "-"> SAPWD=<StrongPassword> SQLACCOUNT=<domain\user> SQLPASSWORD=<DomainUserPassword> AGTACCOUNT=<domain\user> AGTPASSWORD=<DomainUserPassword> SQLBROWSERACCOUNT=<domain\user> SQLBROWSERPASSWORD=<DomainUserPassword>

Use the template.ini file to create a configuration.ini file and kick off the install

setup.exe /settings <full path to your .ini file>


setup.exe /settings C:\binyoga\sqlinstall.ini

You may use \qn switch for a silent installation with no dialogs and \qb switch if you can live with progress dialogs.

Click here for a sample template.ini file