Backup SQL server using 2003 Backup

[sbruce66]

All,
I am new to SQL server 2005, can I use the windows 2003 backup utility to backup the whole server including the SQL database. I also want to do SQL database backups seperately? Does SQL have a backup tool?

Thanks,
Scott

 

[mrdenny]

Yes it does. You have to use the backup database command to backup the databases. Lookup the backup database command in Books Online for the full syntax and all the options.

You should tell the Windows Backup software to skip the SQL Server database files as backing these files up will do you no good in the event that you have to restore the server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[WhoKilledKenny]

I do not believe that the Native backup will backup your databases hot (online). You would have to take the databases offline to back them up.

Can't take the database offline...
In SQL Server Enterprise manager (SQL7 and 2000) under Managment you can set up database maintenace plans that will backup up your database to a flat file. I'm sure the same type of functionality exitst in SQL 2005. The windows Native backup can then backup up the flat file. This is a standard procedure if you are not using a solution that backs up databases hot.

 

[WhoKilledKenny]

Yes it does. You have to use the backup database command to backup the databases. Lookup the backup database command in Books Online for the full syntax and all the options.

Mr Denny,
Just to clarify, here you are talking about SQL or Windows 2003 Native back up?

If native, I want to researh this...

Thanks-

 

[itsp1965]

Windows native backup CANNOT backup an online SQL Server db (since it is always open). In this scenario it can only backed up if the SQL Service is shutdown (which you don't want to do) You can use either Enterprise Mgr/SMO to perform the backup or use an open file/data protection product that specifically backs up SQL server. Hope this helps.

 

[mrdenny]

The backup database command is a SQL Server T/SQL command.

File backup utilities can not backup SQL Server files unless you stop the SQL Server service which stops the SQL Server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com