Saturday, February 25, 2012

Backup and restore databases In my MSDE

Hi all! How can i backup databases which are running in my MSDE 2000 & then how can i restore them with all the data, Yes i know that their is no visual tool for doing this in MSDE but can we do it with scripts, if Yes then howThe SQL to backup a database in MSDE is:

BACKUP DATABASE { database_name | @.database_name_var }
TO < backup_device > [ ,...n ]
[ WITH
[ BLOCKSIZE = { blocksize | @.blocksize_variable } ]
[ [ , ] DESCRIPTION = { 'text' | @.text_variable } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] EXPIREDATE = { date | @.date_var }
| RETAINDAYS = { days | @.days_var } ]
[ [ , ] PASSWORD = { password | @.password_variable } ]
[ [ , ] FORMAT | NOFORMAT ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @.text_variable } ]
[ [ , ] MEDIANAME = { media_name | @.media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @.mediapassword_variable } ]
[ [ , ] NAME = { backup_set_name | @.backup_set_name_var } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]

and to restore:

RESTORE DATABASE { database_name | @.database_name_var }
[ FROM < backup_device > [ ,...n ] ]
[ WITH
[ RESTRICTED_USER ]
[ [ , ] FILE = { file_number | @.file_number } ]
[ [ , ] PASSWORD = { password | @.password_variable } ]
[ [ , ] MEDIANAME = { media_name | @.media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @.mediapassword_variable } ]
[ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
[ ,...n ]
[ [ , ] KEEP_REPLICATION ]
[ [ , ] { NORECOVERY | RECOVERY | STANDBY = {undo_file_name|@.undo_file_name_var} } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] REPLACE ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]

Things in square brackets are not strictly speaking required.

No comments:

Post a Comment