Sunday, September 23, 2012

SQL SERVER - How to verify Backup FIle before restoring

Verify backup-   There are some script to check and verify existing database before restoring. SQL Server has provided some Keywords that are mentioning below:-
  1. Verifyonly
  2. Headeronly
  3. Filelistonly
  4. Labelonly
-- - Checks to see that the backup set is complete and that all volumes are readable
restore verifyonly from disk='C:\Bacukup\Test_backup_DB.bak'

 ---Returns a result set containing all the backup header information for all backup sets on a particular backup device
restore headeronly from disk='C:\Bacukup\Test_backup_DB.bak'

 --- Returns a result set containing a list of the database and log files contained in the backup set.
restore filelistonly from disk='C:\bacukup\Test_backup_DB.bak'

 --sample for error message:
restore verifyonly from disk='C:\Test\Test_Error_BackUP.bak'

 --ingormation and software version information
RESTORE LABELONLY FROM DISK='C:\Backup\Full_Test_Error_BackUP.bak'

Like and Share to SQL Integrity Blog

Friday, September 14, 2012

SQL SERVER – How to see how much percent of backup has been completed while taking backup

SQL SERVER – How to see how much percent of backup has been completed while taking backup

There is simple command to get the level of percentage of backup in SQL SERVER.
When we have to take backup of big database at that time we can add the STATS option and can see the percentage of running backup

-- Creating test backup
CREATE DATABASE TESTDB
GO
--creating test table
CREATE TABLE TEST_TABLE (ID INT)
GO
-- Taking bacup with STATS option
BACKUP DATABASE TESTDB  TO DISK= 'C:\TESTDB.BAK' WITH STATS =10
GO


Like and Share to SQL Integrity Blog

Monday, September 10, 2012

SQL SERVER - What is RAISERROR with details and Example


Using RAISERROR, we can throw our own error message while running our Query or Stored procedure.
·        It allows developers to generate their own messages
·        It returns the same message format that is generated by SQL Server Database Engine
·        We can set our own level of Severity for messages
·        It can be associated with Query and stored procedure
·        ERROR message can have 2047 character and show only 2044

-- syntax 


RAISERROR ( { Message ID | Message Text} { ,severity ,state }
    [ ,argument [ ,...n ] ] )
    [ WITH option [ ,...n ] ]

·        Custom error message ID should be greater than 5000.
·        Severity option should between 0 to 25 and for fatal error 20 to 25
·        State option is default set 1 but we can set 1 to 127
·        With option can set for log it can true or false like as:-

 Example:

exec sp_addmessage @msgnum=50010,@severity=1,@msgtext='my custom error message text',@with_log='true'

We can see error massage in sys.messages view and using sp_addmessage procedure we can add new custom error message in sys.messages view.
exec sp_addmessage @msgnum=50009,@severity=1,@msgtext='Adding Custom Error Message'

OR
 
            SELECT * FROM sys.messages

Like and Share to SQL Integrity Blog