Category Archives: SQL Server
If you have overcome the subject error when setting up a backup on Microsoft SQL Server Management Studio; below is a series of steps which will provide you the solution
- Under Object Explorer, expand the Server Objects tree
- Under Server Objects; expand Backup Devices
- If there is backup object list, delete it
- Create New by right-clicking on Backup Devices
- In the resulting Window, provide the Device Name and file name as illustrated
- Create the backup and select the Backup Device as the destination
Your SQL Log File is basically your lifeline when it comes to recovering your database to a previous point in time. If Point in time recovery is not important to you; then you must set the Recovery Model to Simple.
To deal with the issue in hand; lets go through a series of simple steps to shrink your log file
- In the Query window, type; backup log <databasename> with truncate_only
- dbcc shrinkfile (databasename_log)
- Once this is done; you will noticed your log file having shrinked considerably within your file directory
If Point in Time recovery is useful to you; I would recommened that you schedule the above activity periodically to keep the size of your log file in check.
- Start SQL Server 2005 Management Studio. To do this, click Start, click All Programs, click Microsoft SQL Server 2005, and then click SQL Server Management Studio.
- Click New Query, and then detach the database as follows:
- Copy the data files and the log files from the current location (D:\folder1\Data) to the new location (E:\folder2).
- Reattach the database. Point to the files in the new location as follows:
To verify that the change has taken place; run the below commanduse mydb go sp_helpfile go
The filename column values should reflect the new locations.