Archive

Archive for the ‘DBA Task’ Category

Logical file ‘XYZ_Log2′ is not part of database ‘XYZ’. Use RESTORE FILELISTONLY to list the logical file names.


I was restoring Databases as a routine monthly job. Suddenly in between I came across an error which didn’t let me restore a Database. I restored other Databases and left this one to do at the end. Finally I picked it up again and saw that some new kind of error I was facing. The error message says to use “RESTORE FILELISTONLY”, I check the syntax on MSDN and found that this Database had two log files (LDF) earlier, but now configured for only one. And as I was using the same old script to Restore it, I was getting this error.

Let’s try to reproduce it in a standalone box:

–> I took a backup of AdventureWorks2012 Database on my machine.

I created the RESTORE script and added another line of log file at line 8 below:

USE [master]
GO

RESTORE DATABASE [AdventureWorks2012] 
FROM DISK = N'E:\Softwares\MS_bits\AdventureWorks2012.bak' WITH  FILE = 1,  
MOVE N'AdventureWorks2012_Data' TO N'E:\MSSQL11\DATA\AdventureWorks2012_Data.mdf', 
MOVE N'AdventureWorks2012_Log' TO N'E:\MSSQL11\Log\AdventureWorks2012_Log.ldf', 
MOVE N'AdventureWorks2012_Log2' TO N'E:\MSSQL11\Log\AdventureWorks2012_Log2.ldf', 
NOUNLOAD,  REPLACE,  NOUNLOAD,  STATS = 5
GO

On executing I got an error as expected, shown below:

Msg 3234, Level 16, State 2, Line 1
Logical file ‘AdventureWorks2012_Log2′ is not part of database ‘AdventureWorks2012′. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

As suggested in the Error Message, I used the “RESTORE FILELISTONLY” command to check what all files this Database is using:

RESTORE FILELISTONLY from disk=N'E:\Softwares\MS_bits\AdventureWorks2012.bak'

And I could see only two, one MDF & one LDF, as shown below:

LogicalName		PhysicalName					Type	FileGroupName
AdventureWorks2012_Data	E:\MSSQL11\DATA\AdventureWorks2012_Data.mdf	D	PRIMARY
AdventureWorks2012_Log	E:\MSSQL11\Log\AdventureWorks2012_log.ldf	L	NULL

So, I removed the extra LDF log file option from the RESTORE script, as shown below:

RESTORE DATABASE [AdventureWorks2012x] 
FROM  DISK = N'E:\Softwares\MS_bits\AdventureWorks2012.bak' WITH  FILE = 1,  
MOVE N'AdventureWorks2012_Data' TO N'E:\MSSQL11\DATA\AdventureWorks2012_Data.mdf', 
MOVE N'AdventureWorks2012_Log' TO N'E:\MSSQL11\Log\AdventureWorks2012_Log.ldf', 
--MOVE N'AdventureWorks2012_Log2' TO N'E:\MSSQL11\Log\AdventureWorks2012_Log2.ldf', 
NOUNLOAD,  REPLACE,  NOUNLOAD,  STATS = 5
GO

The above statement executed fine and the Database restored successfully.

So, I learnt a new thing here about the “RESTORE FILELISTONLY” command. This command tell us what all Database files (MDF, NDF, LDF) a Backup file contains without actually Restoring the Database.

Disable/Enable multiple SQL Jobs at once


Seems to be a simple topic. But yes when it comes to do these type of tasks we tend to recall the syntax and end up searching internet (bing/google) for the solution.

–> Disabling a single SQL Job can be done simply through SSMS. Right click on the SQL Job and select Disable. To enable it back simply select Enable for a disabled Job.

This can also be done by a TSQL query as shown below:

USE msdb;
GO

-- Disable a SQL Job:
EXEC dbo.sp_update_job
    @job_name = N'syspolicy_purge_history',
    @enabled = 0 ;
GO

select enabled, * from sysjobs where name = 'syspolicy_purge_history'
GO

–> Now if you’ve to Disable Multiple or All the Jobs in SQL Agent, how will you do it?

Selecting All SQL Jobs on SSMS and right clicking won’t give you the Disable option. And here it become more tricky as there is only way to do this by TSQL query. But there is no single TSQL query defined in SQL Server to Disable all SQL Jobs at once. So, we will have to create a Dynamic SQL which will create Script for all SQL Jobs dynamically to Disable each and every SQL Job. Let’s see how:

USE msdb;
GO

-- Disable Multiple SQL Jobs at once:
DECLARE @dynSql NVARCHAR(MAX) = ''

SELECT @dynSql += N'exec msdb.dbo.sp_update_job @job_name = ''' + name + N''', @enabled = 0;' + CHAR(10) + CHAR(13)
FROM msdb.dbo.sysjobs
WHERE enabled = 1
ORDER BY name;

PRINT @dynSql;
-- Here is the output of above PRINT statement:
exec msdb.dbo.sp_update_job @job_name = 'ExecuteSPuspGetBillOfMaterials', @enabled = 0;

exec msdb.dbo.sp_update_job @job_name = 'syspolicy_purge_history', @enabled = 0;

Simple Copy-Paste the the above Dynamically generated SQL Script and Execute it, it will Disable all SQL Jobs at once.

Moved MASTER database by ALTER DATABASE statement? here’s the solution

October 8, 2012 2 comments

Have you also moved your MASTER DATABASE by using “ALTER DATABASE” statement just like you did for other system databases like MSDB, MODEL, TEMPDB & other databases?

If YES, then you are same nerdy DBA like me.

For quite some time I was observing very bad performance in one of our DEV servers. So today I thought to check it, I found that the C: Drive is almost full. Don’t know why do the DBA guys installed SQL Server on C: drive and put all system databases here to make it even worse. To get some room on C: drive I thought to move all four system databases (i.e. MASTER, MODEL, MSDB & TEMPDB) to another drive.

So, I created normal “ALTER DATABASE” scripts for all the 4 databases and executed them, as follows:

ALTER DATABASE master MODIFY FILE (
	NAME = 'tempdev' ,
	FILENAME = 'D:\MSSQL10_50\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf' )
ALTER DATABASE master MODIFY FILE (
	NAME = 'templog' ,
	FILENAME = 'D:\MSSQL10_50\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\templog.ldf' )

ALTER DATABASE master MODIFY FILE (
	NAME = 'modeldev' ,
	FILENAME = 'D:\MSSQL10_50\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modeldev.mdf' )
ALTER DATABASE master MODIFY FILE (
	NAME = 'modellog' ,
	FILENAME = 'D:\MSSQL10_50\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf' )

ALTER DATABASE master MODIFY FILE (
	NAME = 'MSDBData' ,
	FILENAME = 'D:\MSSQL10_50\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf' )
ALTER DATABASE master MODIFY FILE (
	NAME = 'MSDBLog' ,
	FILENAME = 'D:\MSSQL10_50\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf' )

-- !!!! BEWARE DON'T RUN THIS !!!!
ALTER DATABASE master MODIFY FILE (
	NAME = 'master' ,
	FILENAME = 'D:\MSSQL10_50\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf' )
ALTER DATABASE master MODIFY FILE (
	NAME = 'mastlog' ,
	FILENAME = 'D:\MSSQL10_50\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf' )
-- !!!! BEWARE DON'T RUN THIS !!!!

-> Happily I Stopped the SQL Server service.

-> Now, to move the databases physically I moved the MDF & LDF files to the new location I used in “ALTER DATABASE” statements above.

-> After moving DB files I tried to Start the “SQL Server” service, but the service didn’t start and I was getting following error:
“The SQL Server service on [SERVER_NAME] started and then stopped. blah blah blah…”

I immediately thought that I’ve done something wrong, checked MS BOL, and found that I should not have moved the MASTER database by using “ALTER DATABASE” statement.

–> WORKAROUND:
Now when the wrong scripts are executed and there is no way to undo it, there should be some way to fix it.

SQL Server comes with a tool i.e. “SQL Server Configuration Manager” to manage the services associated with SQL Server. Like, for this case to configure startup options that will be used every time the Database Engine starts in SQL Server.

Open this tool from “Program Files -> SQL Server -> Configuration Tools”:

-> Select “SQL Server Services” on the left side navigation bar.

-> On the right side Right Click on SQL Server instance and select Properties.

-> On the Pop-Up select the “Startup Paramaters” tab. Here you can change the MASTER database’s MDF & LDF file’s location:
—> Parameter starting with “-dD” is for DATA file (MDF).
—> AND parameter starting with “-lD” is for LOG file (LDF).

-> Select both properties one by one and change the file location at the “Existing Parameters:” text box and click Update for both the files.

-> Now, Start the Services and yes it started without any issue.

-> Check the new location by issuing either of following 2 SQL queries:

select * from sys.sysdatabases
-- OR --
select * from sys.master_files

Not only this is a workaround to fix this issue, but you can also use this tool to move your MASTER database to a different Drive.

Different ways to get SQL Server Version

October 5, 2012 2 comments

Today I got an email form a newbee regarding some help in SQL Server.
His question was a typical “SQL Server Interview Question”: What are the various ways to get SQL Server version number?

So I researched a bit and come up with following different methods for the same, as follows:

–> Method #1:

select @@version
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) 
	Jun 28 2012 08:36:30 
	Copyright (c) Microsoft Corporation
	Data Center Edition (64-bit) on Windows NT 6.1  
		(Build 7601: Service Pack 1) (Hypervisor)

–> Method #2:

SELECT 
	SERVERPROPERTY ('productversion') as ProductVersion, 
	SERVERPROPERTY ('productlevel')   as ProductLevel, 
	SERVERPROPERTY ('edition')		  as Edition
ProductVersion	ProductLevel	Edition
10.50.4000.0	SP2		Data Center Edition (64-bit)

–> Method #3:

select 
	CAST(@@microsoftversion as binary(10))	as VerBinary,
	@@microsoftversion / 0x01000000		as VersionNumber1, 
	@@microsoftversion / power(2, 24)	as VersionNumber2, 
	@@microsoftversion & 0xFFFF			as ReleaseNumber
VerBinary		VersionNumber1	VersionNumber2	ReleaseNumber
0x0000000000000A320FA0	10		10		4000

–> Method #4:

EXEC xp_msver 'ProductVersion'
Index	Name		Internal_Value	Character_Value
2	ProductVersion	655410		10.50.4000.0

–> Method #5:

EXEC sp_server_info
attribute_id	attribute_name		attribute_value
1		DBMS_NAME		Microsoft SQL Server
2		DBMS_VER		Microsoft SQL Server 2008 R2 - 10.50.4000.0
500		SYS_SPROC_VERSION	10.50.4000

–> Method #6:
Check the INSTANCE name in SSMS Object explorer. It shown SQL Server Version Number in brackets, like: (SQL Server 10.50.4000 – DOMAIN\user).

–> Method #7:
Check by “SQL Server Features Discovery report”.
Go to Start Menu -> Pragram Files -> Microsoft SQL Server -> Configuration Tools -> SQL Server Installation Center (64-bit)
A window will open, click on Toolsat the left navigation bar, then click on “Installed SQL Server Features Discovery report” link.
This will open up a HTML page in web-browser, which looks like in the image below:

–> Method #8:
Simply, in SSMS go to menu, Help -> About.
You will get a pop-up window which shows version number of difefrent Components installed as a part of SQL Server installation.

Categories: DBA Task, SQL Tips Tags:

Upgrade to SQL Server 2012 – Use Upgrade Advisor

September 2, 2012 Leave a comment

Are you planning to upgrade your SQL Servers to 2012? YES!

How will you make sure that you are ready for Upgrade? ???
How will you make sure that the Upgrade will be seamless? :(

SQL Server 2012 “Upgrade Advisor” is for you to check and analyze instances of all previous SQL Server versions i.e. 2008 R2, 2008, 2005 and even 2000 in preparation for upgrading to SQL Server 2012.

“Upgrade Advisor” identifies all features and configuration changes that might affect your upgrade. It provides links to documentation that describes each identified issue and how to resolve it and also generates a report that captures identified issues to fix either before or after you upgrade.

This tool has some prerequisites to install, and if you don’t install them you might see following error while installation:

Setup is missing prerequisites:

-Microsoft SQL Server 2012 Transact-SQL Script DOM, 
which is not installed by Upgrade Advisor Setup.
To continue, install SQL Server 2012 Transact-SQL Script DOM from below 
hyperlink and then run the Upgrade Advisor Setup operation again :

Go to http://go.microsoft.com/fwlink/?LinkID=216742

The above error mentions to install “MS SQL Server 2012 Transact-SQL ScriptDom” component. Install it from here: http://www.microsoft.com/en-us/download/details.aspx?id=29065&ocid=aff-n-we-loc–ITPRO40886&WT.mc_id=aff-n-we-loc–ITPRO40886

But for this also “MS DOT NET 4.0″ is required on your system. To Install it check this link: http://www.microsoft.com/en-us/download/details.aspx?id=17851

After installing Upgrade Advisor, launch the tool, it gives you 2 options:
1. Launch Upgrade Advisor Analysis Wizard
2. Report Upgrade Advisor Viewer

–> Analysis Wizard lets you run alanysis on following SQL components shown in image below:

–> Report Viewer tell you about the changes that needs attention or needs change/fix before or after upgrading to 2012. After Analysis Wizard finishes it creates repots which is in the form of an XML file.

The Report Viewer tool uses this XML file generated and give you details of components that may be affected. The report provides Importance, When to fix (Before/After), Description and links to information that will help you fix or reduce the effect of the known issues, image below:

After you are done with this Analysis and checking Reports, now the time is to work and fix on issues listed in the Reports.

Other than this you also need to check Microsoft BOL and/or MSDN articles to check for Discontinued and Deprecated features, Breaking and Behavior Changes.
Check following important links:
1. SQL Server Backward Compatibility: http://msdn.microsoft.com/en-us/library/cc707787
2. Database Engine Backward Compatibility: http://msdn.microsoft.com/en-us/library/ms143532
3. Analysis Services Backward Compatibility: http://msdn.microsoft.com/en-us/library/ms143479
4. Integration Services Backward Compatibility: http://msdn.microsoft.com/en-us/library/ms143706
5. Reporting Services Backward Compatibility: http://msdn.microsoft.com/en-us/library/ms143251
6. Other Backward Compatibility: http://msdn.microsoft.com/en-us/library/cc280407

Follow

Get every new post delivered to your Inbox.

Join 379 other followers