Backup MS SQL Server 2005?

Question

I have a MS SQL Server 2005. It is very easy to backup individual databases, just right click on database then Tasks->Backup. My question is how to back up SQL server database objects outside the databases?

For example, under the Security->Logins, there are list of login users; under the SQL Server Agent->Jobs, there are list of jobs, and under the Server Objects->Linked Servers; ans so on.

Is there any way to do full-backup of SQL server? in TSQL? I tried to find out from SQL Server Management Studio but I could not find any.

Answer 1

database logins are stored in the user database, server logins are stored in master. msdb stores sql jobs and history. the simplest full backup you can do in T-SQL is:

BACKUP DATABASE dbname 
TO DISK='C:\backupfile.bak' WITH INIT

WITH INIT means that it will overwrite the file.

Restoring the master database requires restart sql server in single user mode. You do this from the command line in the sql server directory with: sqlservr.exe -c - m

then connecting with SSMS or sqlcmd and run a restore.

Answer 2

I recommend using this script. I spent much time looking after maint plan failures before it.

http://blog.ola.hallengren.com/

http://msdn.microsoft.com/en-us/library/ms187048.aspx has more info on the standard procedure.

If you're responsible for these databases, I recommend to practice restoring the system databases on a new server, so when the time comes you are confident. The restore is not as simple as rightclicking restore.

Answer 3

Those items are stored in the system databases -- mostly master and msdb (under databases | System Databases). You can either back those up individually (like you do other databases) or, better yet, create a Maintenance plan (Management | Maintenance Plans) to do so on a regular schedule.