locked
Back up file RRS feed

  • Question

  • I had to make a backup file of the adventureworks database then restore it. Now I am tring to find where the adventureworks.mdf and andventureworks.ldf file are located so I can turn them in to show I did the assignment.
    Tuesday, December 4, 2012 7:36 PM

Answers

  • Several ways to find it, but here are some easy ones.  Connect to the DB and run:

    exec sp_helpfile

    or

    SELECT * FROM sys.database_files

    SQL Server uses a default location for backups, but this is configurable in the backup syntax/wizard.  As a result, you can run the above queries to determine where the DB is located.

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Tuesday, December 4, 2012 7:41 PM
  • Hello,

    The informations are stored in the backup file as well; if you want to restore a backup to the same database you backuped, then you don't need to known the location of the database files.

    Or are you trying to archive something different?


    Olaf Helper

    Blog Xing

    Tuesday, December 4, 2012 7:43 PM
  • How did you backup the database?  If you used the BACKUP DATABASE syntax, you supply the path where the .bak file will get created and that's where you'll find the file.  In the UI, you also supply this value, but it is prepopulated to the default backup location highlighted below. 

    If you need to turn in .mdf/.ldf files that are usable, you may want to DETACH the database, then grab the two files to turn in.  The default path for database creation is the C:\Program Files\Microsoft SQL Server\MSSQL10_50.R2\MSSQL\DATA\ folder for SQL Server 2008 R2 (named instance).  It varies slightly based on default/named instance and the version you are running, but this should get you close enough to track it down.

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Tuesday, December 4, 2012 9:12 PM
  • Not sure , Incase if you want to see what the backup file contents the you can use the->

    RESTORE FILELISTONLY (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms173778.aspx

    if not elaborate more on this.


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Tuesday, December 4, 2012 9:14 PM

All replies

  • Several ways to find it, but here are some easy ones.  Connect to the DB and run:

    exec sp_helpfile

    or

    SELECT * FROM sys.database_files

    SQL Server uses a default location for backups, but this is configurable in the backup syntax/wizard.  As a result, you can run the above queries to determine where the DB is located.

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Tuesday, December 4, 2012 7:41 PM
  • Hello,

    The informations are stored in the backup file as well; if you want to restore a backup to the same database you backuped, then you don't need to known the location of the database files.

    Or are you trying to archive something different?


    Olaf Helper

    Blog Xing

    Tuesday, December 4, 2012 7:43 PM
  • This is a lab I am doing to teach how to do a backup. I have done the backup and restore and need to turn in the 2 files to show that I completed the lab
    Tuesday, December 4, 2012 7:46 PM
  • Sorry, I don't understand what you mean; could you please explain it more detailed?

    Olaf Helper

    Blog Xing

    Tuesday, December 4, 2012 8:04 PM
  • How did you backup the database?  If you used the BACKUP DATABASE syntax, you supply the path where the .bak file will get created and that's where you'll find the file.  In the UI, you also supply this value, but it is prepopulated to the default backup location highlighted below. 

    If you need to turn in .mdf/.ldf files that are usable, you may want to DETACH the database, then grab the two files to turn in.  The default path for database creation is the C:\Program Files\Microsoft SQL Server\MSSQL10_50.R2\MSSQL\DATA\ folder for SQL Server 2008 R2 (named instance).  It varies slightly based on default/named instance and the version you are running, but this should get you close enough to track it down.

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Tuesday, December 4, 2012 9:12 PM
  • Not sure , Incase if you want to see what the backup file contents the you can use the->

    RESTORE FILELISTONLY (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms173778.aspx

    if not elaborate more on this.


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Tuesday, December 4, 2012 9:14 PM