Friday 7 March 2014

Databases Related Issue and Solutions

                                    How to Repair a SQL Server 2008 Suspect database?
 
A database can go in suspect mode for many reasons like improper shutdown of the database server, corruption of the database files etc.
To get the exact reason of a database going into suspect mode can be found using the following query, DBCC CHECKDB (’YourDBname’) WITH NO_INFOMSGS, ALL_ERRORMSGS
To repair the database, run the following queries in Query Analyzer
EXEC sp_resetstatus 'yourDBname';

ALTER DATABASE yourDBname SET EMERGENCY

DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER

                                                     How to Moving System Databases?
 
Ans:  move a system database data or log file as part of a planned relocation or scheduled maintenance operation, follow these steps.
This procedure applies to all system databases
(1)Stop the instance of SQL Server or shut down the system to perform maintenance
(2)Move the file or files to the new location.
(3) Restart the instance of SQL Server or the server
(4) Verify the file change by running the following query.


**********************************************************
SQL Server Agent won't start up. Agent XPs is disabled too, even though I followed the following steps
Use below queries

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

                                Move Master database from one drive to another

You may need to move master database from one drive then you may help from given below info

Open SQL Server Configuration manager from program files. In SQL Services Nodes, right clieck on SQL Server(i.e.MSSQLSERVER) and choose Properties and go to Advanced TAB and Edit Startup Parameters values to point to planned location for the master database data abd log files and then click on ok.

The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.

-dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\
master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\
LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\
DATA\mastlog.ldf


If the planned relocation for the master data and log files is E:\SQLData, the parameter values would be changed as follows:

-dE:\SQLData\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG\ERRORLOG;-lE:\SQLData\mastlog.ldf

stop the instance by right click and choose stop and move the master.mdf and master.ldf to new location.

and restart the instance.

Verify the file change for the master database by running the following query.

CopySELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('master');
GO
 
 
                 
A database can go in suspect mode for many reasons like improper shutdown of the database server, corruption of the database files etc.
To get the exact reason of a database going into suspect mode can be found using the following query, DBCC CHECKDB (’YourDBname’) WITH NO_INFOMSGS, ALL_ERRORMSGS
To repair the database, run the following queries in Query Analyzer
EXEC sp_resetstatus 'yourDBname';

ALTER DATABASE yourDBname SET EMERGENCY

DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER

                         

                              How to start and stop the server through COMMAND prompt?


Ans  Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt
For the default (MSSQLSERVER) instance, run the following command
            NET START MSSQLSERVER /f /T3608
For a named instance, run the following command:
            NET START MSSQL$instancename /f /T3608

                                        
                                                How to: Restore the master Database?
 
Start the server instance in single-user mode single-user startup parameter (-m)
Command: Net start MSSQLSERVER /m

Then make a connection to the sql server using the Dedicated Admin console using the sqlcmd command
sqlcmd -S Server Name -U sa -P saPassword –A

Note: here -A stands for dedicated Admin connection that allows the admin to connect even if the db engine is not responding to other applications and users
Once a connection is made, we can execute restore comnmand to restore the master database from the backup.for example..
\> sqlcmd restore database master from disk =’c:\backup\master.bak’
Note :For a named instance, the sqlcmd command must specify the -S<ComputerName>\<InstanceName> option.
Once that is done, restart the service and you are back online.


Creating a New msdb Database : If the msdb database is damaged and you do not have a backup of the msdb database, you can create a new msdb by using theinstmsdb script
  • Stop all services connecting to the Database Engine, including SQL Server Agent, SSRS, SSIS, and all applications using SQL Server as data store.
  • Start SQL Server from the command line using the command: NET START MSSQLSERVER /T3608
  • In another command line window, detach the msdb database by executing the following command, replacing <servername> with the instance of SQL Server: SQLCMD -E -S<servername> -dmaster -Q"EXEC sp_detach_db msdb"
  • Using the Windows Explorer, rename the msdb database files. By default these are in the DATA sub-folder for the SQL Server instance.
  • Using SQL Server Configuration Manager, stop and restart the Database Engine service normally.
  • In a command line window, connect to SQL Server and execute the command: SQLCMD -E -S<servername> -i"C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Install\instmsdb.sql" -o" C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Install\instmsdb.out"
  • Replace <servername> with the instance of the Database Engine. Use the file system path of the instance of SQL Server.
  • Using the Windows Notepad, open the instmsdb.out file and check the output for any errors.
  • Re-apply any service packs or hotfix installed on the instance.
  • Recreate the user content stored in the msdb database, such as jobs, alert, etc.
  • Backup the msdb database.

