Tag Archive | "SQL Server"

Tags:

Set default language and database for db user


Execute the following SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate the setting of default langauage and default database:

– Setting  default language for login
use master
              
EXEC sp_defaultlanguage‘mydbuser’, ‘us_english’
GO
             
– SQL Server  2005 and SQL Server 2008
              
ALTER LOGIN mydbuser WITH DEFAULT_LANGUAGE = British;
GO
              
– Languages in  SQL Server 2008 with dateformat
SELECT LanguageID = langid,
       name,
       alias,
       dateformat
FROM sys.syslanguages
ORDER BY langid
GO
————
              
– Setting  default database for login
EXEC sp_defaultdb‘mydbuser’, ‘mydatabase’
              
– SQL Server  2005 and SQL Server 2008
              
ALTER LOGIN mydbuser WITH DEFAULT_DATABASE = mydatabase;
GO
———

Share

Posted in SQL ServerComments (0)

Tags:

Move MSSQL Database File Location


MS SQL server default installed to your system C drive, as long as you have sufficient disk space you do no need to worry about the following, but in case you need to move the data to another location, you need to follow the below steps.

Let’s take the database "websitedb" for example, to move the data and log file to D:\MSSQL Data directory.

1. Run the following statement.

ALTER DATABASE websitedb SET OFFLINE;

2. Move the file or files to the new location (under C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data).
3. For each file moved, run the following statement.

ALTER DATABASE websitedb MODIFY FILE ( NAME = websitedb_log, FILENAME = ‘D:\MSSQL Data\websitedb_log.ldf‘ );

–repeat the above to move the websitedb.mdf as well.

4. Run the following statement.

ALTER DATABASE websitedb SET ONLINE;

5. Verify the file change by running the following query.

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N’<websitedb>’);


More details can be found at this page

http://msdn.microsoft.com/en-ca/library/ms345483.aspx

Share

Posted in SQL ServerComments (0)

Tags: ,

SQL server backup problem


When you make a backup of your SQL Server database you get the following error:

TITLE: Microsoft SQL Server Management Studio
——————————

Backup failed for Server ‘localhost’.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=
Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&
EvtID=Backup+Server&LinkId=20476

——————————
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The backup of the file or filegroup "sysft_SearchCatalog" is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476

 It’s because of your Full Text Catalog offline or the parameter incorrect. For my case, I used my local design path but this path doesn’t exist on live server and get the above error when backup. You can generate the sql query to view the source.

Solution: you can simply delete the catalog or rebuild it (expand your db > Storage > Full Text Catalog)

Share

Posted in SQL ServerComments (3)