Solved

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

Posted on 2014-12-09
12
42 Views
Last Modified: 2016-01-16
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
Comment
Question by:aiico
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40489915
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
 
LVL 70

Expert Comment

by:Qlemo
ID: 40489958
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40490824
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
Don't Miss ATEN at InfoComm 2017!

Visit booth #2167 to see the  new ATEN VM3200 32 x 32 Modular Matrix Switch. Other highlights include the VE8950 4K HDMI Over IP Extender, VS1912 12-Port DP Video Wall Media Player  and VK2100 ATEN Control System. Register now with Free Pass Code ATEN288!

 

Author Comment

by:aiico
ID: 40491459
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40491512
I see. Then execute a Backup with COPY option so won't mess with your backup plan.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 40491828
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
 

Author Comment

by:aiico
ID: 40491840
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
 

Author Comment

by:aiico
ID: 40491853
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
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40491863
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
 

Author Comment

by:aiico
ID: 40492047
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
 
LVL 70

Expert Comment

by:Qlemo
ID: 40492214
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40493285
Since it's a backup to be restored in a dev environment I wouldn't bother with the checksum option.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question