Restore point in time recovery Process: These are the commands to restore the database and one transaction log to Apr 23, 2007 05:31:00 PM.  The first full backup restore is done using the NORECOVERY option, so the database stays in a loading state and additional backups can be restored.  The second restore command restores the transaction log to the point in time = 'Apr 23, 2007 05:31:00 PM'.  In addition, we are using the RECOVERY option to put the database back into a useable state after the restore of the transaction log.. 
Note: Database needs to be in either the Full or Bulk-Logged recovery model
RESTORE DATABASE DBUtil
FROM DISK = 'C:\Backup\dbutil.bak'
WITH NORECOVERY

RESTORE LOG DBUtil
FROM DISK = 'C:\Backup\dbutil_log.trn'
WITH RECOVERY,
STOPAT = 'Apr 23, 2007 05:31:00 PM'

                                                                                                            How to Detach Database.
EXEC sp_detach_db @dbname = 'pubs'
                                                                                                    How to Attach mdf file without ldf file in Database.
EXEC sp_attach_single_file_db @dbname = 'pubs', @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'


Error : RESTORE cannot operate on database because it is configured for database mirroring
Error:RESTORE DATABASE AdventureDB WITH RECOVERY

Msg 3104, Level 16, State 1, Line 1
RESTORE cannot operate on database 'AdventureDB' because it is configured for database mirroring.
Use ALTER DATABASE to remove mirroring if you intend to restore the database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

You found this above strange error.

Solution :You can use below step:
You come to know mirror database not sync while, If you decided to break the mirror and re-setup mirror.

1. ALTER DATABASE AdventureDB SET PARTNER OFF ( You remove the mirror first)
2. Take the full backup from the principal server copy over to mirror
3. You will see mirror database is disconnected

RESTORE DATABASE AdventureDB WITH RECOVERY 
 
 
 
 
 

    Determining how long a database will be IN RECOVERY (SQL Server 2008)

DECLARE @DBName VARCHAR(64) = 'databasename'
 
DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
 
INSERT INTO @ErrorLog
EXEC sys.xp_readerrorlog 0, 1, 'Recovery of database', @DBName
 
SELECT TOP 5
  [LogDate]
 ,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
 ,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
 ,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
 ,[TEXT]
 
FROM @ErrorLog ORDER BY [LogDate] DESC
Update - Sql Server 2012 uses a different stored procedure to read the error log:
DECLARE @DBName VARCHAR(64) = 'databasename'
 
DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
 
INSERT INTO @ErrorLog
EXEC master..sp_readerrorlog 0, 1, 'Recovery of database', @DBName
 
SELECT TOP 5
  [LogDate]
 ,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
 ,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
 ,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
 ,[TEXT]
 
FROM @ErrorLog ORDER BY [LogDate] DESC



  Query to find out Estimated Time, Percentage, Elapsed time of a RESTORE DATABASE/BACKUP DATABASE 
 SELECT r.session_id ,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours]
,CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2
,CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))FROM sys.dm_exec_requests r
WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')


                         Restore Database From SQL Server 2008 to SQL Server 2005

Problem

When you restore or attach a database which is created from SQL Server 2008 to SQL Server 2005 or SQL Server 2000, you will see some error messages as the examples below.

Error  :RESRORE HEADERONLY is terminating abnormally (Microsoft SQL Server ,Error 3241)

Backup and Restore
You have backup a database from SQL Server 2008. If you try to restore the backup database file to SQL Server 2005, you will receive the error message:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Additional information:
-> The media family on device ‘the backup file‘ is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)
Detach and Attach
You have detach a database from SQL Server 2008. If you try to attach the detached database file to SQL Server 2005, you will receive the error message:
Attach database failed for Server ‘SQL Server name’. (Microsoft.SqlServer.Smo)
Additional information:
-> An exception occurred while executing a Transact-SQL statement batch. (Microsoft.SqlServer.ConnectionInfo)
–> The database ‘database name’ cannot be opened because it is version 655. This server supports version 611 and earlier. A downgrade path is not supported.
Could not open new database ‘database name’. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 948)
Solution
These problems occur because a backup or detach database file is not backward compatible. You cannot restore or attach a database which is created from a higher version of SQL Server to a lower version of SQL Server.
But there are some alternatives which can help you to restore a database to a lower version of SQL Server. I divide into separate parts.
  1. Part 2: Generate SQL Server Scripts Wizard. The solution creates a SQL Server script file using a wizard. Then, you simply execute the script file on SQL Server 2005 or SQL Server 2000. So you will get everything as same as the source database on the destination. But there are some disadvantages:
    • If the source database contains lots of data, you will have a large script file.
    • The generated file is a plain text. Anyone who has access to the file can read it. So you should delete the script file after the restoration.
  2. Part 3: Import and Export Wizard. This solution exports data to the targeted SQL Server using a wizard. It is more secure and effective than the first solution. But you can only export tables and views only.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)