Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm currently taking over for someone who was in charge of backing up over 250 servers on different platforms, until we hire a replacement. The main question I have is:

If we use a backup software, such as Symantec backup exec, does this perform the correct backup for MSSQL Server?

I was listening to Stack Overflow Podcast, and I heard them talk about you cannot just backup the SQL data files, but you also need the transaction log? So, if we just backup the whole machine, would we be able to recover it correctly, since we would be backing up the data file and the log?

Thanks!

share|improve this question

4 Answers 4

up vote 1 down vote accepted

There are two ways people backup databases. One is to do dump of the database (i'm not a dba so i'm not sure the mechanics of doing the dump) to a text file, then write that file to tape. The second is to use an agent that is aware of the RDBMS that you are using. Both of these methods will get you everything you need to restore the DB to a working condition.

You can use backup exec, but you will need to make sure that the MSSQL Agent is installed on those systems. Since you have more that 250 server's i'm guessing you have at least one dba on staff - I would ask them as to how backups of the SQL servers are currently being done. They should know some of the basics at least enough to get you started. They would know things like whether your predecessor used an agent, or backed up text dumps.

share|improve this answer
    
I wouldn't call native SQL Server backups text dumps, they're binary files (eg, a full backup of a file with a .bak extension) I wouldn't be doing text file dumps of sql server as a backup strategy (maybe your thinking of mysql?) Also don't forget that master & msdb are important system dbs to be backing up –  Nick Kavadias Jan 3 '10 at 22:43

You need to check out Brent Ozar's blog on sql server. He's a fountain of great knowledge. That being said, backing up SQL server can be really easy, or really complicated depending on how fresh the data needs to be. If you can handle a day's worth of lost data, then plain jane SQL server backup works great. If you need to make sure you have a much more recent data file then log shipping might be your next best bet.

Find a friendly DBA to audit and make sure you are actually getting good backups.

share|improve this answer

If you use Backup Exec with the SQL agent option then it does backup the databases correctly, by using the SQL API to perform the backup. I think the issues with SQL backups referred to in the podcast were to do with offline backups (stop SQL server then backup the files). Note that with Backup Exec you need to do transaction log backups as well as full backups in order to stop your transaction logs growing (if your databases are in full recovery mode). Have a look at http://www.backupexecfaq.com/articles/concepts/backing-up-microsoft-sql-server.html for some more in depth information.

share|improve this answer

Here is a script I use:

DECLARE @DBName varchar(255)

DECLARE @DATABASES_Fetch int

DECLARE DATABASES_CURSOR CURSOR FOR select DATABASE_NAME = db_name(s_mf.database_id) from sys.master_files s_mf where -- ONLINE s_mf.state = 0

   -- Only look at databases to which we have access
and has_dbaccess(db_name(s_mf.database_id))

= 1

    -- Not master, tempdb or model
and db_name(s_mf.database_id) not in ('Master','tempdb','model')
group by s_mf.database_id
order by 1

OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

WHILE @@FETCH_STATUS = 0 BEGIN declare @DBFileName varchar(256)
set @DBFileName = datename(dw, getdate()) + ' - ' + replace(replace(@DBName,':',''),'\','')+ '.BAK'

exec ('BACKUP DATABASE [' + @DBName + '] TO  DISK =

N''$systemdrive$:\somewhereFun\' + @DBFileName + ''' WITH NOFORMAT, INIT, NAME = N''' + @DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 100')

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName END

CLOSE DATABASES_CURSOR DEALLOCATE DATABASES_CURSOR

All you have to do from here is Restore the .BAK file. No need for backup exec

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.