2009-11-13

Move databases from SQL 2005 to SQL 2008

I am awfully new at SQL server and this was a real headache for me. So I decided to document this experience so I never have to go through all those google pages again :)

Contents:
Step 1 - Making a backup of database.
Step 2 - Restoring databases.
Step 3 - Transfer of logins and passwords



Source: Server A - SQL 2005
Destination: Server B - SQL 2008

Step 1 - Making a backup of database.

On the source server: Open Microsoft SQL Server Management Studio

method a)
(preferable when backuping a single database)

expand ServerA -> Databases
right click on the database you want to backup and choose Tasks -> Back Up
Backup type: Full
Backup component: Database
now choose where you want to put your backup file - click Add and enter path and file name (you can enter share location as well for example \\Server_B\backups\database1.bak)
Click OK.

method b) (when backuping few, lots or all databases)

expand ServerA -> Management
right click Maintenance Plans and choose Maintenance Plan Wizard
on the first page of the wizard click Next
on the second page enter name for your backup plan, choose "Single schedule for the entire plan or no schedule", enter schedule setings or leave it on demand if you want to run it manualy, and click Next.
third page select "Back Up Database (Full)" and click Next
fourth page - Next
fifth page - choose which databases you want to backup, select "Create a backup file for every database" and select a location for backups (preferably share on the destination server \\Server_B\backups\)
sixth page - Next
and at last - Finish
after wizard finishes to create your maintenance plan - click Close
Now expand Maintenante Plans and you will see your maintenance plan listed there. Right click it and Execute.


Step 2
- Restoring databases.


On the destination server: Open Microsoft SQL Server Management Studio
expand ServerB, right click Databases and choose Restore Database
enter the name of database you are going to restore
To database: database1
then choose "From device" and browse for you database backup file
once you add it select it and press OK

