Account Locked out

One of the application owners sent me a message the other day: Login failed for user ‘sa’ because the account is currently locked out. The system administrator can unlock it.

The following code will solve the issue

--------------------CODE START----------------------------------

use master

ALTER LOGIN sa WITH PASSWORD = 'password' UNLOCK

-----------CODE END-------------------------

Source:Jack Vamvas (http://www.sqlserver-dba.com)

Move MDF/LDF files to a designated path

Even though I'd prefer to Backup a database and then move , often a third party supplier
will need it as a MDF or LDF , which they then will have  a script to reattach .
The main steps are :

Detach DB
Move to designated path
ReAttach DB
2 input parameters: Name of DB on SAN, targeted path

The main issue to consider is permissions on network to actually transport the files .

The 'xp_cmdshell' is used (which I don't often recommend , but it does serve its purpose on this script

--------------------CODE START----------------------------------
     DECLARE  @DB_NAME NVARCHAR(128);
     DECLARE @FILE_PATH NVARCHAR(256);
     SET   @DB_NAME = 'MY_DB'
     SET   @FILE_PATH = 'MY_FILE_PATH'
    
      --variables
       DECLARE @SQL_A VARCHAR(300);
       DECLARE @SQL_B VARCHAR(300);
       DECLARE @SQL_C VARCHAR(300);
       DECLARE @SQL_D VARCHAR(300);
       DECLARE @SQL_E VARCHAR(2000);
       DECLARE @DATA_FILE VARCHAR(600);
       DECLARE @LOG_FILE VARCHAR(600);
       DECLARE @MDF_FILE NVARCHAR(256);
       DECLARE @LDF_FILE NVARCHAR(256);
       DECLARE @FILENAME_TBL TABLE(fileType INT, FilePath NVARCHAR(256));
       --establish physical path and filename of MDF and LDF files
       SET @SQL_A = 'SELECT 1,filename from '+@DB_NAME+'.sys.sysfiles WHERE fileid = 1';
       SET @SQL_B = 'SELECT 2,filename from '+@DB_NAME+'.sys.sysfiles WHERE fileid = 2';
      
       INSERT INTO @FILENAME_TBL EXEC (@SQL_A);
    INSERT INTO @FILENAME_TBL EXEC (@SQL_B);
       SET @MDF_FILE = (SELECT FilePath FROM  @FILENAME_TBL WHERE fileType=1);
       SET @LDF_FILE = (SELECT FilePath FROM  @FILENAME_TBL WHERE fileType=2);
    
 
       ---- DETACH DB
       PRINT 'Detach DB : START';
        --set the DB to SINGLE USER
    SET @SQL_C = 'ALTER DATABASE ' + @DB_NAME + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;';
    EXEC (@SQL_C);
      
       EXEC sp_detach_db @DB_NAME,'true'
       PRINT 'Detach DB : FINISH';
      

        --MOVE MDF file
        PRINT 'MOVE MDF & LDF : START';
        DECLARE @dataFile1Cmd VARCHAR(200),
     @dataFile2Cmd VARCHAR(200)
       
        SET  @dataFile1Cmd = 'copy "'+@MDF_FILE + '" "' +  @FILE_PATH + '"';
       
  SET  @dataFile2Cmd = 'copy "'+@LDF_FILE + '" "' +  @FILE_PATH + '"';
        EXEC master..xp_cmdshell @dataFile1Cmd
        EXEC master..xp_cmdshell @dataFile2Cmd
        PRINT 'MOVE MDF & LDF : FINISH';
    
      --REATTACH DB
        PRINT 'Attach DB : START';
        SET @SQL_E = 'EXEC sp_attach_db @dbname = N'''+@DB_NAME +''',@filename1 = N'''+@MDF_FILE+''',@filename2 = N'''+@LDF_FILE+'''';
        PRINT @SQL_E;
        EXEC (@SQL_E);
        SET @SQL_D = 'ALTER DATABASE ['+@DB_NAME+'] SET MULTI_USER WITH ROLLBACK IMMEDIATE';
        EXEC (@SQL_D);
        PRINT 'Attach DB : FINISH';
--------------------CODE END----------------------------------

Source:Jack Vamvas (http://www.sqlserver-dba.com)

Space Integrity Check

There was a requirement to allow  non -technical  users , to manage archiving
their databases on a SQL Server 2005 platform. They wanted to have the capacity to move the databases from a SAN drive

to SATA drive, but they continued to have  a problem regarding space on the destination drive. In other words , having

knowledge of the existing database and comparing to the space available on the destination drive.

The following code satisfied the requirement .

Firstly , create the following procedure (fnSpace_Integrity) , which has 2 input parameters .
@db  (which is the db name)
@intendedDrive   (the targe drive )
Secondly , call the procedure with code similar to the CODE 2 section. You can do whatever you want from the result

-------------------------------------CODE 1 START-------------------------------------
CREATE PROCEDURE [dbo].[fnSpace_Integrity]
@db VARCHAR(200) ,
@intendedDrive char(1),
@spResult int OUTPUT

AS

DECLARE @dbsize bigint,@logsize bigint,
@MB_Free int,
@reservedpages  bigint,
@database_size VARCHAR(100)

--establish db size in MB
select   @database_size   = convert(int,
                                    case -- more than 2TB(maxint) worth of pages (by 8K each) can not fit an int...
                                    when convert(bigint, sum(s_mf.size)) >= 268435456
                                    then null
                                    else (sum(s_mf.size)*8)/1024 -- Convert from 8192 byte pages to Kb then to MB
                                    end)
from   sys.master_files s_mf
where
        s_mf.state = 0 and -- ONLINE
        has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
        and db_name(s_mf.database_id) = @db
    group by s_mf.database_id
    order by 1

PRINT @database_size

create table #FreeSpace(
Drive char(1),
MB_Free int)

insert into #FreeSpace exec xp_fixeddrives
select @MB_Free = MB_Free  from #FreeSpace WHERE drive = @intendedDrive
drop table #FreeSpace

--compare intended drive space with the database size 
if @MB_Free > @database_size
begin
select  @spResult = 1
end
else
begin
select  @spResult = 0
end
--------------------------------CODE 1 END-----------------------------------------------------

--------------------------------CODE 2 START------------------------------------------------------

declare @spaceResult INT
exec fnSpace_Integrity  @DB_NAME_EXISTING,@BackupDrive,@spaceResult OUT
select @spaceResult

if @spaceResult = 0
begin
   
  PRINT 'There is not enough space on drive ' + @BackupDrive + ' to move the database : ' +

@DB_NAME_EXISTING

end

------------------------------CODE 2 END----------------------------------------------------------

Source:Jack Vamvas (http://www.sqlserver-dba.com)