Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 49
  • Last Modified:

Can I make a backup of a live SQL 2005 Database while it's in use?

Can I make a backup of a live SQL 2005 Database while it's in use?

Specifically, I have a large database (96.5Gb) on my SQL 2005 Server.  (I have a program that is in widespread daily use in my Chicago office that uses the SQL 2005 database).  I need to make a FULL backup copy of that database to send to a fellow IT guy in our Philadelphia office so that he can mount it into a test environment that is set up there for the purpose of testing an upgrade to our program.

Can I initiate a standard SQL Server FULL database backup of that database while it is live and being used by users, or will this bring the SQL Server (and thereby also the program itself) to a crashing halt?

I don't think this matters much, but both of the servers (the SQL server and the application server) are both in a VMware environment as guests.

Thanks in advance for any and all input.
0
aiico
Asked:
aiico
  • 4
  • 4
  • 3
  • +1
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
You can do it.  Will it bring it to it's knees ?  It shouldn't - but that may depend on  your disk layout - write the backup to a disk that the database isn't using and you should be fine as long as your server isn't already over extended.

I've run backups during the day on my servers before in special cases, etc and not noticed too much of a bump.
0
 
QlemoC++ DeveloperCommented:
At that size storing the backup on a different disk is mandatory. At least for physical servers. Depending on the disk layout of the VM host, it might be a good idea to temporarily add another virtual disk for the backup file.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes you can run it live. Anyway, it's usually how in general backups are taking in SQL Server.
But if you only need a backup for testing purpose why not send him an old backup (the last full backup from your maintenance plan, for example).
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
aiicoAuthor Commented:
Vitor -

Unfortunately, I can't really send him a previous nightly backup file because our environment uses Redgate Backup Pro, which does a special compression, strings the backup across multiple streams and creates it's own proprietary .SQB file format instead of the standard SQL .BAK format.  Onsite in my office, if we need to do a restore, we just run the restore job through Redgate again which interfaces with SQL properly.

The Philly location doesn't have Redgate, so that's out.  I'm trying to get him a standard .BAK copy that he can mount normally through the default SQL server Mgmt interface.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I see. Then execute a Backup with COPY option so won't mess with your backup plan.
0
 
QlemoC++ DeveloperCommented:
And you can't just mount that backup - it is not like a detached DB file(s). You (he) will have to restore, and change the paths as stored in the backup to something suitable for the local MSSQL instance.
0
 
aiicoAuthor Commented:
Vitor -

I was also thinking about a COPY as well so as to not mess with the scheduled backup process, but ran into this on a Microsoft site:

SQL Server Management Studio 2005 does not support copy-only backups.

• For a copy-only full backup, the required BACKUP syntax is:
 BACKUP DATABASE database_name TO <backup_device> … WITH COPY_ONLY


At the risk of sounding like a Rube, I am most definitely not a DBA, and we unfortunately do not have a DBA on staff.  Is the above command that Microsoft references done as a query against the appropriate database?

I understand what they mean by SQL Server Mgmt Studio 2005 not supporting the COPY_ONLY option, as I definitely could not find it as an option in the "Backup Type" drop-down list.  However, I'm guessing it can be run manually within SQL Server Mgmt Studio 2005 as a direct query against the DB???

Thanks again
0
 
aiicoAuthor Commented:
Olemo -

Are you referring to just the paths to the DB's MDF and LDF as they are currently specified on my SQL Server for that DB, or are there other paths needing to be changed as well?

Thank you
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
SQL Server Management Studio 2005 does not support copy-only backups.
Means that you can't do it using the SSMS interface but you can run a SQL command (query) to perform the backup:
BACKUP DATABASE database_name TO <backup_device> … WITH COPY_ONLY …
0
 
aiicoAuthor Commented:
Vitor -

I've got the SQL Command syntax worked out as well as the "Restore VerifyOnly" command syntax for checking it afterward,  I just have a question on whether or not to use the "With Checksum" option in the backup.  I have read that using it can often adversely affect the processing workload, throughput and backup time.  The DB is 96.5Gb.  Thoughts?
0
 
QlemoC++ DeveloperCommented:
Yes, I'm talking of exactly those MDF, NDF and LDF files (or whichever extension they have).
Can't really give advise on the checksum option. But created locally, then zipped and transfered, should make sure the file is intact, so I suppose that would be sufficient. The copy to NAS or similar might be an issue, if you do that, and this operation should be made safe. (A corrupt ZIP is detectable, a corrupt backup might not be).
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Since it's a backup to be restored in a dev environment I wouldn't bother with the checksum option.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now