Archive

Posts Tagged ‘SQL 2008 R2’

Free BizTalk Message Archiving – SQL / File

November 18, 2011 Leave a comment

Most of the common problem building solutions in BizTalk is how to create a lightweight archiving solution.

Below is the open source solution that supports both File and/or SQL archiving.

http://biztalkarchiving.codeplex.com/

Advertisements

SQL Server 2008 R2 Logical name truncation during backup

September 13, 2011 1 comment

We’re currently upgrading  an existing solution to use SQL Server 2008R2 from SQLServer 2005, the solution autogenerates database, performs couple of operations afterwhich it will create a backup.

During the restoration process that’s where we encountered the error:

Logical file ‘<database_name>’ is not part of database ‘<database_name>’. Use RESTORE FILELISTONLY to list the logical file names. RESTORE DATABASE is terminating abnormally.

So we take a peek on the generated backup file and discovers that the last letter of logical filename is being truncated. It happens when the logical name of the database is renamed before creating a backup.

You can try it using this script: (it works on SQL Server 2008R2 without any updates):

 USE [master]
GO

CREATE DATABASE [Test]
GO
ALTER DATABASE [Test] SET RECOVERY SIMPLE
GO

— Rename Logical Files and Start Backup
ALTER DATABASE [Test]
MODIFY FILE
(NAME = [Test], NEWNAME=’Test_New’)
GO

ALTER DATABASE [Test]
MODIFY FILE
(NAME = Test_log, NEWNAME=’Test_New_Log’)
GO

— Backup Working Copy
BACKUP DATABASE [Test] TO  DISK = N’E:\Test.bak ‘ WITH NOFORMAT, NOINIT,  NAME = N’Test_New’, SKIP, REWIND, NOUNLOAD,  STATS = 10
GO

Message:

The file name ‘Test_New’ has been set.
The file name ‘Test_New_Log’ has been set.
13 percent processed.
22 percent processed.
31 percent processed.
40 percent processed.
53 percent processed.
62 percent processed.
71 percent processed.
80 percent processed.
94 percent processed.
Processed 176 pages for database ‘Test’, file ‘Test_Ne’ on file 2.
100 percent processed.
Processed 2 pages for database ‘Test’, file ‘Test_New_Lo’ on file 2.
BACKUP DATABASE successfully processed 178 pages in 0.027 seconds (51.486 MB/sec).

 Question is how did the QA guys missed this part,  it was working prior to SQL Server 2008R2.

A fix is released to resolve this issue.

Or with workaround:

To work around this issue, use one of the following two methods:

  • After the logical file name is modified, run the following statements to restart the database:
    ALTER DATABASE <database name> SET OFFLINE
    ALTER DATABASE <database name> SET ONLINE
  • When you modify the logical file name, append a dummy space character in the new logical name. For example, if you want to change the logical name to DB_modified, use the following statement to modify the logical file name:
    ALTER DATABASE <database name> MODIFY FILE
     (NAME=N'<database name>', NEWNAME='DB_modified ') -- Append ' ' to NEWNAME

SQL Login not working in SQL 2008R2/2005

August 19, 2011 1 comment

From time to time I see this problem reoccurring, suppose you correctly setup an SQL Login for a certain account and when you tried to login it throws: Login failed for ‘RPAULO_WebUser’ (Microsoft SQL Server, Error: 18456) you might want to check whether the sql server supports mixed mode by going to SQL Server Properties of the server (connect via RDP).

Note that updating the settings would require a service restart to take effect.

SQL Server 2008 R2 – Error Code 2337 while installing on Virtual PC 2007

July 20, 2011 Leave a comment

While preparing a new VPC image for our new development standards using BizTalk 2010, I’ve encountered an error  during the installation of SQL Server 2008 R2 with error code 2337;  even though the service is installed when I tried to start the SQL Service it throws out a faultexception about sqlsrv.exe.

As it turns out the problem lies with the Capture ISO feature of the Virtual PC 2007 itself, so to solve this I’ve used 7-Zip to extract the contents of iso file then shared the folder and ran it from there.

%d bloggers like this: