• DBA

    Restoring a database from higher version to lower version SQL server

    May 28, 2011

    Restoring a database from higher version to lower version SQL server How to downgrade a database from higher version to lower version? There is no direct way to do this like Backup/Restore. You can use an alternative ways. The alternative ways are,   1. Generate Script 2. Import/Export 3. Transaction replication You may use the alternative ways. It depends upon your environment. Restore errors A backup has taken from higher version (SQL 2008/2005) BACKUP DATABASE dba_test TO DISK='E:\mu\dba_2008.bak' Restore the backup to lower version (SQL 2005/2000)   RESTORE FILELISTONLY FROM DISK ='E:\mu\dba_2008.bak'   Restore error from SQL server 2005 to SQL server 2000 Server: Msg 3205, Level 16, State 2, Line 1 Too many backup…

    Read More
  • DBA,  Scripts

    Shrinking the log file script for SQL server database

    April 10, 2011

    Shrinking the log file script for SQL server database                                This script mainly for a DBA’s, because most of the time the log file grow again and again and eats up all the disk space and finally an application goes down. Errors from SQL server error log: Error: 9002, Severity: 17, State: 2.   The transaction log for database ‘test’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases If you got an error “Error: 9002, Severity: 17, State: 2.” like this go and check the log_reuse_wait_desc column in sys.databases by running below command and see what value it returns.   SELECT name,…

    Read More
  • DBA

    Difference between truncating and shrinking the transaction log file

    March 7, 2011

      Difference between truncating and shrinking the transaction log file I have noticed in many forum the frequently asked question is “Truncating and shrinking the log file both are same” The answer is obviously NO. These both are totally different. Before going into discussion, let’s read the transaction log file architecture. Truncating the transaction log: Truncating a log file will change one or more VLF file’s status from active to inactive and mark the space for reuse. SQL server will change the VLF from active to inactive and reuses it automatically (Just like a circular).   When does it truncate and reuse it automatically? It depends upon your setting: recovery…

    Read More
  • DBA,  Scripts,  T-SQL

    How to get the SQL server critical errors via mail using native T-SQL Script

    February 6, 2011

      T-SQL Tuesday #015: Automation:           I am very much interested to participate in the T-SQL Tuesday event party. I have seen the T-SQL Tuesday logo and article in one of my favorite SQL server blog Gail Shaw. I found the Adam Machanic ( Blog | Twitter ) via Google but, I did not find the required information so, I wrote an email to him and he has sent me the link to topic http://sqlasylum.wordpress.com/2011/02/01/invitation-to-t-sql-tuesday-15-automation-in-sql-server. Thanks to Adam Machanicfor doing this wonderful job. This is my first T-SQL Tuesday post. I have referred some of T-SQL Tuesday blog post. This month Pat Wright is hosting and has given us the…

    Read More