do you need to take take server offline to backup, what is point of attaching and detaching?

Question

do you need to take take server offline to backup, what is point of attaching and detaching?

adam spencer

Hall of Fame

Points: 3926
More actions
November 16, 2005 at 4:42 pm

#111188

do you need to take take server offline to backup, what is point of attaching and detaching?

Thanks you!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Lotta posts this afternoon, Adam. Got a quiz tomorrow?

Native SQL Serve backups don't require the server to be offlilne and most 3rd party backup software have "agents" that backup without taking it offline also.

Detaching and attaching a database is used to move a database to another server or another disk. You can detach, move the database files to a new location, then attach. See 'Attaching and

Greg

adam spencer Hall of Fame Points: 3926 More actions · Answer 2

thanks so much . No I do not have a quiz, I just have pent up questions. kinda like blue balls if you will.

David Brountas SSC Veteran Points: 204 More actions · Answer 3

Adam,

As stated you do not need to stop SQL to take a backup, however, if the database you take is large the users may see a performance hit if you run a backup during business hours. I have a 12 GB database here. If I ran it during the day, I would cripple the users here. One suggestion is to disconnect all users off the database at the end of the evening, before the backup runs. You can automate the backup through a SQL Agent. I would suggest you backup up WITH INIT on the backup. You can look at the SQL commands for more specifics on the command and create a step that performs a full backup or full and differentials.

One of the challenges I had with SQL backup software was the ability to restore databases. It can be a challenge in some products if you want to restore to a different server, so I resort to full, differential flat file backups instead. It gives me more control of the files and I can restore them easily where I want them. There is a large debate over transaction log backups as well, so read up on that

So, one thing I would suggest is to really think about the types of backups you need to do and plan accordingly.

Good luck.

David.

adam spencer Hall of Fame Points: 3926 More actions · Answer 4

adam spencer

Hall of Fame

Points: 3926

November 18, 2005 at 12:06 pm

#604716

Very good thanks David

SQLBill SSC Guru Points: 51440 More actions · Answer 5

Wow David, 12 GB and you have to kick off the users to do a backup? My major database is currently 248 GB (yes that is 2 hundred and forty-eight gigs) and I always back it up while it's being used. Full backups once a day and it never affects my users. I backup to disk and then copy the backup file to tape all while the database is in use.

-SQLBill

David Brountas SSC Veteran Points: 204 More actions · Answer 6

Thanks. 248 GB is a big database! Do you do anything special on the backup or are you using SQL agent to perform the backup?

David.

SQLBill SSC Guru Points: 51440 More actions · Answer 7

I use the 'native' SQL Server commands to backup the database and log to disk, then I use Backup Exec to copy the backup files to tape. All of this is done daily while the database is operational. (By the way, as of today it is 260 GB).

-SQLBill