Siusic Dot Com

When I first learn how to use SQL server the only way I knew to backup SQL server was to use Enterprise Manger. In fact Enterprise Manger (Management Studio for SQL 2005) is a very advance and easy to use database management tool. You can pretty much do everything

you need to do with SQL server in Enterprise manager. Many database systems like MySQL or Oracle don’t have such advance tools come with them. I think that is Microsoft’s golden norm. They always try to develop an easy to use GUI tool to shield user from thinking the complexity of the software behind it and they are doing that very successful. The follwing is a screen shot of Enterprise Manager when I try to backup the test database.

New user of database system will think that the learning curve of using SQL server is much shorter but in fact that may be just a miss-conception. By the time you realize that you may have already invested money and time. Of course you won’t regret because SQL server is excellent database system.

Anyway Enterprise Manger is great tool and it can let you backup and restore database easily but what if you have 20 database servers. Maybe it is not too much for you set up the maintenance plan in Enterprise Manger one by one. I remember one of my formal colleague mentioned that on average a SQL server DBA should manage 100 SQL servers. Think about those hosting companies that provide SQL database hosting. They probably have several hundred SQL servers and tens of thousands of SQL databases. Setting up maintenance plan one by one is just very labor intensive.

An alternative way to backup SQL server is to use SQL script. Enterprise manger does that for you behind the scenes. A simple statement like the following can let you backup and restore SQL databases.

Backup Database DatabaseName to DISK='c:\BackupFile'
Restore Database DatabaseName from DISK='c:\BackupFile'

The best thing about these two statements is that you can pass variables as the database name and the backup file name. So they are totally dynamic. If you know a list of SQL server name you can write a script to loop through each server, connect to them, get a list of database they have and send a backup statement. You can do that with one SQL server then you can do that with any number of servers.

Related Posts:

MySQL Replication and MS SQL Log Shipping

SQL Server Backup Device Error or Device off-line

Restore the latest database backup automatically and xp_dirtree

How to Separate Data and Indexes of a Table into Different Files

A CommVault Galaxy QiNetix Bug Experienced in Restoring SQL Server Database