March 18, 2011 at 1:01 am
have anyone ever used this option(DUMP) with backup database/log.
I have just read an article on msdn and found that to make your database backward compatible, you can use DUMP keyword in your backup syntax,i.e.,
dump backup database/log ........
I havent tried it yet but would like to know if it workes and reliability between versions.
The post says applicable till 2005 and will be removed but would like yo know if anyone tried it on higher version and restored the backup file to lower version?
Please share your thoughts.
edit:-
forget to mention the MSDN article:-
http://msdn.microsoft.com/en-us/library/ms187315(v=sql.90).aspx
----------
Ashish
March 18, 2011 at 2:39 am
DUMP isn't an "option" or a "version", it won't make your backup file backwards compatible. It is nothing other than the keyword of the command used to backup the database in earlier versions of SQL Server.
In SQL2000 and 2005, DUMP DATABASE..... is exactly the same as BACKUP DATABASE....., the backup file produced will be exactly the same whichever keyword you use.
Newer versions of SQL Server won't support the DUMP keyword as a synonym for BACKUP, so don't even bother going there, just use the BACKUP keyword.
March 18, 2011 at 7:14 am
From the article: " ... The DUMP statement is included for backward compatibility ..."
You are misinterpreting "backward compatibility "
It means that old syntax (from SQL 6, 7, 2000 etc...) can still be used so that processes using the old syntax will still run. "DUMP" will still work, but BACKUP is the current & preferred syntax.
March 18, 2011 at 1:53 pm
A restore between different versions will only work from a lower to a higher version, not the other way around because the system tables of a SQL Server 2008 database are "unintelligible" to a 2005 server, whilst a 2008 server will automatically recognize the 2005 system objects and update them as necessary to "upgrade" the entire database.
Therefore, if you have to move a database back to an older version, your best bet is to export ONLY the user objects and import them again on the lower version server.
The "compatibility level" setting of a database has no effect on this. If compatibility level is set to 2005 and it's running on a 2008 server, it's still a SQL Server 2008 database "under the hood", it's just presented as a 2005 database to applications connecting to it.
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
March 20, 2011 at 9:43 pm
Thanks everyone for their valuable input. Was bit confused when someone told me that the feature can be used, but now 100% sure that its not the case.
----------
Ashish
March 21, 2011 at 1:59 am
The command can still be used (in some versions), it does exactly what backup database does, nothing more.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply