Results 1 to 14 of 14

Thread: Back up file?

  1. #1
    Join Date
    Aug 2005
    Posts
    16

    Question Unanswered: Back up file?

    Hello everyone,
    I have a PC and I am using it as a "mini" server for Temp testing purpose. This PC is on the network. I am wondering if there is a DB file with specific name and extension I can save on our network drive or folder which is backed up automatically overnight daily.
    Any suggestion/comments are highly appreciated!

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    not sure I got your question .. did you want to ask if you could back up your database to a network server
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Aug 2005
    Posts
    16
    That's correct. I have this "mini server" as a testing machine which is not "supported" by IT group since it is not part of data center. I do want my "mini server" backed up. I know all the files on the network drive get backed up overnight so that I am wondering if there is a file or set of files I could push onto our network drive so that my database can be backed up.

    Thank you for the help.

  4. #4
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    moving the 'YourDB.mdf' and 'YourDB_Log.ldf' files to the network drive will help you to back it up

    Cheers....

    baburajv

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Quote Originally Posted by baburajv
    moving the 'YourDB.mdf' and 'YourDB_Log.ldf' files to the network drive will help you to back it up

    Do you think your Database will work after that ???

    What you need to do is schedule a backup of your database using the "Backup database" command and move it over to the file share.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Enigma
    Do you think your Database will work after that ???
    Definitely not, but at least it could be recovered if you had enough expertise.

    On a more serious note, you really need to use the BACKUP DATABASE command to produce a dump file. That file can be copied wherever you like, backed up, whatever... It can be restored using RESTORE DATABASE command, and things will be exactly as they were when you issued the first BACKUP DATABASE command.

    -PatP

  7. #7
    Join Date
    Aug 2005
    Posts
    16
    Thank you for all replies. Just want to be clear, I need to use database backup command to backup my current DB. Can I assume everything (tables, stored procedures, DTS package and etc) I have created in the DB will be saved and backed up on the network. Is my assumption right?
    Many thanks.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    All of the transactions that are committed by the end of the backup operation will be included in the backup file. One file will be produced, regardless of how many files make up your database (you could have an MDF, a few NDF, and a couple of LDF files and still get just one dump file). The dump file created by the backup can then be zipped, copied, etc as you see fit, and that dump can be used to restore the database later.

    -PatP

  9. #9
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by BeijingPinkGirl
    ... (tables, stored procedures, DTS package and etc) I have created in the DB will be saved and backed up on the network. Is my assumption right?
    Many thanks.
    DTS packages are not stored in user databases. If they are stored on the SQL Server, they are in the MSDB database. Soooooo ... if you want to save everything, you will need to backup **all** your databases then move them off server for storage.

    -- This is all just a Figment of my Imagination --

  10. #10
    Join Date
    Aug 2005
    Posts
    16
    would you please more explicit about
    PHP Code:
    DTS packages are not stored in user databases. if you want to save everythingyou will need to backup **all** your databases then move them off server for storage
    How and what I should do in order to backup **all**? Is there a function or command I should do? Please advise.

  11. #11
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Open Books OnLine. Search the index for the "backup" command. Make sure you run a backup for each and every database on your server. Take all the backup files created during this process and copy them to a safe place off your server.

    If you are so inclined, you can create a database mainenance plan to backup all databases on the server in one job than can be run by the SQLServer Agent, if it is active. Look up "Database Maintenance Plan Wizard" in Books OnLine.

    -- This is all just a Figment of my Imagination --

  12. #12
    Join Date
    Aug 2005
    Posts
    16
    I tried to save DTS packages. I have backed up msdb database on my local PC (which is SQL server as well) crogram files\MS SQL server\backup\msdb_backup (I just wanted to test to see if it works the way I expected since this is my first time try so that only saved on the server directory.)
    I went back to Enterprise Mgr and select "Restore database...", and select the file [msdb_backup] which was created through backup command. I got the error message attached. Not sure why. Would you please give me a hint?

    Thank you in advance for helping me out!

  13. #13
    Join Date
    Aug 2005
    Posts
    16
    here is the error msg.
    Attached Files Attached Files

  14. #14
    Join Date
    Jul 2005
    Location
    New Zealand
    Posts
    61
    Have you got Query Analyzer open at the time that you try to back up the database?
    If so, this spawns a process that connects to your database. When you backup a database, SQL Server tries to obtain an exclusive database lock (to ensure that data cannot be changed while the backup is in progress) and it cannot do this if there is another process connected to the database.

    Check out 'How to view current server activity' in Books Online.

    Once you've made sure that there are no other processes connected to your database, you can put the database in 'single user' mode by right-clicking on the database name, selecting Properties and checking the 'Restrict access' box on the Options tab.
    Then backup your database.
    Last edited by Lempster; 10-16-05 at 22:54.

Posting Permissions

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