Step 3 - Transfer of logins and passwords (http://support.microsoft.com/kb/918992/)

3.1 On source Server A open New Query in the Microsoft SQL Management Studio
paste and execute the following code:

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1 END SELECT @hexvalue = @charvalue GO IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS DECLARE @name sysname DECLARE @type varchar (1) DECLARE @hasaccess int DECLARE @denylogin int DECLARE @is_disabled int DECLARE @PWD_varbinary varbinary (256) DECLARE @PWD_string varchar (514) DECLARE @SID_varbinary varbinary (85) DECLARE @SID_string varchar (514) DECLARE @tmpstr varchar (1024) DECLARE @is_policy_checked varchar (3) DECLARE @is_expiration_checked varchar (3) DECLARE @defaultdb sysname IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR


SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO


this creates a stored procedure named sp_help_revlogin in ServerA/Databases/System Databases/Master/Programmability/Stored Procedures

3.2 Open New Query window again and run this code:
EXEC sp_help_revlogin


3.3 copy the outcome of the step 3.2 and paste it in the New Query window on destination Server B, execute it.

2009-11-07

Enable Remote Desktop remotely via Group Policy

Have you been in a situation where you need to remotely connect to some workplace and do some administrator stuff, but can not because Remote Desktop is not enabled and the user who is near that PC has no admin rights to enable Remote Desktop?


So here is what you do (Windows Server 2008 R2 in this case):
Connect to your domain controller -> Open Group Policy Management Editor
navigate to:
Computer Configuration > Administrative Templates > Windows Components > Remote Desktop Services > Remote Desktop Session Host > Connections
Enable: Allow users to connect remotely using Remote Desktop Services

Also you might need to add an exception to Windows Firewall, to do this via Group Policy navigate to:
Computer Configuration > Administrative Templates > Network > Network Connections > Windows Firewall > Domain Profile
Enable: Windows Firewall: Allow inbound Remote Desktop exceptions

Links: http://www.twistedethics.com/2009/01/06/how-to-enable-remote-desktop-via-group-policy/comment-page-1/

2009-10-02

Can't uninstall Exchange 2010 RC, must remove public folder replicas first.

When trying to uninstall Exchange 2010 RC I ecounter this situation

Mailbox Role Prerequisites

Failed

Error:

Uninstall cannot continue. Database 'Public Folder Database 1166479595': The public folder database "Public Folder Database 1166479595" contains folder replicas. Before deleting the public folder database, remove the folders or move the replicas to another public folder database. For detailed instructions about how to remove a public folder database, see http://go.microsoft.com/fwlink/?linkid=81409&clcid=0x409.Recommended Action: http://go.microsoft.com/fwlink/?linkid=30939&l=en&v=ExBPA.4&id=b6e3b32a-8848-46cb-9567-72288ac15f60

When trying to use cmdlet to remove public folder replicas
Get-PublicFolder -Server "\Non_Ipm_Subtree" -Recurse -ResultSize:Unlimited Remove-PublicFolder -Server -Recurse -ErrorAction:SilentlyContinue

I get the error:
"Multiple MAPI public folder trees were found".

What I did was opened ADSI Edit and went to
Configuration [.contoso.com]
CN=Configuration,DC=contoso,DC=com
CN=Services
CN=Microsoft Exchange


then in the details pane right clicked "CN=Public Folders" and changed parameter of "msExchPFTreeType" from "1" to "<not set>" (by choosing Edit and clicking Clear).

Then restarted Microsoft Exchange Information Store service and rerun cmdlets:

Get-PublicFolder -Server "\" -Recurse -ResultSize:Unlimited Remove-PublicFolder -Server -Recurse -ErrorAction:SilentlyContinue
and
Get-PublicFolder -Server "\Non_Ipm_Subtree" -Recurse -ResultSize:Unlimited Remove-PublicFolder -Server -Recurse -ErrorAction:SilentlyContinue

Ttried to uninstall Exchange server again. This time prerequisites passed and I uninstalled successfully.

Links: http://msexchangeteam.com/archive/2007/07/09/445967.aspx

2009-10-01

How to custom schedule Windows Server Backup on Windows Server 2008 R2

I was kinda pissed that Windows Server 2008 R2 Backup GUI had so little scheduling options,
you have to choose a "Once a day" bakcup or "More than once a day".

So to customize a shcedule to suite ones needs, simply create a backup with "Once a day" option, then in the Administrative Tools open Task Scheduler and navigate to:
Task Scheduler Library/Microsoft/Windows/Backup
there you should find your backup job, right click it and select Properties.
Go to Triggers tab, select trigger Daily and press Edit. Here you have more options for scheduling. Good luck.

2009-09-26

Outlook cannot connect to Exchange Server 2010 RC using POP3

Just installed Exchange Server 2010 RC and when trying to test it with my outlook in POP3 configuration I received this error:

"Task 'user@domain.com - Receiving' reported error (0x800CCC0F) : 'The connection to the server was interrupted. If this problem continues, contact your server administrator or Internet service provider (ISP).'"

The problem was that for some reason after installing Exchange Server 2010 RC, service Microsoft Exchange POP3 did not start automatically, so I had to open services.msc (via "run"), and start this service manually, also changed its startup type to Automatic for future. This solved the problem.

Outlook cannot connect to Exchange Server 2010 RC using IMAP

Just installed Exchange Server 2010 RC and when trying to test it with my outlook in IMAP configuration I received this error:

"Task 'Synchronizing subscribed folders for user@domain.com.' reported error (0x8004DF0B) : 'Outlook cannot synchronize subscribed folders for user@domain.com. Error: The connection to the server is unavailable. Outlook must be online or connected to complete this action. If you continue to receive this message, contact your server administrator or Internet service provider (ISP).'"

The problem was that for some reason after installing Exchange Server 2010 RC, service Microsoft Exchange IMAP4 did not start automatically, so I had to open services.msc (via "run"), and start this service manually, also changed its startup type to Automatic for future. This solved the problem.

Uninstalling Exchange Server 2010 RC, Mailbox role prerequisites error:

Tried to uninstall Exchange 2010 RC and encountered this error:
"Uninstall cannot continue. Database 'Mailbox Database 0186314254': This mailbox database contains one or more mailboxes or arbitration mailboxes. To get a list of all mailboxes in this database, run the command Get-Mailbox -Database . To get a list of all arbitration mailboxes in this database, run the command Get-Mailbox -Database -Arbitration. Before you can remove this mailbox database, you must disable, move, or remove user mailboxes and move arbitration mailboxes.
"

So I removed the user maibloxes, but had no idea how to remove arbitration mailboxes, after some googling here is what I did:
in Exchange Management Shell
Get-Mailbox -Arbitration | Remove-Mailbox -Arbitration -RemoveLastArbitrationMailboxAllowed
this should remove all arbitration mailboxes.

Or you can do the following
get-mailbox -arbitration | fl name, identity

After you get the ID of those mailboxes you can remove them one by one with the following command:
remove-mailbox -identity "domain.com/Users/SystemMailbox{1f05a927-563f-4671-8a1b-487764400889}" -arbitration -RemoveLastArbitrationMailboxAllowed

If all went smooth you can try uninstalling Exchange Server.

Links: http://social.technet.microsoft.com/Forums/en-US/exchange2010/thread/d058717e-bf70-4449-91c2-fa00b47d415f

2009-09-11

Microsoft File Transfer Manager queue keeps on restoring when you relaunch FTM

If you can't get rid of files in FTM queue try navigating to C:\Users\you_username\AppData\Roaming\Microsoft\File Transfer Manager
and delete file "ftmTransferList.txt". Next time you launch FTM they should not reappear.

Can't uninstall Exchange 2007 SP1 Mailbox role from Server 2008 R2

If you are getting error:
"An error occurred. The error code was 3221685466. The message was The service is already registered.."
while trying to uninstall Exchange 2007 SP1 Mailbox role from Server 2008 R2 (RC in my case)
try changing registry key value from "Uninstall" to "Install" located at
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Exchange\v8.0\Mailbox\
after that try uninstalling again.

2009-07-23

Cannot install printer, because Print Spooler service does not work.

When trying to add a printer you receive error message:
"Operation could not be completed. The Print Spooler service is not running"

Start->Run (Win+R)
type: services.msc
in the list find Print Spooler service, check for Startup Type (should be automatic) right click and choose Start.
If the service starts successfully you can try adding printer again.

If after a short time you receive error message similar to this:
"Spooler subsystem app has encountered a problem and needs to close.
"

Download FixPrintSpooler.msi from Microsoft, run it and follow the instructions.

More info here: http://support.microsoft.com/kb/324757

Cannot add Outlook Data File - Maximum file size exceeded.

If you're trying to add .pst data file to your outlook profile and get the following error
"The file "drive:/path_to_your_.pst/outlook.pst" has reached its maximum size. To reduce
the amount of data in the file, select some items that you no longer need and
then permanently delete them by pressing SHIFT + DELETE".

Obviously you can not delete any messages because you can't add the data file itself. This case should be only for Outlook 2003 without sp2 because it limits .pst file size to somewhat 2GB, however I encountered a situation where this message was generated by Outlook 2007.

Fastest workaround for this is to share the .pst file and add it to outlook 2007 on another computer in you local area network and delete some messages to lower the size of .pst file. That is if you have a network and another PC with outlook installed.

The other way is to download microsoft tool for shrinking .pst files, then fix the file with SCANPST.EXE ) (if you have outlook 2007 it will be located in "drive:\Program Files\Microsoft Office\OFFICE12") and then try adding the Data File again.

