SQLGear

Recently a job failed with the following error: Database ‘DatabaseName’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details. BACKUP DATABASE is terminating abnormally.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly. Database was in RECOVERY_PENDING State and log_rescue_wait_desc was showing ACTIVE_TRANSACTION. There were disk space issues on both drive hosting the Data files and Log files. Making some space free on both these drives did not help. Alter database with set online command helped. Following is the command which was executed: ALTER Database DatabaseName SET ONLINE;

I recently faced an error while restoring a database from the backup file on a server. Following is the error I faced: Restore failed for Server 'ServerName'.  (Microsoft.SqlServer.SmoExtended) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1045+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476 ADDITIONAL INFORMATION: System.Data.SqlClient.SqlError: RESTORE detected an error on page (0:256) in database "DatabaseName" as read from the backup set. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1045+)&LinkId=20476 Another similar error was below: Msg 3183, Level 16, State 2, Line 1 RESTORE detected an error on page (0:16777216) in database "DatabaseName" as read from the...

Recently trying to restore a database following message was thrown: System.Data.SqlClient.SqlError: File 'F:\DATA\DatabaseName.mdf' is claimed by 'DBFileGroup1'(3) and 'DBFile'(1). The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo) The issue might be because of the restore process using same physical file name and path for two different database files. Go to the options tab and see. Following is the screenshot: In any file system there would be no physical files with same name in any given folder. This is why the error comes. Just modify the file name as per your requirement as below: Now hit the OK button. This will restore the database without any error.

Making sure the databases are being backed up is the first check every DBA needs to do. Following is the script to check the the last date of the database backup on current server or list out the databases which were not not backed up within last X number of days. It will also include databases which were never backed up on current server. It works with SQL Server version 2005 and above. --//-------------------------------------//-- --// Credits http://sqlgear.blogspot.com //-- --//-------------------------------------//-- /* Finding last backup date for all the databases OR find the databases not backed up within last X number of days. */ DECLARE @Days AS INT SET @Days = 0 --//If Set to 0 it will show you the last backup dates for the databases SELECT 'Database ' + d.NAME + ' was ' + CASE MAX(ISNULL(b.backup_finish_date, '1900-01-01 00:00:00.000')) WHEN '1900-01-01 00:00:00.000' THEN ' never backedup.' ELSE 'last backed up on ...

Credits: Jared Tarbell Cursors could be resource consuming. If there is a way to avoid cursor I would go by that first. Following is the simple query showing how to avoid cursors. This query backs up all the user databases to a specified location. It loops to every database in the sysdatabases table. --//-------------------------------------//-- --// Credits http://sqlgear.blogspot.com //-- --//-------------------------------------//-- DECLARE @name VARCHAR(50) --// database name DECLARE @path VARCHAR(256) --// path for backup files DECLARE @fileName VARCHAR(256) --// filename for backup DECLARE @fileDate VARCHAR(20) --// used for file name DECLARE @Min INT ,@Max INT ,@SQL NVARCHAR(MAX) SET @path = 'D:\Backup\' SELECT @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112) SELECT @Min = MIN(dbid) ,@Max = MAX(dbid) FROM MASTER.dbo.sysdatabases WHERE NAME NOT IN ( 'master' ,'model' ,'msdb' ,'tempdb' ) WHILE @Max >= @M...