Solved

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

Posted on 2014-12-09
12
36 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
  • 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 68

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 45

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
 

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 45

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 68

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 45

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 68

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 45

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

This article offers some helpful and general tips for safe browsing and online shopping. It offers simple and manageable procedures that help to ensure the safety of one's personal information and the security of any devices.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

743 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now