Cannot start Microsoft Office Outlook. Cannot open the Outlook window.

When launching Microsoft Outlook 2007 you get the following error message:
"Cannot start Microsoft Office Outlook. Cannot open the Outlook window."

This might help:
Start->Run (or Win+R)
then type the following: Outlook.exe /resetnavpane

If this does not fix your problem, you can always try System Restore.

2009-06-23

Unable to Import-Mailbox from .pst file to newly created mailbox on Exchange Server 2007 SP1

If you're unable to import .pst file to newly created mailbox using "Import-Mailbox" cmdlet check if the mailbox appeared in the Store. If it won't appear for a long time simply send an email for that mailbox and try importing again.

2009-06-22

Error when trying to Export-Mailbox to .pst file (or import-mailbox) on Exchange Server 2007 SP1

When trying to export mailbox to .pst file in Exchange Server 2007 SP1 using Exchange Management Shell command "Export-Mailbox" I have encountered an error:
"Failed to copy messages to the destination mailbox store with error: MAPI or an unspecified service provider. ID no: 00000000-0000-00000000.."

To solve this run this command in Exchange Management Shell
"Get-Mailbox Add-MailboxPermission -User "" -AccessRights FullAccess"
and try exporting again.

Cannot install maiblox role on Exchange Server 2007 SP1: An error occurred. The error code was 3221684229. The message was Access is denied

