MS SQL Database Backup and Restore

We can make MS SQL server backup a scheduled activity or a one-time manual process. The simplest way to do the backup is going to the Enterprise Manager, right click on that database, pick All Tasks, and pick Backup Database. Over there you can choose the devices or files you want to backup your Data and/or Log file of the database to. You can also decide whether this is going to be a scheduled task to one-time task. The backuped files can be appended to the backup media, or overwrote to the backup media.

To Restore the database(s), go to the same place to restore from your backup device(s). At some point we will need to overwrite the data and the log when needed, if we really want to restore. To make it 100% safe just in case restoration fails, you can backup data and log files to some other place first. To know where the data and the log files are, right click on the database in enterprise manager, click properties, the click on Data Files for the location and the Transaction Log for the location.

If the Master Database is damaged, then it is a different story: The SQL server will not run at all!

Let’s assume we already have a backup master, then all we need to do are the following steps:

1. Build The Master Database from scratch by a command line utility rebuildm.exe.

2. Start the SQL server in single user mode (sqlserver.exe -c -m)

3. Restore Master database from the backup device.

4. Restore msdb database from the backup device.

5. Restore other databases, if applicable.

For details, please go to:

http://www.dbarecovery.com/restoremasterdb.html#T-SQL