Category Archives: SQL Server
Backup failed for Server, The media is formatted to support 2 media families Error
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
Reduce Size of Log File of your SQL Server Database
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.
Moving SQL Datafile to alternate location in SQL Server 2005
- 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:
go
sp_attach_db ‘mydb’,’E:\folder2\mydbdata.mdf’,’E:\folder2\mydblog.ldf’
go
To verify that the change has taken place; run the below command
use mydb go sp_helpfile goThe filename column values should reflect the new locations.