If you're trying to install Exchange Server 2007 SP1 on Windows Server 2008 R2 RC as did I and get the following results for Mailbox Role:

"Mailbox Role
Failed

Error:
An error occurred. The error code was 3221684229. The message was Access is denied.."

try running Setup.exe in Vista SP2 compatibility mode and it should be ok.

Cannot install Exchange Server 2007 SP1: The system cannot find the file specified

If you are getting this error when trying to install Exchange Server 2007 SP1
Error:
The system cannot find the file specified

"Warning:
An unexpected error has occurred and debug information is being generated: The system cannot find the file specified"

you may want to check C:\ExchangeSetupLogs\ExchangeSetup.log for errors.
If the missing file is Ldifde.exe you should install ADDS.
For Windows Server 2008 R2 open Server Manager go to Roles, click Add Roles and install Active Directory Domain Services
.
If you're installing Exchange 2007 Management Tools on Windows 7, simply copy the file ldifde.exe from your server to C:\windows\system32\ folder.

2009-06-09

Clean System Installation v0.6

List of most useful free programs for freshly installed OS.

Antivirus:
Microsoft Security Essentials (recommended)
Avast Home
Avira Personal

File archiver:
7zip 32-bit
7zip x64

Browser:
Firefox
Chrome
Opera
Safari

Flash Player:

IE
non IE

.pdf Reader:

Adobe Reader
Foxit Reader

Java plug-in:

Java

Silverlight:

Silverlight

Burner:

ImgBurn

Imaging:

Photo Gallery
Picasa
Irfan View
FastStone

Codeck Pack:

cccp
K-Lite Codec Pack

Video:

VLC

Audio:

Winamp (recommended)
aimp2
Songbird

Malware Removal:
Malicious Software Removal Tool 32-bit
Malicious Software Removal Tool x64

Messaging:

Google Talk
Skype
Windows Live Messenger

Torrents:

Micro Torrent

Virtual Drive:

VirtualClone Drive

Mail:
Windows Live Mail
Thunderbird
Zimbra

Mail Spam Filters:
Spamihilator (works with Outlook 2000/XP/2003/Express, Eudora, Mozilla Thunderbird, IncrediMail, Pegasus Mail, Phoenix Mail, Opera, etc…)
Cactus Spam Filter (this spam stopper integrates seamlessly with all e-mail clients that use POP3)

System Maintenance:

CCleaner (uninstaller, registry cleaner, temp files cleaner and more)
Revo Uninstaller

HDD Maintenance:

Smart Defrag

Recover lost data (deleted files, formatted data)
Recuva

2009-01-27

Calculate HDD Input Outputs per second (IOps)

If you're looking to buy HDD and you need to know what IOps it can sustain, here is a formula you could use:
IOps = 1000[msec] / (Average seek time [msec] + Average Latency [msec])

For example let's calculate IOps for this IBM SAS HDD
1000 / 4.7 + 3 = 129.87 IOps

Links:
http://blogs.smugmug.com/don/2007/10/08/hdd-iops-limiting-factor-seek-or-rpm/
http://technet.microsoft.com/en-us/library/bb897498.aspx

2009-01-24

Install process randomly crashes when trying to install anything that uses an MSI based installer on Windows 7 beta

