Questions

Backing up - Microsoft SQL Server\MSSQL.1\Data "folder"

+
0 Votes
Locked

Backing up - Microsoft SQL Server\MSSQL.1\Data "folder"

jjstccean
I'm new to the sql environment and need to know if and when I backup the above mentioned data folder "Microsoft SQL Server\MSSQL.1\data" in case of any hardware failures...do I also need to backup any of the other folders under "Microsoft SQL Server\MSSQL.1"? What about SQL Server settings associated with the data folder...should I backup the the entire Microsoft SQL Server\MSSQL.1 folder? Let's assume I had some hardware failure and reinstalled my windows system, will it then be ok to simply restore these folders after re-installing the SQL server?
  • +
    0 Votes
    BrandonB81

    jjstccean, don't forget the transaction log. I'm familiar with SQL, but I'm no expert, but the few times I've needed to restore from a backup, I have just used the data file and the transaction log, but that hasn't been with a server rebuild, only recovering older copies of the database after attempting things and breaking the existing one.

    The first thing you would definitely want to do is make sure you're getting all the data files you need to be saving, after that, somebody else will point out if I missed something.

    Under Enterprise Manager, right click on the database and go to properties. Look under the "Data Files" and "Transaction Log" tabs, and you will see all the files being used. As mentioned above, I've never had issues so long as I saved all the files listed under those tabs. I recommend looking here, because sometimes people don't drop their files in the default areas, and I'd hate for you to be backing up an old copy or something to that nature. This will at least make sure you've copying the right folders.

    +
    0 Votes
    ms

    Do not backup SQL in a regular (NTBackup) way. MSSQL has its own facility to backup the databases to disk. These files should be included in the standard backup.

    It is called maintenanceplan in SQL.

    Resources in Books Online for MSSQL:
    http://search.microsoft.com/results.aspx?mkt=en-US&setlang=en-US&q=books+online

    You can also buy an addition to your backupsoftware which will fully backup the servers databases.

  • +
    0 Votes
    BrandonB81

    jjstccean, don't forget the transaction log. I'm familiar with SQL, but I'm no expert, but the few times I've needed to restore from a backup, I have just used the data file and the transaction log, but that hasn't been with a server rebuild, only recovering older copies of the database after attempting things and breaking the existing one.

    The first thing you would definitely want to do is make sure you're getting all the data files you need to be saving, after that, somebody else will point out if I missed something.

    Under Enterprise Manager, right click on the database and go to properties. Look under the "Data Files" and "Transaction Log" tabs, and you will see all the files being used. As mentioned above, I've never had issues so long as I saved all the files listed under those tabs. I recommend looking here, because sometimes people don't drop their files in the default areas, and I'd hate for you to be backing up an old copy or something to that nature. This will at least make sure you've copying the right folders.

    +
    0 Votes
    ms

    Do not backup SQL in a regular (NTBackup) way. MSSQL has its own facility to backup the databases to disk. These files should be included in the standard backup.

    It is called maintenanceplan in SQL.

    Resources in Books Online for MSSQL:
    http://search.microsoft.com/results.aspx?mkt=en-US&setlang=en-US&q=books+online

    You can also buy an addition to your backupsoftware which will fully backup the servers databases.