Home / server / Questions / 98915
Accepted
MJ.
Asked: 2010-01-03 19:35:16 +0800 CST

MSSQL Backup Question

  • 772

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!

sql-server

4 Answers

  1. Best Answer

    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.

    • 1

  2. 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.

    • 1

  3. 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.

    • 1

  4. 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

    • 0