Database RESTORE error Msg 3183, Level 16, State 2, Line 1

When you try to restore/verifyonly SQL backup you may be found this error some time because the backup may be corrupted.

Solution : Make sure to copy backup file into another device then restore, The issue is on tape/usb/disk

RESTORE VERIFYONLY FROM DISK = 'E:\MSSQL\Backup\MYDBTEST.BAK'

Msg 3242, Level 16, State 2, Line 2
The file on device '\\websql2\SQLBackup\MYDBTEST_2011-06-21-2040.FUL' is not a valid Microsoft Tape Format backup set.
Msg 3013, Level 16, State 1, Line 2
VERIFY DATABASE is terminating abnormally.


RESTORE DATABASE MYDBTEST
FROM DISK = 'E:\MSSQL\Backup\MYDBTEST.BAK'
WITH RECOVERY,
MOVE 'MYDBTEST_Data' TO 'E:\\MSSQL\DATA\MYDBTEST.mdf',
MOVE 'MYDBTEST_Log' TO 'E:\ MSSQL\DATA\MYDBTEST_log.ldf',
STATS = 5


05 percent processed.
.
.
.
.

85 percent processed.
90 percent processed.

Msg 3183, Level 16, State 2, Line 1
RESTORE detected an error on page (0:0) in database "MYDBTEST" as read from the backup set.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Error: 3041, Severity: 16, State: 1

Message:

BACKUP failed to complete the command BACKUP DATABASE db_name. Check the backup application log for detailed messages

Solution:  Make sure to take another full database backup.

How to find the Linked Servers in SQL Server

You can run the below system stored procedure to find the list of linked servers configured on instance of sql servers.

sp_linkedservers

How to start sql server without tempdb

When i try to take image of sql server for installation to new server  i could not restart sql server service on new server due to tempdb database files missing. In that image server where tempdb database files were placed on E:\mssql\data drive and  new sql server only got C: drive  and all system dbs there on c: drive  files. This solution is tested and worked fine.

--  SQL Server Path
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn
Step:1  You are going to  start sql using single user with minimal configuration on command prompt
 cmd-- > sqlservr -f -m
Now you create Folder on 'C:\MSSQL\DATA\
-- Open the second window for command prompt
c:\sqlcmd
1>
use master
go
ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, FILENAME='C:\MSSQL\DATA\Tempdb.MDF')
go
ALTER DATABASE tempdb MODIFY FILE (NAME=templog, FILENAME='C:\MSSQL\DATA\Templog.LDF')
go
>quit

T-SQL query to get tables size in GB with no of rows in SQL Sever

SELECT
 s.name + '.' + t.Name AS [Table Name],
 part.rows AS [Total Rows In Table - Modified],
 CAST((SUM( DISTINCT au.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(18, 3))
 AS [Table's Total Space In GB]
FROM
 SYS.Tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
 INNER JOIN SYS.Indexes idx ON t.Object_id = idx.Object_id
 INNER JOIN SYS.Partitions part ON idx.Object_id = part.Object_id
                    AND idx.Index_id = part.Index_id
 INNER JOIN SYS.Allocation_units au ON part.Partition_id = au.Container_id
 INNER JOIN SYS.Filegroups fGrp ON idx.Data_space_id = fGrp.Data_space_id
 INNER JOIN SYS.Database_files Df ON Df.Data_space_id = fGrp.Data_space_id
WHERE t.Is_ms_shipped = 0 AND idx.Object_id > 255
GROUP BY t.Name, s.name, part.rows
ORDER BY [Table's Total Space In GB] DESC