Posts

Sometimes you have to copy all user databases from a source server to a destination server. Copying from development to test could be one reason, but I’m sure there are others. Since the question came up on the forums at SQLServerCentral, I decided to modify a script I published some months ago to accomplish this task. Here is the code: ## ============================================= ## Author: Gianluca Sartori - @spaghettidba ## Create date: 2013-10-07 ## Description: Copy user databases to a destination ## server ## ============================================= cls sl "c:\" $ErrorActionPreference = "Stop" # Input your parameters here $source = "SourceServer\Instance" $sourceServerUNC = "SourceServer" $destination = "DestServer\Instance" # Shared folder on the destination server # For instance "\\DestServer\D$" $sharedFolder = "\\DestServer\sharedfolder" # Path to the shared folder on the destination server # For instance "D:" $remoteSharedFolder = "PathOfSharedFolderOnDestServer" $ts = Get-Date -Format yyyyMMdd # # Read default backup path of the source from the registry # $SQL_BackupDirectory = @" EXEC master. [Read More]

Oooooops! I exchanged some emails about my post with Jonathan Kehayias and looks like I was wrong on many of the points I made. I don’t want to keep misleading information around and I definitely need to fix my wrong assumptions. Unfortunately, I don’t have the time to correct it immediately and I’m afraid it will have to remain like this for a while. Sorry for the inconvenience, I promise I will try to fix it in the next few days. [Read More]

Some months ago I posted a method to overcome some quirks in the MDW database in a clustered environment. Today I tried to upgrade that clustered instance (in a test environment, fortunately) and I got some really annoying errors. Actually, I got what I deserved for messing with the system databases and I wouldn’t even dare posting my experience if it wasn’t cause by something I suggested on this blog. [Read More]

Yesterday I upgraded my laptop to Windows 8.1 and everything seemed to have gone smoothly. I really like the improvements in Windows 8.1 and I think they’re worth the hassle of an upgrade if you’re still on Windows 8. As I was saying, everything seemed to upgrade smoothly. Unfortunately, today I found out that SQL Server services were gone. My configuration manager looked like this: My laptop had an instance of SQL Server 2012 SP1 Developer Edition and the windows upgrade process had deleted all SQL Server services but SQL Server Browser. [Read More]

SQL Server logins can implement the same password policies found in Active Directory to make sure that strong passwords are being used. Unfortunately, especially for servers upgraded from previous versions, the password policies are often disabled and some logins have very weak passwords. In particular, some logins could have the password set as equal to the login name, which would by one of the first things I would try to hack a server. [Read More]

Now that my SQL Server 2014 CTP1 virtual machine is ready, I started to play with it and some new features and differences with the previous versions are starting to appear. What I want to write about today is a T-SQL enhancement to DDL statements that brings in some new interesting considerations. SQL Server 2014 now supports a new T-SQL syntax that allows defining an index in the CREATE TABLE statement without having to issue separate CREATE INDEX statements. [Read More]

The day has come at last! SQL Server 2014 CTP1 is available for download! You can now start playing with Hekaton and all the exciting features included in this version. Mi suggestion is to create a new virtual machine for your playground: this CTP version won’t install on a machine with other installations of SQL Server. If you prefer it and you have a subscription, you can also try SQL Server 2014 on Windows Azure. [Read More]

As a DBA, you probably have your toolbox full of scripts, procedures and functions that you use for the day-to-day administration of your instances. I’m no exception and my hard drive is full of scripts that I tend to accumulate and never throw away, even if I know I will never need (or find?) them again. However, my preferred way to organize and maintain my administration scripts is a database called “TOOLS”, which contains all the scripts I regularly use. [Read More]

Recently I had to assess and tune quite a lot of SQL Server instances and one the things that are often overlooked is the location of the system databases. I often see instance where the system databases are located in the system drives under the SQL Server default installation path, which is bad for many reasons, especially for tempdb. I had to move the system databases so many times that I ended up coding a script to automate the process. [Read More]