Database Backup

Question

Database Backup

vsamuel

Mr or Mrs. 500

Points: 566
More actions
March 4, 2014 at 1:26 am

#290546

I have a database in Sql server management studio which I want to create a full database backup in my laptop. The database is in a server which in on a Local Area Network and resides within the building of my office. I would be happy if it does that automatically. How should I do this?

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

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

SQLCJ SSCommitted Points: 1791 More actions · Answer 1

Can you please post what did you try till now ?

Have you tried taking the backup of the database in the shared folder of your laptop. (assuming you are connected tothat SQL server from you laptop) You may create a maintenance plan to automate this.

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

Take the backup of the server locally and transfer it to the laptop. If you would like to automate you can do so by creating job.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

dbalmf SSCrazy Points: 2063 More actions · Answer 3

Hi,

I'm assuming your laptop is part of the same domain as the SQL Server. Create a shared folder on your laptop, You will need to give the user which starts the SQL Server Service permissions on the folder.

Then you can use this code which backs up the AdventureWorks database:

USE AdventureWorks

GO

BACKUP DATABASE AdventureWorks

TO DISK = '\\MYLAPTOP\\MYSHAREDFOLDER\AdventureWorks.BAK'

GO

Ta

David

============================================================
David

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 4

dbalmf (3/4/2014)
Hi,
I'm assuming your laptop is part of the same domain as the SQL Server. Create a shared folder on your laptop, You will need to give the user which starts the SQL Server Service permissions on the folder.
Then you can use this code which backs up the AdventureWorks database:
USE AdventureWorks
GO
BACKUP DATABASE AdventureWorks
TO DISK = '\\MYLAPTOP\\MYSHAREDFOLDER\AdventureWorks.BAK'
GO
Ta
David

If you're going to do that from a production box, make sure you use COPY_ONLY in case there are DIFFERENTIAL backups, otherwise you'll break that connection making any disaster recovery situation much worse than it needs to be.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 5

Assuming it's getting backed up on a regular basis, I'd just access it's backup to do the restore. You can restore from a file share using "\\myfileshare\myfilename.bak" just fine.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 6

vsamuel (3/4/2014)
I have a database in Sql server management studio which I want to create a full database backup in my laptop. The database is in a server which in on a Local Area Network and resides within the building of my office. I would be happy if it does that automatically. How should I do this?

Follow Grant's advice. Also if you schedule the backup to be automatic, then you should make sure that your laptop is connected to network everyday during the backup. Otherwise the job will fail.

--

SQLBuddy

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

Grant Fritchey (3/4/2014)
dbalmf (3/4/2014)
Hi,
I'm assuming your laptop is part of the same domain as the SQL Server. Create a shared folder on your laptop, You will need to give the user which starts the SQL Server Service permissions on the folder.
Then you can use this code which backs up the AdventureWorks database:
USE AdventureWorks
GO
BACKUP DATABASE AdventureWorks
TO DISK = '\\MYLAPTOP\\MYSHAREDFOLDER\AdventureWorks.BAK'
GO
Ta
David
If you're going to do that from a production box, make sure you use COPY_ONLY in case there are DIFFERENTIAL backups, otherwise you'll break that connection making any disaster recovery situation much worse than it needs to be.

In addition, if you're going to automate this in a database job and backup directly to your laptop, you have to allow for the possibility that your laptop is offline when the job runs. I would at least consider the approach of using the existing production backup on your network and scheduling a task on your laptop that would copy the most recent one down to your hard drive. This would minimize the impact on the database itself.

You also have to consider if your laptop has full-disk encryption on it if there is any sensitive information in that database. You don't want to end up on the news where a laptop containing a database of PII or HIPPA data was stolen out of a car.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

dbalmf SSCrazy Points: 2063 More actions · Answer 8

Grant makes a good point about using COPY_ONLY and Ed's point about data security is a very valid one in this digital age. Follow their advise..

============================================================
David

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]