Post reply

Database Backup

  • 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?

  • March 4, 2014 at 1:54 am

    #1693780

    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.

  • March 4, 2014 at 1:58 am

    #1693782

    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."

  • March 4, 2014 at 2:02 am

    #1693784

    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]

  • March 4, 2014 at 8:16 am

    #1693916

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • March 4, 2014 at 8:17 am

    #1693918

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • March 4, 2014 at 8:43 am

    #1693937

    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

  • March 4, 2014 at 11:54 am

    #1694019

    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

  • March 5, 2014 at 1:49 am

    #1694183

    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]

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

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