SQL Server - Restore Database to different instance/DB name

Before executing the restore command, an empty DB with the new DB name, db_bbb in this example, have to be created under the SQL Server instance you like to move to.

Restore Database db_bbb  -- the New DB Name
from disk = 'D:\backup\db_aaa.bak'  -- Original DB Backup with the logical name db_aaa
with replace,
move 'db_aaa' to 'D:\data\db_bbb.mdf', -- Move the logical DB to the physical path with the new DB name
move 'db_aaa_log' to 'D:\data\db_bbb_log.ldf' -- Move the Logical Log to the physical path with the new "DB name_log"


--Set Single user Mode before restore:
ALTER DATABASE [db_bbb] SET SINGLE_USER with rollback immediate;

--Set Multi user Mode after restore:
ALTER DATABASE [db_bbb] SET MULTI_USER

--Show Logical name of DB
RESTORE FILELISTONLY
FROM DISK = 'D:\backup\db_aaa.bak'

use db_bbb
select FILE_ID, name as [logical_file_name], physical_name

from sys.database_files

Alter database [db_bbb] modify file ( name = db_aaa, newname = db_bbb  )
Alter database [db_bbb] modify file ( name = db_aaa_log, newname = ab_bbb_log )

jqGrid - editCell not allows edit?

I spend an hour or two to figure out I forgot to return true in an empty beforeSelectRow event I had added, and this made the grid didn't allow cell editing...

Hope it may saves you a bit of times.

BTW, if you're looking for how to change the column editable in run time, here is an example:

var grid1 = $('#grid1');   
    grid1.jqGrid({
...
colNames:[ 
            'name','note','new'
 ], 
 colModel:[
{name:'name',index:'name',editable=false},
{name:'note',index:'note',editable=true},
{name:'new',index:'new',editable=false}
],
cellEdit:true,
cellsubmit: 'clientArray',
...

beforeSelectRow : function(rowid) {        
            var data = grid1.jqGrid('getRowData', rowid);
            var cm = grid1.jqGrid('getColProp','note'); 

            cm.editable = (data.new=='Y') ? true:false; //'note' will be allowed to edit if 'new' is 'Y'
            return true;
        },

javascript - call an inner function from outside

function outer(){
        var newObj = new Object();
        function inner(x){
                alert(x);
        }
        newObj.inner = inner;
        return newObj;
}
   
var outerObj = new outer(); 
outerObj.inner(5);

jqGrid - Move/Add - buttons/ toolbar/ pager to Top

$('#grid1).jqGrid({
...
pager: $('#pager1'),    
viewrecords: true,       
toppager:true,
...});


$('#grid1').jqGrid('navGrid',"#pager1", {edit:false,add:false,del:false,search:false,refresh:false,cloneToTop:true}); //You can disable/enable any jqGird default buttons you like


//Add a custom button on the top-pager
$('#grid1).jqGrid('navGrid','#grid1_toppager_left')
    .navButtonAdd('#grid1_toppager_left',{
        caption:"click me",
        buttonicon:"ui-icon-lightbulb",
        id: "lightbulb1",
        onClickButton: function(){       
            alert('clicked');
        }
    });

//Optional, Hide the bottom pager
$('#pager1').hide();

jqGrid - Server side paging sorting implementation with PHP, SQL Server

Recently, I faced to load a result set with around 5 thousand records, and found that the overall performance could not be accepted.

Therefore, server side paging is the way to go, and I found that there are only a few examples on the net to demonstrate how to do it.

So, I hope my simple example here can help you if you're also looking for it.

javascript :

var mygrid01 = $('#mygrid1');
    mygrid01.jqGrid({
        url:'php/loaddata.php',
        datatype: "json",        
        mtype: 'GET',
        colNames:[ 
            'customer_id','name','address'
        ],
        colModel:[    
            {name:'customer_id',index:'customer_id'},
            {name:'name',index:'name'},
            {name:'address',index:'address'}
        ],
        rowNum:50,
        rowList: [50,100,500],
        pager: '#pager1',    
        rownumbers: true,
        viewrecords: true,
        loadComplete: function() {
        }
    });

loaddata.php:

$limit=$_GET['rows']; 
$page=$_GET['page'];
$sidx=$_GET['sidx'];
$sord=$_GET['sord'];
...//your script of connection to db

$sidx = ($sidx=='')? 'customer_id':$sidx; //set default order if empty
$sord = ($sord=='')? 'asc':$sord;

$tsql = "select count(*) as count from customer";
$stmt = sqlsrv_query($conn, $tsql);
$row = sqlsrv_fetch_array($stmt);
$count = $row['count'];

$total_pages=ceil($count/$limit);
if ($page > $total_pages) { $page=$total_pages; }

$start = $limit*$page - $limit;
$end = $page * $limit;

$tsql = "WITH PAGED_CUSTOMERS  AS
    ( SELECT  customer_id, name, address,
     ROW_NUMBER() OVER (ORDER BY ".$sidx." ".$sord.") AS RowNumber FROM customer )

    SELECT customer_id, name, address  FROM PAGED_CUSTOMERS
   WHERE RowNumber BETWEEN ".$start." AND ".$end;

$stmt = sqlsrv_query($conn, $tsql);

$response = new stdClass();
$response->page = $page;
$response->total = $total_pages;
$response->records = $count;

$l_rowcount=0;
while ($row = sqlsrv_fetch_array($stmt)){ 
$response->rows[$l_rowcount]['id']=$l_rowcount+1; //set id starting from 1 instead of 0, and it's up to your design
    $response->rows[$l_rowcount]['cell']=array(
        $row['customer_id'],
        $row['name'],
        $row['address']
    );
    $l_rowcount++;               
}

echo json_encode($response);

jqgrid - custom button in cell with jquery ui icon

colModel:[ 
{name:'mycolname',index:'mycolname', width:20,
     formatter: function(cellvalue, options, rowObject) {
        return '<span class="ui-icon ui-icon-folder-open" title="click to open" onclick="clickme('+options.rowId+');"></span>'; } //replace span with button  if you want a real button

},...],


function clickme(rowid) {
            alert(rowid);
}

jqGrid - how to use formatter rowobject json

I decided to change the datatype from xml to json, as I want to see the processing time since json is native to javascript and may have a bit benefit on the performance; another reason is I found the 'rowobject' argument in formatter is quite useful and easy to get the value of the row data rather than in xml datatype.

example:
colModel:[
{name:'account', index:'account', width:100} ,
{name:'credit', index:'credit', hidden:true} ,
{name:'name', index:'name', width:50,
                formatter: function(cellvalue, options, rowObject) {
                    var color = (rowObject[1]<=650 || rowObject['credit']<=650) ? 'red':'black';  //when the grid first load, it used integer as the index of the array, after loadComplete, the grid will use the column name as the index.
                    return '<span style="color:'+color+';">' + cellvalue + '</span>';
                }
}, ...]