Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2013
    Posts
    35

    Question Unanswered: Database Backup - SQL Server Question

    Hello everybody. My name is Edward and I am a systems administrator with a real estate company in the Buffalo, NY area.

    We utilize a property management tool named Yardi/Voyager for our properties scattered across the area. I have been working closely with Yardi to handle different technical matters. The problem I am running into is the fact that the Director of IT resigned from the company 3 weeks ago. This has left me in a position to run the entire IT department by myself with only 4 months of experience in the IT world.

    The only way Yardi is able to help me is by backing up our live database from our SQL server, compressing the file and then sending it to them via FTP server. The size of our database is roughly 26 GB's. We utilize Windows Server 2008. I do not have enough space on the SQL server to do an actual full live backup.

    Is there a way to connect a large flash drive to the server somehow so I can backup this database or utilize another way to backup somewhere other than the SQL server which doesn't have enough space? I notice that you can copy from a local disk and paste into the server but you can't do it the other way around. I can't just copy a file from the server and place it locally. Or is there a way to send this backup to my local computer where there is enough room? Our last database backup was back in November. Any suggestions on how to handle this matter?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Welcome to the forum. I think we have all been thrown into the fire in somewhat similar ways to how you describe. Let the learnin' begin! ;-)

    You can set up a fileshare on any server (in the domain), and have SQL Server write the backup file to that fileshare, IF SQL Server is running as a domain account. If SQL Server is running as local system, or network service, then you will not be able to do that without changing the login for the service. Changing the login can have some....interesting.... effects on Kerberos authentication, so I would not suggest running down that route just yet, and certainly not without a test server.

    SQL Server will happily write to any local drive, so you could get an external drive, and plug it into the back of the SQL Server, but you will want to think about how quickly you will be able to write a 26 GB file to an external drive. While a backup is running, you will not be able to do a log backup, or truncate the transaction log, so you may want to hold off on doing the backup at peak business hours.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Normally I'd agree with MCrowley but if your last backup is from November, I'd make a backup NOW any way that I could.

    There's no guaranty that this will work, but let's try the simplest answer first. If there is another disk drive with enough space for a backup file on the Windows Server that is running the SQL Server, then use SSMS (SQL Server Management Studio) to put the backup there.

    If there isn't a drive on the Windows Server with enough free space for the SQL backup, then try a long shot and figure out the UNC to a spare folder that has sufficient space to hold a backup file, and try to send the backup there.

    If neither of those ideas work, then plug a spare drive with enough space into the Windows Server and store the SQL Backup file onto the removable drive... This is messy, but it rarely fails.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Nov 2013
    Posts
    35
    Thank you both for the advice. I am going to look into this some more and possibly attempt on of your suggestions. I need to dive a little deeper into this SQL server and find out some more information. I think you guys have put me on the right path though. I'll update if I proceed with anything and if I am successful. Thanks for now guys.
    Last edited by LaC0saNostra; 01-15-15 at 19:45. Reason: Another question

  5. #5
    Join Date
    Nov 2013
    Posts
    35
    Pat. Why is backing up this database onto a removable drive messy?

  6. #6
    Join Date
    Nov 2013
    Posts
    35
    Hey guys. I was successful in backing up my database. I then zipped a 26 GB file down very small to only a couple GBs. Tomorrow I need to send it to the company via FTP. I just deleted the only backup we had from September. Risky yes but it made room for a new backup which works. Thanks for the advice

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If you can get at least a weekly backup of the database (on a file share, on tape, etc.), you will have much less stress in your life. A day will come when one of the business people come to you and sheepishly say they deleted a bunch of data they didn't mean to, and is there any way you can keep them from getting fired? With backups, you can help that person (and the business, and look like a hero besides), without a backup, you get into a world of pain. Just continuing what Pat started to imply.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by LaC0saNostra View Post
    Pat. Why is backing up this database onto a removable drive messy?
    Sorry, I just discovered this post...

    When you create a backup on a removable disk volume, it leaves information on the SQL Server that will lead SQL and other tools to assume that the backup file is present and online. Once you remove the media (disconnect the USB, etc.) there is no longer viable media or backup files online, and some tools don't handle that very well.

    This doesn't drastically hurt anything, and the backup is still valid and completely usable. There can just be some things that "go bump in the night" when you make backups on removable media, and because you aren't experienced with these tools or SQL in general, that can cause you confusion or consternation that is completely unnecessary.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •