SQL Backup

[darronb]

I have just set up a backup to happern throught Enterprise Manager, I have set jobs up to backup the databases I have created.

If I needed to do a full restore because of a system failure would these database be enough, or do I need to back up master, model, msdb, and tempdb.

I am very new at this so sorry if it is a muppet question.

Thanx

 

[DrSql]

Master for all system ojectsis, users etc ,
model only if you customize it.
msdb has details of all tasks, dts packages, jobs etc
tempdb is the temp space used by other db's when its used.
So I would backup depeindig on your changes.
It shouldn't take too much storage, so backup all system db's.
Good luck

Dr. Sql
goEdeveloper@yahoo.com
Good Luck.

 

[SQLBill]

You really should never have to backup TempDb. This database is deleted and recreated everytime that SQL Server is stopped and started.

As Dr.SQL says, the Model database only changes when you make changes to it. It's the template that is used when you create new databases.

-SQLBill

Posting advice: FAQ481-4875

 

[darronb]

I have now backed up all my databases, including master, Model, and msdb.

I am now trying to restore the databases and having problems restoring the master database.

I get a error "RESTORE DATABASE must be in single user mode when trying to restore the master database. RESTORE DATABASE is terminating abnormally".

Could you please advice me how to restore the master database and weather this error will occur on the model ans MSDB database.

 

[keain]

To start in single user mode, you can use a command line.

Go to start/run and type cmd and hit enter.

Then, navigate to:

x:\program files\microsoft sql server\mssql\binn>

and run:

sqlservr.exe -c -m

Once the database starts up, you should be able to open enterprise manager and restore the master.

Shouldn't have to do that for model or msdb.

 

[darronb]

I am doing the above and getting an error message when trying to restore the master batabase

It is an empty dialog box with a red cross and an OK button. the header says "Microsoft SQL-DMO (ODBC SQLState: HY000)

Any ideas please