Busy conference fall!

Leave a comment

The summer is coming to an end. It was a productive summer this year. A lot of diving and orca whatching. And I even had time for a trip to Microsoft before rushing in to the conference season 🙂

I’m speaking at SQL Saturdays in Cambridge and Katowitze so far, apart from arranging the SQL Saturday Gothenburg for the third time. This year the Gothenburg day is in a new place, the Lindholmen Science park. I hope that will be good for us all.

Summer and SQL Saturday with precon

Leave a comment

Summer is approaching and with that the long awaited time to schedule all sessions for the SQL Saturday in Gothenburg. This year we will have even more intresting sessions than last year and also a precon. The precon tracks are already out and the schedule will be set at the end of this week. The earlybird discount is running out, so grab your tickets soon!

And most of all: have a wonderful summer and be prepaired in August. I know I will…

Two more SQL Saturday opportunities

Leave a comment

Right when I was prepairing my session for the #sqlsat525 in Rheinland, I got notified that one of my sessions was selected for the #sqlsat520 in Cambridge.

I’m happy, humbled and a bit proud to have been given this exciting assignment. If you are the least bit interested in SQL Server, you really have to join one of the (totaly free) SQL Saturdays all over the globe. Especially considering the imminent SQL Server 2016 launch.

SQL Saturday #536 PRECON!

Leave a comment

The second SQL Saturday #536 in Sweden is taking place on August 27. The location is at the same place as before (Scandic Opalen), but we have some exciting news.

There will be 2 amazing pre-conference sessions this year. The precon info is located at http://sqlweekday.azurewebsites.net/ and the session links will take you directly to EventBrite, where the tickets sale occurrs.

Uwe Ricken will talk about how to Analyze and tune your SQL Server like a PRO

Mark Broadbent will deliver a Real World SQL Server High Availability Masterclass

TechEd Europe 2014

Leave a comment

I have been selected to staff at TechEd in Barcelona. So if you are there, go to the Data Platform Booth. That’s where I’ll be standing answering questions all day long. I’m also representing SQL Server at the “Ask the experts” session once again. That’ll be really exciting! Before that I hope to make it in time for the SQL Server Saturday that also is in Barcelona.

Did you attend any Microsoft class and need the lab files?

Leave a comment

Well, they are a bit difficult to get from the virtual machines, and almost impossible if you have been using LabsOnline.

Microsoft has compiled a page with a lot of lab files and setup scripts here:

https://www.microsoft.com/learning/en-us/companion-moc.aspx

I guess they are pretty useless without the lab instructions, but feel free to download them 🙂

And speaking of download… The SQL Server sample database location is still codeplex…

http://msftdbprodsamples.codeplex.com/

Happy sampling

Another session selected

Leave a comment