Solution taken from Windows 7 Action Center

Solve a problem with Windows 7 beta

An issue with the Customer Experience Improvement Program (CEIP) client in Windows 7 beta is causing Explorer and some MSI-based installers to stop working properly.

To solve this problem, follow these steps:


  1. Click the Start button , click All Programs, and then click Accessories.

  2. Right-click Command Prompt, and then click Run as administrator. In the User Account Control window, verify that Program name is Windows Command Processor, and then click Yes.

  3. In the Administrator: Command Prompt window, type or paste the following text at the prompt:

    reg delete HKLM\SOFTWARE\Microsoft\SQMClient\Windows\DisabledSessions /va /f

  4. Press Enter to install the solution.

  5. If The operation completed successfully displays, close the Administrator: Command Prompt window to complete this procedure. If "ERROR: Access is denied" displays, repeat this procedure from the top, making sure you clicked Run as administrator in step two.



links:
http://chris123nt.com/2009/01/18/sqm-client-causing-crashing-in-windows-7/

2009-01-23

Windows 7 Hotkey list

General
Win+Up = Maximize
Win+Left = Snap to left
Win+Right = Snap to right
Win+Shift+Left = Jump to left monitor
Win+Shift+Right = Jump to right monitor
Win+Home = Minimize / Restore all other windows
Win+T = Focus the first taskbar entry
Pressing again will cycle through them, you can can arrow around.
Win+Shift+T = cycles backwards.
Win+Space = Peek at the desktop
Win+G = Bring gadgets to the top of the Z-order
Win+P = External display options (mirror, extend desktop, etc)
Win+X = Mobility Center (same as Vista, but still handy!)
Win+#
(# = a number key) Launches a new instance of the application in the Nth slot on the taskbar.
Example: Win+1 launches first pinned app, Win+2 launches second, etc.
Win + +
Win + -
(plus or minus key) Zoom in or out.

Explorer
Alt+P = Show/hide Preview Pane

Taskbar modifiers
Shift + Click on icon = Open a new instance
Middle click on icon = Open a new instance
Ctrl + Shift + Click on icon = Open a new instance with Admin privileges
Shift + Right-click on icon = Show window menu (Restore / Minimize / Move / etc)
Note: Normally you can just right-click on the window thumbnail to get this menu
Shift + Right-click on grouped icon = Menu with Restore All / Minimize All / Close All, etc.
Ctrl + Click on grouped icon = Cycle between the windows (or tabs) in the group

Links:
taken from http://brandonlive.com/

2009-01-21

Remote Desktop not allowed to use saved credentials

When trying to use saved credentials in Remote Desktop Connection you might receive this message:

Your credentials did not work
Your system administrator does not allow the use of saved credentials to log on to the remote computer terminal.server.com because its identity is not fully verified. Please enter new credentials.
(screenshot)

To be able to use saved credentials in this situation you need to do the following:

1. Open Group Policy Editor via cmd -> gpedit.msc (screenshot)
2. Navigate to Local Computer Policy\Computer Configuration\Administrative Templates\System\Credentials Delegation\
3.Open Setting Allow Delegating Saved Credentials with NTLM-only Server Authentication, set it to Enabled click on button Show... and in Show Contents window add Value TERMSRV/terminal.server.com. Close all windows by pressing OK. (screenshot)
4. Run cmd and enter gpupdate command to update your policy. (screenshot)

Now you should be able to use your saved credentials.

note:
these steps were performed on Windows 7 beta, but probably it will stay the same in final release and my guess is, it is the same in Vista (too lazy to check).

2009-01-10

Huge lags in Fallout 3 on Hi-End PC

If you experience huge lags in Fallout 3 from the moment you start the game and menu screen loads, try uninstalling Total Media Theater if you have installed one, worked for me.

PC specifications:
Asus HD 4870X2
Core i7 920
Creative SB X-Fi Titanium
6 GB of DDR3 at 1066 Mhz

Mouse Delay in Dead Space

If you experience mouse delay in menu and game try turning off Vertical Sync. (screenshot)