MSDN Blog Link!

I want each one of you ,go through the below link ->where the MS Engineers/Solid professional blogs over there-

http://blogs.msdn.com/b/sqlserverfaq/

http://blogs.msdn.com/b/sqlserverstorageengine/

tempdb database is marked suspect,,

How to recover SQL Server after a tempdb database is marked suspect?

You can refer the below link to resolve the issue-
http://support.microsoft.com/kb/288809

Msg 3257, Level 16, State 1, Line 2-There is insufficient free space on disk volume 'Drive:\' to create the database

Today there was an activity to perform restore on the PROD server using the TEST database backup file,the

test Backup file is around 23GB, so moved to Production server and started executing the restore command  but the below error has thrown-
Msg 3257, Level 16, State 1, Line 2
There is insufficient free space on disk volume 'R:\' to create the database. The database requires 293066571776 additional free bytes, while only 75202101248 bytes are available.
Msg 3119, Level 16, State 4, Line 2
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.



So used the below query to see what are the files that exists in the Backup file i,e size,Maxsize, Filegroup,Logical file name and Physical filename.

Restore filelistonly from disk='<Drive>:\XXXXXX.BAK'

so we observed that the Initial size of the MDF file is near to 176GB and remaining other files(1 ndf and 2 ldf files) occupies is 96.93GB ->Over all 272.3G.

But when we have checked - what is the usuage of the database ?

--the database has 78% free space in it & used space is 22%.

how ever our restore strategy  pointing to the drive where it was less space i,e R:Drive has 70.73GB free space out of 120GB, hence the  error throwns that-

"There is insufficient free space on disk volume 'R:\' to create the database. The database requires 293066571776 additional free bytes, while only 75202101248 bytes are available.
Msg 3119, Level 16, State 4, Line 2"


I,e A)the database requires 293066571776 additional free bytes --->Here the whole Database size 272.93GB i,e the Physical files to get initialize on the Physcial disk eventhough the usuage percentage of the database was less.
    B)while only 75202101248 bytes are available ---------->Here clearly expalins that in the  drive which you are restoring is have only 70.73GB hence database not able to Initialize .

so the next error will be as mentioned below -
Msg 3119, Level 16, State 4, Line 2
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

To resolve
==========
1.Before taking the Backup of the database --Verify what is the Initial file size and check what is the overall usuage of the indivual file size i,e how much used space & free space.

2.Once identifed then go for altering the initial file sizes using the Alter command or you can go for shrinking then take the backup and restore it on where  ever you want.

3.or If any other drive has enough space to hold then change the restore command i,e where you wan to be Physical files to be

or if you do not have the space then check Muliple drive free space then you can add accordingly.



Bottom Lines
=============
Want to know more about what is the Database file initialization then refer the below links -

http://msdn.microsoft.com/en-us/library/ms175935.aspx
http://sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-instant-file-initialization.aspx
http://sqlserverperformance.wordpress.com/2011/02/10/windows-instant-file-initialization-and-sql-server/
http://sqlblog.com/blogs/tibor_karaszi/archive/2009/03/09/do-you-have-instant-file-initialization.aspx
http://www.databasejournal.com/features/mssql/article.php/3899156/Instant-File-Initialization-for-SQL-Server-20052008.htm


Thanks,
Rama Udaya.K

Higher to Lower version restoration!

Can we restore the database of Higher to Lower version restoration?
OK let's Plan like this -Backup the database of SQL server 2008(before backing up change the Database compatible level to 90) then restore the database on SQL server 2005.

Backup completed of SQL server 2008 database after changing 100 to 90.

Now I will go for the restoring the database on SQL server 2005 -let's see what it throws an error even though changed the compatibility of the database-

 

 So I plan to see what the Header of the Backup file to get some information about Backup file, how ever it is failed to reterive data in SQL server 2005 & it throws sam error-

 So one way we can do, not restore method but we can use script out method so,Let's see how it is.
*Log in to the SQL server 2008 ->select the database( which you want place in sql server 2005) ->Right click Tasks ->Generate the scripts and change the script for server version to your comfortable version.

Please see the below figures:-

                                          

Now I will use the above scripts and will execute in the new query window of SQL server 2005 -

(see the figure below)  before that I have created an dummy database called :DBMAINT_TEST

after executing the script you will get the outpuy as Command's completed successfully........(please see fig)

Finally it's done

Bottom Lines

---------------

1.You cannot restore the database from Higher to Lower version.

2You can also use -Import and export wizard instead of Generate SQL Server Scripts 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.

3.Note ->When you restore or attach a database which is created from SQL Server 2008 to SQL Server 2005 or SQL Server 2000, you will get an error so other way you can use is the Bottomline 2 options.

Thank you!!!