This time in Oslo. (http://sqlsaturday.com/317/eventhome.aspx)
I hear Norway is nice this time of the year, and my aunt was eaten by a moose…
No seriously 🙂 My session is only one of many and all of them are worthy your attendance. There are still room, last time I checked so go!!!

Speakers dinner on the way

Leave a comment

I was selected as a speaker at the SQL Saturday #275 in Copenhagen, Denmark. Now I’m at the hotel, polishing the presentation while waiting for the speakers dinner.

<yoda>A nice tradition, the speakers dinners are. </yoda>

Hopefully a lot of people will join tomorrow for a day full of SQL Server stuff.

Links and downloads will show up after the conference…

SQL Saturday Homepage

Restore Master

1 Comment

You’ve just rebuilt your master database. You discover that your other databases are gone, all logins are missing, pulse is rising…

If you take regular backups of your system databases (master and msdb primarily) you have nothing to worry about. If your database files are on undamaged drives you are even better up.

To get all the logins back and all of your user databases “re-attached” you only need to restore your master database. And restoring the msdb database gets you the jobs, schedules and alerts back as well.

To tell the truth: This is not something you do every day, it may even be a bit scary. So here is a simple way to restore your master database, that works in SQL Server 2008, 2008 R2, 2012 and in 2014.

NOTE: commands are written against the default instance (named MSSQLSERVER). If you have a named instance, you’ll have to find the name of it first…

1. Start an elevated command prompt.

2. Run : net stop mssqlserver

3. Navigate to your instance’s “binn” folder (i.e. “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn”)

4. Run : sqlservr.exe -m -c -s MSSQLSERVER
This starts sqlservr (the server executable) in single user mode (-m) as a console/not service application (-c). And the instance name is MSSQLSERVER (the default instance)

!I got a warning from Windows built in firewall at this step. It’s perfectly OK to ignore, and NOT let the application communicate on networks – we’re local so the restore will work anyway.

5. Start ANOTHER Command prompt – DO NOT close the other one.

6. Run : sqlcmd -s. –E
SQL Server command tool logs in to the default instance, locally (-s.) with Windows Authentication (-E)

7. 1>restore database master from disk = ‘full path to backup file ‘
2>GO

Your other window should now be back at the command prompt. Database restored.

Since the method uses only console tools, it works fine on servers with sparse UI possibilities Ler

Rebuild Master

2 Comments

The master.mdf is corrupt. The disk failed… Powerfailure? This never occurs at a convenient time. The ERRORLOG-file has a message similar to this.

Error 2(failed to retrieve text for this error. Reason: 15100) occurred while opening file
‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf’
to obtain configuration information at startup.

This is of course easily fixed. It’s only to resore the backup we have of all our system databases. Just one small problem… SQL Server doesn’t start.

Time to rebuild the system databases. But first : Documentation!

  1. Look for “Authentication mode is MIXED.” (or “Authentication mode is WINDOWS-ONLY.” ) Write it up.
  2. Document what Collation you want. If you want “same as before”, just forget it. Otherwise write the new Collation on the same piece of paper. (I’m documenting “Finnish_Swedish_CI_AS”)
  3. How do you connect to SQL Server? If you have a backslash somewhere in your servername, you have a named instance. (connecting to COMPUTER\FINANCE gives you the instancename FINANCE) However if you only use the servername, you probanly have a default instance, in which case the instancename is MSSQLSERVER – Onto the paper!

 

Command:

Same syntax since SQL Server 2008. Still works in SQL Server 2014 (CTP2) and has no signs that it would stop working:

Setup.exe /ACTION=REBUILDDATABASE
/INSTANCENAME=MSSQLSERVER  
/SQLSYSADMINACCOUNTS=win7x64pro\mikael 
/SAPWD=P@ssw0rd
/SQLCOLLATION=Finnish_Swedish_CI_AS

  • ACTION – obviously
  • INSTANCENAME – the instancename documented in item 3.
  • SQLSYSADMINACCOUNTS – Windows account(s) who will be sysadmin of the server.
  • SAPWD – This parameter MUST be present if item 1 states the server started with MIXED autentication.
  • SQLCOLLATION – from item 2.
  • QUIET – I recommend using this. Saves some clicking.

OK, now you’ll have to run this command in an elevated command prompt. That is the command prompt starting in C:\Windows\System32 instead of in your user profile. Then it’s only to navigate to the folder of “setup.exe” and run the command line. All words are typed on one line BEFORE hitting ENTER!

If your installation is made with C:\Program Files\ as the root, the setup.exe can be found here:

SQL 2008 – C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release\Setup.exe

SQL 2008 R2 – C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\Setup.exe
(I found another setup.exe in the same folder as for SQL 2008, but running that file generated errors instead of databases…)

SQL 2012 – C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012\Setup.exe

SQL 2014 CTP2 – C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQL14CTP2\Setup.exe

and my guess is that RTM of SQL 2014 will use the same path as before:
C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014\Setup.exe 
or potentially 
C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQL14\Setup.exe

 

For all Swedish speaking readers, please check out SQL Server på svenska

Older Entries