Moving database from one location to another

Hi All,

Recently I ran across a situation where in there was a GIS application which was trying to feed a large amount of data (more than 22GB) to SQL server. This led to the increase of the data file (.mdf) and eventually this led to filling the disk space. So came the situation of moving the database to a different location where there was more room for growth !!

Solution/Approach:
1. using Attach/detach method.
use adventureworks
go

sp_helpdb 'adventureworks' ---> this will show you the logical and physical file names of the data and the log files for a particula database

Detach the database:

USE master;
GO
EXEC sp_detach_db @dbname = N'AdventureWorks';
GO

Move the files data(.mdf), secondary data file(.ndf) and log files (.ldf) to the desired location.

Attach the database:


USE master;
GO
CREATE DATABASE MyAdventureWorks
    ON (FILENAME = 'C:\MySQLServer\AdventureWorks_Data.mdf'),
    (FILENAME = 'C:\MySQLServer\AdventureWorks_Log.ldf')
    FOR ATTACH;
GO

This method has certain drawback and is not recomended by BOL: "We recommend that you move databases by using the ALTER DATABASE planned relocation procedure, instead of using detach and attach."

So the recomended method is to use ALTER DATABASE.


1. sp_helpdb 'adventureworks' -> will give logical and physical file names

2. use the alter database command to move the files.

ALTER DATABASE adventureworks
MODIFY FILE (NAME = adventureworks_dat, FILENAME = 'E:\adventureworks_database\adventureworks.mdf');
ALTER DATABASE adventureworks
MODIFY FILE (NAME = adventureworks_dat2, FILENAME = 'D:\adventureworks_data_file2\adventureworks_dat2.ndf'); --- here if you have any secondary data file, if not ignore this
ALTER DATABASE adventureworks
MODIFY FILE (NAME = adventureworks_log, FILENAME = 'E:\adventureworks_database\adventureworks.ldf');


ALTER DATABASE adventureworks SET OFFLINE
WITH ROLLBACK IMMEDIATE --- this will take your database immediately offline

Now move the data files and log file to the respective desired location.

ALTER DATABASE adventureworks SET ONLINE -- this will take your database online

This will move your database from one location to another and is more efficient way to do.

This is a good link to follow: http://www.sqlservercentral.com/articles/Administration/65896/

Hope this helps !!

\\K

SQL2005 [264] An attempt was made to send an email when no email session has been established.

Hi All,

Below is the problem which is more obvious to face when a fresh install of SQL server 2005 is done.

Problem: "[264] An attempt was made to send an email when no email session has been established." This message is logged in the SQL agent error logs.

The message is from a job which will send email to an operator when it is completed. In the operator notification setting, I the correct email address has been entered for the 'Email Name' text box, such as kkk@xyz.com
However the Database Mail is configured and I can send/receive a test email.

Solution/Approach:

1. In Management Studio, right-click on SQL Server Agent and select "Properties."
2. Click "Alert System"
3. Uncheck "Enable mail profile"
4. Click "OK"
5. right-click SQL Server Agent again and select "Properties."
6. click "Alert System"
7. Check "Enable mail profile"
8. Click "OK"
9. Restart SQL Server Agent. (this will not bring any thing down, unless jobs are running at the moment when SQL agent is restarted i.e the running jobs will fail, but they can be ran after the agent is restarted)

Hope this helps !!!

\\K

SSIS package fails when scheduled as SQL server agent job

Problem: When run through the BIDS or Visual Studio, the SSIS package runs well. But when scheduled as a SQL agent job, the SSIS package fails with the error message:
" Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. "

Solution: It took me a while to figure out the problem and the same was mentioned at SSC.
The standard security on an SSIS package is "EncryptSensistiveWithUserKey" which only allows the user that created it to be able to run in as long as there is sensitive information (passwords) stored within the package.

A workaround that I am using is that I changed the package security to "EncryptAllWithPassword" which protects the entire package with a password. When you set the package as a job step, go into the properties of that job step, click on the "Command Line" tab and you should be prompted for the password. After you enter the password, the /DECRYPT switch should show up in the command line and the job should then be able to run under a different user.

Users won't be able to see the password because they will need to enter it before viewing the command line for the job step.
(The issue is addressed nicely with many inputs at http://www.sqlservercentral.com/Forums/Topic349385-148-1.aspx)