Automate Your Backup and Restore Tasks

Question

Automate Your Backup and Restore Tasks

Viewing 10 posts - 31 through 39 (of 39 total)

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

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 1

dano2k3 (9/22/2013)
Seems if you are gonna use script, why not powershell?

Possibly because of the following...

dtodd 43553 (9/20/2013)
Hi,
The only reason why i think tsql is better is if you work with multiple DBA's and if the job fails for whatever reason it is much easier to troubleshoot the issue in TSQL.
Just my 2 cents:-D

I'm not bad mouthing PowerShell. It's just that, through the interview process, I've found a whole lot of DBAs that have a (IMHO) limited skillset even with T-SQL never mind how to troubleshoot the likes of PowerShell.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 2

I find this thread interesting that it portrays the current need at the time to move data or databases around. We all know there are certain things that take place in the real world that often times doesn't make sense to DBAs but gets done for a need.

So here is a business need that I had to develop a solution. I needed to port a production database to a development box. Simple enough. I created a SQL Server Agent job to do this. The first step was to create the backup. The second step was to move the backup to the DEV box.

The DEV box had a job that waited for the backup to arrive and do the following: 1) restore the database 2) push the data to the development database.

At first blush it seemed a bit odd. However the process was able to be done so the developers were able to hit the road running first thing in the morning to verify their results without much effort on their part.

Historically I would have built a UAT database starting from a copy of the production database. I'd then push the changes from the DEV database to the UAT database. The developers and users would test their upgrades for final sign off.

Turns out the "new way" required far less effort on all parts and got the job done.

Finally, it appears there is an on going battle to determine where to develop utility type tools to manage SQL Server. I, for one, prefer doing everything in SQL Server. There isn't much I cannot do in SQL Server, since back in my early days being a DBA you didn't have much other tools to choose from.

Today with PowerShell, VB and the greater enhancements to SSIS the field is wide open to play in to get the job done.

None the less, which ever tool is chosen the main goal is "Get 'R Done".

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

Kurt W. Zimmerman (9/23/2013)
Finally, it appears there is an on going battle to determine where to develop utility type tools to manage SQL Server. I, for one, prefer doing everything in SQL Server. There isn't much I cannot do in SQL Server, since back in my early days being a DBA you didn't have much other tools to choose from.

That's my preference, as well. As the quote in my signature says, "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN"T." 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

petitpere SSCrazy Points: 2520 More actions · Answer 4

petitpere

SSCrazy

Points: 2520

September 23, 2013 at 9:09 am

#1652385

+1

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 5

Jeff Moden (9/23/2013)

2Tall (9/20/2013)
Be careful with killing SPIDs. I forget the URL for the CONNECT item on this but you could easily end up with a CPU consuming 0% ROLLBACK that cannot be cancelled without bouncing the service. And, yeah... I'm speaking from actual experience with the problem. It does happen.
It's far better to set the database to SINGLE user mode with ROLLBACK IMMEDIATE, immediately return it to MULTI user mode (in case you lose the connection, you don't want any gereedy web services to snap up the only connection), and then do the restore. The web services don't react quickly enough to get in between.

I find it is much simpler to just set the database OFFLINE - then execute your restore. With the database OFFLINE no one is going to get a connection to that database and your restore will execute without any issues.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 6

For a little twist - I have a requirement to create a reporting copy of the database to be refreshed on a nightly basis. The database must be available to the users by 6am the following morning - with the data as current as we can get.

Our backups are done using Litespeed - so the backup file is already compressed.

The issues here:

1) The database is 4.5TB allocated - with almost 3TB used.

2) Restoring across the network will take too long...

3) Copying backup files across the networs will take too long...

3) Database Mirroring/Log Shipping are not options

a) Mirroring is not an option because we already mirror to DR

b) Log Shipping is not an option because source system is 2005, destination is 2008 R2

Solution:

Worked with the storage team to create a mirror of our backup drive. The mirrored drive can then be split - so we can present the snapshot of the backup drive to our reporting server. Once presented, we can mount the volume - perform the restores locally - dismount the volume and re-synchronize the mirror set.

Steps included in the agent job:

1) Split the mirror

2) Mount the volume

3) Generate restore scripts (scripts generated from latest available backup and all log backups available up to this time)

4) Execute generated scripts to restore databases

5) Dismount the volume

6) Synchronize the volume

7) Post restore tasks (e.g. add reporting users, fix up orphaned users (if needed), set databases read only, etc...)

This process allows us to restore the copy from live in about 2.5 hours. It also provides us with validation that our backups are good - and allows us the ability to perform an integrity check on the database (granted - not on the current but it is better than not running them).

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Michael Meierruth SSChampion Points: 10051 More actions · Answer 7

Just out of curiosity, what have you got in that 3TB database?

Somewhere on this forum I saw an invitation by someone to all who have huge databases and talk about them.

Can you SMS me a copy?:-)

Ok MMS is fine too.:w00t:

Jonathan Mallia SSCertifiable Points: 5192 More actions · Answer 8

It's far better to set the database to SINGLE user mode with ROLLBACK IMMEDIATE, immediately return it to MULTI user mode ....

The 'with ROLLBACK IMMEDIATE' will automaticcaly kill the connections or will wait until the connections are dropped and will not accept new connections?

Thanks:)

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 9

Jeffrey Williams 3188 (9/23/2013)

Jeff Moden (9/23/2013)
2Tall (9/20/2013)
Be careful with killing SPIDs. I forget the URL for the CONNECT item on this but you could easily end up with a CPU consuming 0% ROLLBACK that cannot be cancelled without bouncing the service. And, yeah... I'm speaking from actual experience with the problem. It does happen.
It's far better to set the database to SINGLE user mode with ROLLBACK IMMEDIATE, immediately return it to MULTI user mode (in case you lose the connection, you don't want any gereedy web services to snap up the only connection), and then do the restore. The web services don't react quickly enough to get in between.
I find it is much simpler to just set the database OFFLINE - then execute your restore. With the database OFFLINE no one is going to get a connection to that database and your restore will execute without any issues.

That's also much better than killing SPIDs. Just remember that if someone is currently in the database when you try to take it OFFLINE, you're still going to have to set the database to the single user mode with the rollback or the system will give you an error about people still using the database after what seems to be an unreasonable amount of time to wait for an error.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 10

Jonathan Mallia (9/24/2013)
It's far better to set the database to SINGLE user mode with ROLLBACK IMMEDIATE, immediately return it to MULTI user mode ....
The 'with ROLLBACK IMMEDIATE' will automaticcaly kill the connections or will wait until the connections are dropped and will not accept new connections?
Thanks:)

No. ROLLBACK IMMEDIATE doesn't kill connections. The SET SINGLE_USER does that.

The best thing to do is to lookup ALTER DATABASE in "Books Online" and read about these options and the related caveates before using them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)