Category: SQL Server

Backup failed for Server, The media is formatted to support 2 media families Error

Sunil PadmanabhanLeave a comment

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

BackupDevice_SQLServer

Reduce Size of Log File of your SQL Server Database

Sunil PadmanabhanLeave a comment

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

  1. In the Query window, type;  backup log <databasename> with truncate_only
  2. dbcc shrinkfile (databasename_log)
  3. 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

Sunil PadmanabhanLeave a comment
  use master
   go
   sp_detach_db ‘mydb’
   go
  • 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:
use master
  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
   go

The filename column values should reflect the new locations.